Oracle创建用户，授权，导入，备份表等常见sql操作讲解

1.创建表空间

create tablespace tablespacename
logging 
datafile 'filepath\filename.dbf'
size 256m 
autoextend on 
next 128m maxsize unlimited; 

2.查看表空间

select * from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name 

3.创建用户，并设置默认表空间以及临时表空间

create user username identified by userpassword
default tablespace usertablespace
temporary tablespace temp;

4.更改用户密码

alter user username identified by password;

5.删除用户

drop user username cascade;

4.给用户授权

grant dba to username;--授权dba,还有connect?,resource?等角色

5.导入数据文件(dmp)

imp username/password@host:port/orcl fromuser=fromuser touser=touser file='filepath' log='logfilepath' ignore=y;

6.导出数据文件(dmp)

exp username/password@host:port/orcl file='filename' owner=(owner)--owner表示导出owner这个用户的表

7.备份还原表

create table tablename_bak as select * from tablename;--备份表
delete from tablename; insert into tablename select * from tablename_bak;--还原表