oracle 创建表空间_用户_授权,oracle创建表空间、用户、授权

DOS:创建数据库实例:dbca

DROP TABLESPACE study INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

drop user study cascade;

用以下这句查找空表：

select table_name from user_tables where NUM_ROWS=0

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 (条件可有可无)

把查询结果导出，执行导出的语句：

'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'

-----------------------------------------------------------

alter table AQ$_AQ$_MEM_MC_H allocate extent;

alter table AQ$_AQ$_MEM_MC_G allocate extent;

SQL*PLUS

建库：

create tablespace study datafile 'C:\app\Administrator\product\11.2.0\dbhome_1\database\cls.ora' size 1000m;

create tablespace study datafile 'd:\oracle\product\10.2.0\oradata\nnhd\cls.ora' size 500m;

create tablespace study datafile 'F:\oracle\product\10.2.0\oradata\szgjj\cls.ora' size 1000m;

create tablespace study datafile 'd:\oracle\product\10.2.0\oradata\datang\cls.ora' size 1000m;

create tablespace study datafile 'F:\oracle\product\10.2.0\oradata\fw\cls.ora' size 1000m;

create tablespace study datafile 'd:\oracle\product\10.2.0\oradata\yl\cls.ora' size 1000m;

create tablespace study datafile 'f:\oracle\product\10.2.0\oradata\zsdx\cls.ora' size 1000m;

create tablespace study datafile 'd:\oracle\product\10.2.0\oradata\xuzhou\cls.ora' size 1000m;

create tablespace study datafile 'd:\orcl\oracle\product\10.2.0\oradata\gdds\cls.ora' size 1000m;

create tablespace study datafile 'C:\app\Administrator\admin\dzyhtest\cls.ora' size 1000m;

create tablespace study datafile 'C:\app\Administrator\admin\dzyhsc\cls.ora' size 1000m;

用户：

create user study identified by ok default tablespace study;

分配权限：

grant dba to study;

DOS:

导入数据库：d:\yl.dmp

imp study/ok@dt file=E:\dak-file\dak\datang20140414.dmp fromuser=study touser=study

imp study/ok@dzyhtest file=E:\dak-file\工作\dzTest20140414.dmp fromuser=study touser=study

imp study/ok@datang file=E:\dt.dmp ignore=y

imp study/ok@shyk file=d:\study.dmp fromuser=study touser=study ignore=y

imp study/ok@zsdx file=f:\zsdx0719.dmp fromuser=study touser=study ignore=y

imp study/ok@zsdx1 file=e:\study.20120325.dmp fromuser=study touser=study

20140328.dmp

imp study/ok@dz file=e:\20140328.dmp fromuser=study touser=study

imp study/ok@dzyhtest file=E:\dak-file\工作\dzTest20140414.dmp fromuser=study touser=study

imp study/ok@datang file=E:\dak-file\工作\Datang0424.dmp fromuser=study touser=study

exp study/ok@dzyh file=c:\dzyh.dmp full=y

exp study/ok@ip地址/study file=D:\20140224.dmp full = y

exp study/ok@datang file = d:\datang.dmp full = y

exp study/ok@dzyhsc file=d:\20140504-dz.dmp full=y

exp study/ok@dt file=d:\dt-20140510.dmp full=y

exp study/ok@dzsc file=d:\dzsc-20140510.dmp full=y

exp study/ok@dzyhsc file=d:\dtyhsc-20140510.dmp full=y

exp study/ok@dzyhtest file=d:\dzyhte