Oracle 数据库管理操作常用命令

写道

--删除表空间和用户
Drop Tablespace ASSET_DATA Including Contents;
Drop User asset Cascade;
Drop Tablespace AUDITOR_DATA Including Contents;
Drop User auditor Cascade;

Drop Tablespace CAPITAL_DATA Including Contents;
Drop User capital Cascade;

Drop Tablespace FINA_DATA Including Contents;
Drop User finance Cascade;

Drop Tablespace FJBPBJ_DATA Including Contents;
Drop User fjbpbj Cascade;

Drop User finance1 Cascade;

--创建表空间
Create smallfile tablespace ASSET datafile 'G:\oradata\ASSET_DATA01.DBF' size 2048M, 'H:\oradata\ASSET_DATA02.DBF' size 2048M nologging;
Create smallfile tablespace AUDITOR datafile 'G:\oradata\AUDITOR_DATA01.DBF' size 2048M nologging;
Create smallfile tablespace CAPITAL datafile 'G:\oradata\CAPITAL_DATA01.DBF' size 2048M nologging;
Create smallfile tablespace CDMA_DATA datafile 'G:\oradata\CDMA_DATA01.DBF' size 2048M nologging;
Create smallfile tablespace ELECSTOCK_DATA datafile 'G:\oradata\ELECSTOCK_DATA01.DBF' size 2048M nologging;
Create smallfile tablespace FINANCE datafile 'G:\oradata\FINA_DATA01.DBF' size 2048M,'H:\oradata\FINA_DATA02.DBF' size 2048M,'I:\oradata\FINA_DATA03.DBF' size 2048M,'G:\oradata\FINA_DATA04.DBF' size 2048M,'H:\oradata\FINA_DATA05.DBF' size 2048M,'I:\oradata\FINA_DATA06.DBF' size 2048M nologging;
Create smallfile tablespace FJBPBJ datafile 'G:\oradata\FJBPBJ_DATA01.DBF' size 2048M, 'H:\oradata\FJBPBJ_DATA02.DBF' size 2048M nologging;
Create smallfile tablespace FJLMATERIAL_DATA datafile 'G:\oradata\FJLMATERIAL_DATA01.DBF' size 2048M, 'H:\oradata\FJLMATERIAL_DATA02.DBF' size 2048M nologging;
Create smallfile tablespace MATERIAL_DATA datafile 'G:\oradata\MATERIAL_DATA01.DBF' size 2048M,'H:\oradata\MATERIAL_DATA02.DBF' size 2048M,'I:\oradata\MATERIAL_DATA03.DBF' size 2048M,'G:\oradata\MATERIAL_DATA04.DBF' size 2048M,'H:\oradata\MATERIAL_DATA05.DBF' size 2048M,'I:\oradata\MATERIAL_DATA06.DBF' size 2048M, 'G:\ORADATA\MATERIAL_DATA07.DBF' SIZE 2048M,'H:\ORADATA\MATERIAL_DATA08.DBF' SIZE 2048M,'I:\ORADATA\MATERIAL_DATA09.DBF' SIZE 2048M, 'I:\ORADATA\MATERIAL_DATA10.DBF' SIZE 2048M nologging;
Create smallfile tablespace PROJECT datafile 'G:\oradata\PROJECT01.DBF' size 2048M,'H:\oradata\PROJECT02.DBF' size 2048M,'I:\oradata\PROJECT03.DBF' size 2048M,'G:\oradata\PROJECT04.DBF' size 2048M,'H:\oradata\PROJECT05.DBF' Size 2048M, 'G:\ORADATA\PROJECT06.DBF' SIZE 2048M,'H:\ORADATA\PROJECT07.DBF' SIZE 2048M,'I:\ORADATA\PROJECT08.DBF' SIZE 2048M ,'I:\oradata\PROJECT09.DBF' size 2048M, 'G:\oradata\PROJECT10.DBF' size 2048M nologging;
Create smallfile tablespace YWCB datafile 'G:\oradata\YWCB01.DBF' size 2048M,'H:\oradata\YWCB02.DBF' size 2048M,'I:\oradata\YWCB03.DBF' size 2048M nologging;

--创建角色
create role mss_role;
grant connect to mss_role;
grant resource to mss_role;
grant dba to mss_role;

---创建用户
create user asset identified by asset123 default tablespace ASSET ;
grant mss_role to asset;
grant UNLIMITED TABLESPACE to asset;
alter user asset quota 0 on users;

create user auditor identified by auditor123 default tablespace AUDITOR ;
grant mss_role to auditor;
grant UNLIMITED TABLESPACE to auditor;
alter user auditor quota 0 on users;

create user capital identified by capital123 default tablespace CAPITAL ;
grant mss_role to capital;
grant UNLIMITED TABLESPACE to capital;
alter user auditor quota 0 on users;

create user cdma identified by cdma123 default tablespace CDMA_DATA ;
grant mss_role to cdma;
grant UNLIMITED TABLESPACE to cdma;
alter user auditor quota 0 on users;

create user elecstock identified by elecstock123 default tablespace ELECSTOCK_DATA;
grant mss_role to elecstock;
alter user elecstock quota 0 on USERS;
alter user elecstock quota unlimited on ELECSTOCK_DATA;

create user finance identified by finance123 default tablespace FINANCE;
grant mss_role to finance;
grant UNLIMITED TABLESPACE to finance;
alter user finance quota 0 on users;

create user finance1 identified by finance123 default tablespace FINANCE;
grant mss_role to finance1;
grant UNLIMITED TABLESPACE to finance1;
alter user finance quota 0 on users;

create user fjbpbj identified by fjbpbj123 default tablespace FJBPBJ;
grant mss_role to fjbpbj;
grant UNLIMITED TABLESPACE to fjbpbj;
alter user fjbpbj quota 0 on users;

create user fjlmaterial identified by fjlmaterial123 default tablespace FJLMATERIAL_DATA;
grant mss_role to fjlmaterial;
alter user fjlmaterial quota 0 on USERS;
alter user fjlmaterial quota unlimited on FJLMATERIAL_DATA;

create user material identified by material123 default tablespace MATERIAL_DATA;
grant mss_role to material;
grant UNLIMITED TABLESPACE to material;
alter user material quota 0 on users;

create user project identified by project123 default tablespace PROJECT;
grant mss_role to project;
grant UNLIMITED TABLESPACE to project;
alter user project quota 0 on users;

create user repair identified by repair123 default tablespace YWCB;
grant mss_role to repair;
grant UNLIMITED TABLESPACE to repair;
alter user repair quota 0 on users;

---创建pulbic数据库链
create public database link to_asset connect to asset identified by assett123 using 'testdb';
create public database link to_auditor connect to auditor identified by auditor123 using 'testdb';
create public database link to_capital connect to project identified by capital123 using 'testdb';
create public database link to_cdma connect to project identified by cdma123 using 'testdb';
create public database link to_elecstock connect to elecstock identified by elecstock123 using 'testdb';
create public database link to_finance connect to finance identified by finance123 using 'testdb';
create public database link to_finance1 connect to finance1 identified by finance123 using 'testdb';
create public database link to_fjbpbj connect to fjbpbj identified by fjbpbj123 using 'testdb';
create public database link to_fjlmaterial connect to fjbpbj identified by fjlmaterial123 using 'testdb';
create public database link to_material connect to material identified by material123 using 'testdb';
create public database link to_project connect to project identified by project123 using 'testdb';
create public database link to_repair connect to repair identified by repair123 using 'testdb';

--删除数据库链
Drop public database link to_asset;
Drop public database link to_auditor;
Drop public database link to_capital;
Drop public database link to_cdma;
Drop public database link to_elecstock;
Drop public database link to_finance;
Drop public database link to_finance1;
Drop public database link to_fjbpbj;
Drop public database link to_fjlmaterial;
Drop public database link to_material;
Drop public database link to_project;
Drop public database link to_repair;

--导入数据库，在命令行（cmd）执行
imp userid=asset/asset123 file=I:\dmp\asset.dmp fromuser=asset touser=asset BUFFER=307200000 commit=Y log=I:\dmp\asset.log
imp userid=auditor/auditor123 file=I:\dmp\auditor.dmp fromuser=auditor touser=auditor BUFFER=307200000 commit=Y log=I:\dmp\auditor.log
imp userid=capital/capital123 file=I:\dmp\capital.dmp fromuser=capital touser=capital BUFFER=307200000 commit=Y log=I:\dmp\capital.log
imp userid=cdma/cdma123 file=I:\dmp\cmda.dmp fromuser=cdma touser=cdma BUFFER=307200000 commit=Y log=I:\dmp\cmda.log
imp userid=elecstock/elecstock123 file=I:\dmp\elecstock.dmp fromuser=elecstock touser=elecstock BUFFER=307200000 commit=Y log=I:\dmp\elecstock.log
imp userid=finance/finance123 file=I:\dmp\finance1.dmp fromuser=finance touser=finance BUFFER=307200000 commit=Y log=I:\dmp\finance1.Log
imp userid=finance1/finance123 file=I:\dmp\finance2.dmp fromuser=finance1 touser=finance1 BUFFER=307200000 commit=Y log=I:\dmp\finance2.log
imp userid=fjbpbj/fjbpbj123 file=I:\dmp\fjbpbj.dmp fromuser=fjbpbj touser=fjbpbj BUFFER=307200000 commit=Y log=I:\dmp\fjbpbj.log
imp userid=fjlmaterial/fjlmaterial123 file=I:\dmp\fjlmaterial.dmp fromuser=fjlmaterial touser=fjlmaterial BUFFER=307200000 commit=Y log=I:\dmp\fjlmaterial.log
imp userid=material/material123 file=I:\dmp\material.dmp fromuser=material touser=material BUFFER=307200000 commit=Y log=I:\dmp\material.log
imp userid=project/project123 file=I:\dmp\project.dmp fromuser=project touser=project BUFFER=307200000 commit=Y log=I:\dmp\project.log
imp userid=repair/repair123 file=I:\dmp\repair.dmp fromuser=repair touser=repair BUFFER=307200000 commit=Y log=I:\dmp\repair.log


Select * From dba_users ;
Select * From Dba_Tablespaces;
Drop User project_gx Cascade;
Drop User material_gx Cascade;
Drop User project_jt Cascade;

create user material_gx identified by material_gx123 default tablespace MATERIAL_DATA;
grant mss_role to material_gx;
grant UNLIMITED TABLESPACE to material_gx;
alter user material_gx quota 0 on users;

create user project_jt identified by project_jt123 default tablespace PROJECT_JT;
grant mss_role to project_jt;
grant UNLIMITED TABLESPACE to project_jt;
alter user project_jt quota 0 on users;

Drop Tablespace PROJECT_GX Including Contents;


Create smallfile tablespace MATERIAL_DATA datafile 'G:\oradata\MATERIAL_DATA01.DBF' size 2048M,'H:\oradata\MATERIAL_DATA02.DBF' size 2048M,'I:\oradata\MATERIAL_DATA03.DBF' size 2048M,'G:\oradata\MATERIAL_DATA04.DBF' size 2048M,'H:\oradata\MATERIAL_DATA05.DBF' size 2048M,'I:\oradata\MATERIAL_DATA06.DBF' size 2048M, 'G:\ORADATA\MATERIAL_DATA07.DBF' SIZE 2048M,'H:\ORADATA\MATERIAL_DATA08.DBF' SIZE 2048M,'I:\ORADATA\MATERIAL_DATA09.DBF' SIZE 2048M, 'I:\ORADATA\MATERIAL_DATA10.DBF' SIZE 2048M nologging;
Create smallfile tablespace PROJECT_GX datafile 'G:\oradata\PROJECT01_GX.DBF' size 2048M,'H:\oradata\PROJECT02_GX.DBF' size 2048M,'I:\oradata\PROJECT03_GX.DBF' size 2048M,'G:\oradata\PROJECT04_GX.DBF' size 2048M,'H:\oradata\PROJECT05_GX.DBF' Size 2048M, 'G:\ORADATA\PROJECT06_GX.DBF' SIZE 2048M,'H:\ORADATA\PROJECT07_GX.DBF' SIZE 2048M,'I:\ORADATA\PROJECT08_GX.DBF' SIZE 2048M ,'I:\oradata\PROJECT09_GX.DBF' size 2048M, 'G:\oradata\PROJECT10_GX.DBF' size 2048M nologging;


imp userid=material_gx/material_gx123 file=I:\dmp\material_gx.dmp fromuser=material_gx touser=material_gx BUFFER=307200000 commit=Y
imp userid=project_gx/project_gx123 file=I:\dmp\project_test.dmp fromuser=project_gx touser=project_gx BUFFER=307200000 commit=Y
imp userid=project_jt/project_jt123 file=I:\dmp\project_jt.dmp fromuser=project_jt touser=project_jt BUFFER=307200000 commit=Y

 

参考资料

http://hi.baidu.com/_ecore/blog/item/1b1b1902b393aa064afb5155.html