欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-19 10:25:52
1.创建表空间 create tablespace tablespacename logging datafile 'filepath\filename.dbf' siz...

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;--还原表