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