Oracle資料庫的常用命令和匯入匯出

--建立表空間

create tablespace elearn_data_test 
logging 
datafile 'C:\oraclexe\oradata\XE\elearn_data.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

 

--建立臨時表空間 

create temporary tablespace elearn_temp_test  
tempfile 'C:\oraclexe\oradata\XE\elearn_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

 

--建立使用者並指定表空間

create user elearnuser identified by elearnpass
default tablespace elearn_data 
temporary tablespace elearn_temp;

 

--使用者授權

grant connect,resource to elearnuser;  

GRANT 
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
　　TO elearnuser;

 

--刪除表空間

DROP TABLESPACE elearn_data INCLUDING CONTENTS AND DATAFILES

DROP TABLESPACE elearn_temp INCLUDING CONTENTS AND DATAFILES

 

--刪除某一使用者所有的表

declare 
cursor cur1 is select table_name from dba_tables where owner='elearnuser';
begin
  for cur2 in cur1 loop
    execute immediate 'drop table elearnuser.'||cur2.table_name;
  end loop;
end;

 

--刪除使用者命令

drop user elearnuser cascade;

 

--命令列登陸oracle

connect system/admin as sysdba

 

--匯出表

exp system/admin@XE file=d:daochu.dmp owner=(elearnuser)

 

--匯入表

imp system/order@elearnDB full=y  file=d:\elearndb.dmp ignore=y

 --建立序列

create sequence  SeqFunSort 
increment by 1       --增長度 
start with 1         --從哪裡增加，就是說下一個獲取的值從這個值開始 
nomaxvalue           --不設定最大值   對應的：maxvalue  30、 
order                --指定一定往下增加 
nocycle              --不迴圈，CYCLE和NOCYCLE 表示當序列生成器的值達到限制值後是否迴圈 
cache  10           --CACHE