创建表空间:
CREATE TABLESPACE finchinafcdd
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\finchinafcdd.DBF'
SIZE 50M
AUTOEXTEND ON
NEXT 100M MAXSIZE 2000M(UNLIMITED)
LOGGING
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K #指定区尺寸为128k,如不指定，区尺寸默认为64k
SEGMENT SPACE MANAGEMENT AUTO;
删除表空间
drop tablespace test including contents and datafiles;
更改表空间表
alter tablespace "system" rename to "TEST";
创建 用户 ：
CREATE USER "TEST" IDENTIFIED BY "*******"
DEFAULT TABLESPACE "TEST";
ACCOUNT UNLOCK
用户授予/删除权限
grant connect,resource,dba,unlimited tablespace to test;
revoke connect,resource,dba,unlimited tablespace from test
更改用户默认表空间
ALTER USER "SYSTEM" DEFAULT TABLESPACE "TEST";
删除用户
drop user test cascade;
移动表至另一表空间
alter table move tablespace room1;
1.增加数据文件
ALTER TABLESPACE TEST
ADD DATAFILE 'D:\oracle\product\10.2.0\oradataTEST02.dbf' SIZE 1000M;
2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradataTEST.dbf'
RESIZE 4000M;
3.设定数据文件自动扩展
ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradataTEST.dbf
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED;
查询
select */table_name from user_tables; #查询当前用户所有*/表
select * from user_users; #查询当前用户默认表空间
select */tablespace_name from user_tablespaces; 查询所有用户*/表空间
select * from all_users #查询所有用户
describe 表名 #查询表空间
select * from v$version;
select * from session_privs;
select * from user_role_privs;
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"
数据的导入
1：将D:daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:daochu.dmp
imp aichannel/aichannel@HUST full=y file=d:datanewsmgnt.dmp ignore=y
2：将d:daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:daochu.dmp tables=(table1)
监听程序附加：
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = ORCL)
)
select username,default_tablespace
from dba_users;
一、创建DBA角色
1.在plsql中以 sys/**** as sysdba登陆，创建角色DBA
使用下列语句进行创建，创建过程中会报错，不用管，这是给DBA赋权时有部分权限没有赋上
-- Create the role
create role DBA;
-- Grant/Revoke object privileges
grant execute on SYS.DBMS_DBVERIFY to DBA;
grant execute on SYS.DBMS_DEFER_QUERY to DBA;
grant execute on SYS.DBMS_DEFER_SYS to DBA;
grant execute on SYS.DBMS_FEATURE_USAGE to DBA;
grant execute on SYS.DBMS_FLASHBACK to DBA;
grant execute on SYS.DBMS_MONITOR to DBA;
grant execute on SYS.DBMS_RESUMABLE to DBA;
grant execute on SYS.DBMS_SERVER_ALERT to DBA;
grant execute on SYS.DBMS_SERVER_TRACE to DBA;
grant execute on SYS.DBMS_SERVICE to DBA;
grant execute on SYS.DBMS_STORAGE_MAP to DBA;
grant execute on SYS.DBMS_UADV_ARR to DBA;
grant execute on SYS.DBMS_UNDO_ADV to DBA;
grant execute on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;
grant execute on SYS.LOAD_UNDO_STAT to DBA;
grant select, insert, update, delete, alter on SYS.MAP_OBJECT to DBA;
grant execute on SYS.OUTLN_EDIT_PKG to DBA;
grant execute on SYS.OUTLN_PKG to DBA;
grant execute on SYS.RESET_UNDO_STAT to DBA;
grant update on WKSYS.WK$SYS_CONFIG to DBA;
-- Grant/Revoke role privileges
grant delete_catalog_role to DBA with admin option;
grant execute_catalog_role to DBA with admin option;
grant exp_full_database to DBA;
grant gather_system_statistics to DBA;
grant imp_full_database to DBA;
grant java_admin to DBA;
grant java_deploy to DBA;
grant olap_dba to DBA;
grant scheduler_admin to DBA with admin option;
grant select_catalog_role to DBA with admin option;
grant wm_admin_role to DBA;
grant xdbadmin to DBA;
-- Grant/Revoke system privileges
grant administer any sql tuning set to DBA with admin option;
grant administer database trigger to DBA with admin option;
grant administer resource manager to DBA with admin option;
grant administer sql tuning set to DBA with admin option;
grant advisor to DBA with admin option;
grant alter any cluster to DBA with admin option;
grant alter any dimension to DBA with admin option;
grant alter any evaluation context to DBA with admin option;
grant alter any index to DBA with admin option;
grant alter any indextype to DBA with admin option;
grant alter any library to DBA with admin option;
grant alter any materialized view to DBA with admin option;
grant alter any outline to DBA with admin option;
grant alter any procedure to DBA with admin option;
grant alter any role to DBA with admin option;
grant alter any rule to DBA with admin option;
grant alter any rule set to DBA with admin option;
grant alter any sequence to DBA with admin option;
grant alter any sql profile to DBA with admin option;
grant alter any table to DBA with admin option;
grant alter any trigger to DBA with admin option;
grant alter any type to DBA with admin option;
grant alter database to DBA with admin option;
grant alter profile t
o DBA with admin option;
grant alter resource cost to DBA with admin option;
grant alter rollback segment to DBA with admin option;
grant alter session to DBA with admin option;
grant alter system to DBA with admin option;
grant alter tablespace to DBA with admin option;
grant alter user to DBA with admin option;
grant analyze any to DBA with admin option;
grant analyze any dictionary to DBA with admin option;
grant audit any to DBA with admin option;
grant audit system to DBA with admin option;
grant backup any table to DBA with admin option;
grant become user to DBA with admin option;
grant comment any table to DBA with admin option;
grant create any cluster to DBA with admin option;
grant create any context to DBA with admin option;
grant create any dimension to DBA with admin option;
grant create any directory to DBA with admin option;
grant create any evaluation context to DBA with admin option;
grant create any index to DBA with admin option;
grant create any indextype to DBA with admin option;
grant create any job to DBA with admin option;
grant create any library to DBA with admin option;
grant create any materialized view to DBA with admin option;
grant create any operator to DBA with admin option;
grant create any outline to DBA with admin option;
grant create any procedure to DBA with admin option;
grant create any rule to DBA with admin option;
grant create any rule set to DBA with admin option;
grant create any sequence to DBA with admin option;
grant create any sql profile to DBA with admin option;
grant create any synonym to DBA with admin option;
grant create any table to DBA with admin option;
grant create any trigger to DBA with admin option;
grant create any type to DBA with admin option;
grant create any view to DBA with admin option;
grant create cluster to DBA with admin option;
grant create database link to DBA with admin option;
grant create dimension to DBA with admin option;
grant create evaluation context to DBA with admin option;
grant create indextype to DBA with admin option;
grant create job to DBA with admin option;
grant create library to DBA with admin option;
grant create materialized view to DBA with admin option;
grant create operator to DBA with admin option;
grant create procedure to DBA with admin option;
grant create profile to DBA with admin option;
grant create public database link to DBA with admin option;
grant create public synonym to DBA with admin option;
grant create role to DBA with admin option;
grant create rollback segment to DBA with admin option;
grant create rule to DBA with admin option;
grant create rule set to DBA with admin option;
grant create sequence to DBA with admin option;
grant create session to DBA with admin option;
grant create synonym to DBA with admin option;
grant create table to DBA with admin option;
grant create tablespace to DBA with admin option;
grant create trigger to DBA with admin option;
grant create type t
o DBA with admin option;
grant create user to DBA with admin option;
grant create view to DBA with admin option;
grant debug any procedure to DBA with admin option;
grant debug connect session to DBA with admin option;
grant delete any table to DBA with admin option;
grant dequeue any queue to DBA with admin option;
grant drop any cluster to DBA with admin option;
grant drop any context to DBA with admin option;
grant drop any dimension to DBA with admin option;
grant drop any directory to DBA with admin option;
grant drop any evaluation context to DBA with admin option;
grant drop any index to DBA with admin option;
grant drop any indextype to DBA with admin option;
grant drop any library to DBA with admin option;
grant drop any materialized view to DBA with admin option;
grant drop any operator to DBA with admin option;
grant drop any outline to DBA with admin option;
grant drop any procedure to DBA with admin option;
grant drop any role to DBA with admin option;
grant drop any rule to DBA with admin option;
grant drop any rule set to DBA with admin option;
grant drop any sequence to DBA with admin option;
grant drop any sql profile to DBA with admin option;
grant drop any synonym to DBA with admin option;
grant drop any table to DBA with admin option;
grant drop any trigger to DBA with admin option;
grant drop any type to DBA with admin option;
grant drop any view to DBA with admin option;
grant drop profile to DBA with admin option;
grant drop public database link to DBA with admin option;
grant drop public synonym to DBA with admin option;
grant drop rollback segment to DBA with admin option;
grant drop tablespace to DBA with admin option;
grant drop user to DBA with admin option;
grant enqueue any queue to DBA with admin option;
grant execute any class to DBA with admin option;
grant execute any evaluation context to DBA with admin option;
grant execute any indextype to DBA with admin option;
grant execute any library to DBA with admin option;
grant execute any operator to DBA with admin option;
grant execute any procedure to DBA with admin option;
grant execute any program to DBA with admin option;
grant execute any rule to DBA with admin option;
grant execute any rule set to DBA with admin option;
grant execute any type to DBA with admin option;
grant export full database to DBA with admin option;
grant flashback any table to DBA with admin option;
grant force any transaction to DBA with admin option;
grant force transaction to DBA with admin option;
grant global query rewrite to DBA with admin option;
grant grant any object privilege to DBA with admin option;
grant grant any privilege to DBA with admin option;
grant grant any role to DBA with admin option;
grant import full database to DBA with admin option;
grant insert any table to DBA with admin option;
grant lock any table to DBA with admin option;
grant manage any queue to DBA with admin option;
grant manage scheduler to DBA
with admin option;
grant manage tablespace to DBA with admin option;
grant on commit refresh to DBA with admin option;
grant query rewrite to DBA with admin option;
grant restricted session to DBA with admin option;
grant resumable to DBA with admin option;
grant select any dictionary to DBA with admin option;
grant select any sequence to DBA with admin option;
grant select any table to DBA with admin option;
grant select any transaction to DBA with admin option;
grant under any table to DBA with admin option;
grant under any type to DBA with admin option;
grant under any view to DBA with admin option;
grant update any table to DBA with admin option;
2.在sqlplus中以 sys/**** as sysdba登陆，给dba角色赋权
用 sys/**** as sysdba登录sqlplus，然后执行下列语句：
grant all privileges to dba with admin option;
grant select_catalog_role to dba with admin option;
grant execute_catalog_role to dba with admin option;
grant delete_catalog_role to dba with admin option;
二、创建CONNECT角色
在plsql中直接创建角色CONNECT，执行下列语句即可：
-- Create the role
create role CONNECT;
-- Grant/Revoke system privileges
grant alter session to CONNECT;
grant create cluster to CONNECT;
grant create database link to CONNECT;
grant create sequence to CONNECT;
grant create session to CONNECT;
grant create synonym to CONNECT;
grant create table to CONNECT;
grant create view to CONNECT;
然后给system和相应的用户赋予DBA的角色，即可操作
注：如果是linux用户可以使用终端之后输入
su-oracle
sqlplus
之后按照以上步骤就可以了~
linux里面这个文件可以用查看的方式打开之后就能复制如果用记事本貌似复制不了~