oracle常用语句

 

 

1、新建表空间

create tablespace TABLE_SPACE
datafile 'D:\OracleDB\table_space.dbf'
size 500M
autoextend on next 100M
maxsize unlimited;

2、创建临时表空间

create temporary tablespace temporary_tablespace_name
tempfile 'D:\OracleDB\table_temp.dbf'
size 500M
autoextend on
next 100M maxsize unlimited
extent management local;

3、删除表空间及数据库文件

drop tablespace tablespace_name including contents and datafiles;

4、创建用户

create user username identified by password
profile default
default tablespace tablespace_name
temporary tablespace temporary_tablespace_name
account unlock;

5、赋予授权

（1）常用的权限（需要别的的百度走起）

grant connect,resource to username ;
grant create any sequence to username ;
grant create any table to username ;
grant delete any table to username ;
grant insert any table to username ;
grant select any table to username ;
grant unlimited tablespace to username ;
grant execute any procedure to username ;
grant update any table to username ;
grant create any view to username ;

（2）自家常用赋权

grant connect,dba,resource to ksfz ;

6、删除用户

drop user  user_name cascade ;

7、导出数据库

（1）原始方式

exp user_name/pass_word@db_name file=d:\work\temp\file_name.dmp

 

（2）数据泵方式

--1、创建directory
create directory dict_name as 'path';
--例如：
create directory myOraBak as 'D:/orabak';
--2、给目标用户授权
grant read,write on directory dict_name to user_name;
--例如：
grant read,write on directory myOraBak to orcl;
#3、导出
#(1)导出表结构
expdp user_name/pass_word@db_name directory=dict_name dumpfile=file_name.dmp version=goal_oracle_version CONTENT=METADATA_ONLY
#例如
expdp test/test@orcl directory=myOraBak dumpfile=bdap20210202.dmp version=11.2.0.1.0 CONTENT=METADATA_ONLY
#(2)导出表
expdp user_name/pass_word@db_name directory=dict_name dumpfile=file_name.dmp version=goal_oracle_version

8、数据库导入

（1）原始方式

imp user_name/pass_word@db_name file=d:\... full=y

（2）数据泵方式

--1、创建本地的directory
create directory myOraBak as 'D:\OracleDB';create or replace directory myOraBak as 'D:\OracleDB';
--2、本地授权
grant read,write on directory myOraBak to bdap;
#3、导入
impdp bdap/bdap@orcl directory=myOraBak dumpfile=bdap20210202_indb.dmp transform=segment_attributes:n;