oracle常用sql

偏执的太偏执、 2022-04-15 03:19 412阅读 0赞

## 一、Oracle基本设置命令 ##

### 1、创建 ###

sys;//系统管理员，拥有最高权限  
system;//本地管理员，次高权限  
scott;//普通用户，密码默认为tiger,默认未解锁

### 2、登陆 ###

sqlplus / as sysdba;//登陆sys帐户  
sqlplus sys as sysdba;//同上  
sqlplus scott/tiger;//登陆普通用户scott

### 3、管理用户 ###

create user zhangsan;//在管理员帐户下，创建用户zhangsan  
alert user scott identified by tiger;//修改密码

### 4，授予权限 ###

#### 1）、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限 ####

grant create session to zhangsan; //授予zhangsan用户创建session的权限，即登陆权限  
    grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限  
    grant create table to zhangsan;//授予创建表的权限  
    grante drop table to zhangsan;//授予删除表的权限  
    grant insert table to zhangsan;//插入表的权限  
    grant update table to zhangsan;//修改表的权限  
    grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

#### 2）、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权 ####

grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限  
    grant drop on tablename to zhangsan;//授予删除表的权限  
    grant insert on tablename to zhangsan;//授予插入的权限  
    grant update on tablename to zhangsan;//授予修改表的权限  
    grant insert(id) on tablename to zhangsan;
    grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update  
    grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限

### 5、撤销权限 ###

基本语法同grant,关键字为revoke

### 6、查看权限 ###

select * from user_sys_privs;//查看当前用户所有权限  
    select * from user_tab_privs;//查看所用用户对表的权限

### 7、操作表的用户的表 ###

select * from zhangsan.tablename

### 8、权限传递 ###

即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：

grant alert table on tablename to zhangsan with admin option;//关键字 with admin option  
    grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似

### 9、角色 ###

角色即权限的集合，可以把一个角色授予给用户

create role myrole;//创建角色  
    grant create session to myrole;//将创建session的权限授予myrole  
    grant myrole to zhangsan;//授予zhangsan用户myrole的角色  
    drop role myrole;删除角色

### 10、oracle 如何查看当前用户的表空间名称 ###

**查询当前用户的表空间**

select default_tablespace from dba_users where username='登录用户'

**查询所有表空间**

-- 1 ）方式1：dba_tablespaces --
    select * from dba_tablespaces;
     --2 ）方式2：v$tablespace --
    select * from v$tablespace;

**查询用户下所有表**

-- 1 ）方式1：user_tables --
    select * from user_tables;
    --2 ）方式2: dba_tables --
    select * from dba_tables where owner='TMS21';

**查看表空间下有多少用户，tablespace\_name表空间 的名字一定要大写**

select distinct s.owner from dba_segments s where s.tablespace_name ='TEST_TABLESPACE';

## 二、数据库创建 ##

### 1、 查询数据文件存放路径 ###

select file_name from dba_data_files;

### 2、创建表空间 ###

表空间要求，初始设置128M，可以自动增长，每次增长32M，最大不限制，采用本地管理。  
创建语句：

create tablespace 表空间名 datafile '数据文件名.DBF' size 128M autoextend on next 32M maxsize unlimited extent management local;

### 3、创建用户并设置权限 ###

创建系统的数据库用户，设置密码和默认的表空间，并设置用户的权限，用户需要设置如下三个权限，connect，resource，create any view。  
创建语句：

create user 用户名 identified by 密码 default tablespace 表空间名;

赋权限语句：

grant connect,resource,create any view to 用户名;

### 4、给用户设置路径的读写权限 ###

设置用户对指定路径的读写权限，以导入数据。  
查询可用的路径：

select directroy_name,directory_path from all_directories;  
    设置对路径的读写权限  
    grant read,write on directory 路径名 to 用户名;

### 5、删除数据库 ###

删除表空间及数据库文件

drop tablespace tablespace_name including contents and datafiles;

删除用户权限  
如果有删除用户的权限，则可以，加了cascade就可以把用户连带的数据全部删掉。

drop user user_name cascade;

## 三、数据库泵导入导出 ##

EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。  
EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。

### 1、数据泵导出（expdp） ###

**expdp流程：**

新建逻辑目录

查看目录是否存在

expdp导出数据

执行expdp和impdp命令需要拥有exp\_full\_database和imp\_full\_database权限，授权语句如下： eg：

grant exp_full_database,imp_full_database to orcldev;

#### 1）查询路径信息 ####

查看已经创建的路径信息：

SELECT * FROM dba_directories;

#### 2）创建路径 ####

创建路径需要sys权限，需要有create any directory权限才可以创建路径。  
选项：DIRECTORY=directory\_object  
Directory\_object用于指定目录对象名称。需要注意，目录对象是使用CREATE  
DIRECTORY语句建立的对象，而不是OS目录。  
eg:

CREATE OR REPLACE directory backup_path AS 'D:\APP\ORADATA\db_backup';

–创建路径名为dackup\_path的路径，并指向硬盘的指定位置  
对新创建的路径进行授权操作：  
eg：

grant read,write on directory backup_path to orcldev;

–将对路径的读写权限分配各orcldev用户。

#### 3）导出orcldev这个schema的所用对象\[schemas or full\] ####

expdp orcldev/oracle@orcldev directory=backup_path dumpfile=orcldev_schema.dmp logfile=orcldev_schema_2013.log schemas=orcldev

#### 4）导出orcldev这个用户下的某些表\[tables\] ####

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_table.dmp logfile=orcldev_table_2013.log tables=('TAB_TEST','TAB_A')

#### 5）只导出orcldev这个用户的元数据\[content\] ####

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_meta.dmp logfile=orcldev_meta_2013.log SCHEMAS=orcldev CONTENT=METADATA_ONLY

#### 6）只导出orcldev这个用户50%的抽样数据\[sample\] ####

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_samp.dmp logfile=orcldev_samp_2013.log schemas=orcldev sample=50

#### 7）采用并行方式备份整库\[parallel\] ####

parallel参数只有在oracle10g之后的版本（包含10g）有效。

> oracle\_online：you can use the DUMPFILE parameter during export operations to specify multiple dump files, by using a substitution variable (%U) in the filename. This is called a dump file template. The new dump files are created as they are needed, beginning with 01 for %U, then using 02,03,and so on.

# 数据泵的并行备份,"%U"表示自动生成递增的序列号
    expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_parallel_%U.dmp logfile=orcldev_parallel.log parallel=4
    # 对应的并行恢复,"%U"表示自动生成递增的序列号
    impdp orcldev/oracle directory=dackup_path dumpfile=orcldev_parallel_%U.dmp logfile=orcldev_parallel.log  parallel=4

注意事项：

*  不是并行度越高备份恢复越快
 *  可以与FILESIZE参数联合使用
 *  导入的PARALLEL值和导出时PARALLEL值可以完全不同的，推荐相同

#### 8) 数据泵压缩备份(多文件备份) ####

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log compression=ALL
    # 对应的恢复不需要进行解压缩，直接导入即可
    impdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log

#### 9）并行与压缩备份结合 ####

# 并行与压缩备份联合
    expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log compression=ALL parallel=4
    # 对应的恢复
    impdp orcldev/oracle directory=dackup_path dumpfile=orcldev_%U.dmp logfile=orcldev.log parallel=4

#### 10）导出orcldev这个方案对象，但不包含索引\[exclude\] ####

eg: --可以剔除的对象有：VIEW,PACKAGE,FUNCTION,index,constraints,table,schema,user等等

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log SCHEMAS=orcldev EXCLUDE=index

#导出这个orcldev方案，剔除以TEST开头的索引
    expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log SCHEMAS=orcldev EXCLUDE=INDEX:"LIKE 'TEST%'"

#备份整库但剔除SCOTT这个用户的对象。
    expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE=SCHEMA:"='SCOTT'"  
    expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_exclude.dmp logfile=orcldev_exclude.log EXCLUDE=USER:"='SCOTT'"  
    #注意：include与exclude不能同时使用。

#### 11）PARFILE选项 ####

expdp命令可以调用parfile文件，在parfile里可以写备份脚本，可以使用query选项。  
Oracle highly recommends that you place QUERY specifications in a parameter file; otherwise, you might have to use operating system-specific escape characters on the command line before each quotation mark.  
如expdp.txt 内容如下：

USERID=orcldev/oracle directory=dackup_path dumpfile=orcldev_parfile.dmp logfile=orcldev_parfile.log TABLES='TAB_TEST' QUERY="WHERE TRAN_DATE=TO_DATE('2013-08-31','YYYY-MM-DD')"

执行方法：expdp parfile=expdp.txt 即可执行备份  
使用parfile好处是使用query选项是不用使用转义字符，如果将query参数放到外边的话，需要将""进行转义。  
eg:  
UNIX写法：

expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log schemas=orcldev INCLUDE=TABLE:\"IN $\'TEST_A\',\'TEST_B\'$\"

–在Unix系统执行是需要将单引号进行转义操作，否则会报错。  
WINDOWS写法：

expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log schemas=orcldev INCLUDE=TABLE:"IN \('TEST_A','TEST_B')"

#### 12）TABLESPACE导出表空间 ####

expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log tablespaces=user,orcldev

#### 13）Version选项 ####

VERSION选项默认值是COMPATIBLE，即兼容模式。在我们备份的时候，可以指定版本号。

expdp orcldev/oracle directory=backup_path dumpfile=2013.dmp logfile =2013.log full=Y VERSION=10.2.0.4

#### 14）FLASHBACK\_TIME选项 ####

指定导出特定时间点的表数据，可以联系一下FLASHBACK功能。

expdp orcldev/oracle directory=dackup_path dumpfile=orcldev_flash.dmp logfile=orcldev_flash.log SCHEMAS=orcldev   FLASHBACK_TIME="TO_TIMESTAMP('2013-09-28 14:30:00','DD-MM-YYYY HH24:MI:SS')"

设置用户对指定路径的读写权限，以导入数据。  
查询可用的路径：

select directroy_name,directory_path from all_directories;

将后缀名为dmp的文件放到指定的目录下，如果该路径下没有权限设置权限如下：  
设置对路径的读写权限

grant read,write on directory 路径名 to 用户名;

执行系统命令impdp：

impdp 用户名/密码   remap_schema=导出的schema名:导入的schema名   remap_tablespace=导出的表空间名:导入的表空间名   dumpfile=数据文件名 directory=路径名 logfile=导入日志名 transform=oid:n;

### 2、数据泵导入（impdp） ###

**impdp流程：**

创建表空间

创建用户并授权

impdp导入数据

#### 1）创建表空间、用户及权限 ####

见上文“二、数据库创建”

#### 2）导入用户（从用户scott导入到用户scott，相同用户表空间） ####

impdp scott/tiger@orcl directory=dump\_dir dumpfile=expdp.dmp schemas=scott logfile=impdp.log;

#### 3）数据泵impdp参数说明 ####

impdp 用户名/密码@地址/orcl DIRECTORY=dump_dir DUMPFILE=dump_name.dmp remap_schema=source_schema:target_schema remap_tablespace=source_tablespace:target_tablespace

> 1.REMAP\_DATAFILE   
> 该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时需要该选项.  
> REMAP\_DATAFILE=source\_datafie:target\_datafile  
> 2.REMAP\_SCHEMA   
> 该选项用于将源方案的所有对象装载到目标方案中.  
> REMAP\_SCHEMA=source\_schema:target\_schema  
> 3.REMAP\_TABLESPACE  
> 将源表空间的所有对象导入到目标表空间中  
> REMAP\_TABLESPACE=source\_tablespace:target:tablespace  
> 4.REUSE\_DATAFILES  
> 该选项指定建立表空间时是否覆盖已存在的数据文件.默认为N  
> REUSE\_DATAFIELS=\{Y | N\}  
> 5.SKIP\_UNUSABLE\_INDEXES  
> 指定导入是是否跳过不可使用的索引,默认为N  
> 6.sqlfile 参数允许创建DDL脚本文件  
> impdp scott/tiger directory=dump\_scott dumpfile=a1.dmp sqlfile=c.sql  
> 默认放在directory下，因此不要指定绝对路径  
> 7.STREAMS\_CONFIGURATION  
> 指定是否导入流元数据(Stream Matadata),默认值为Y.  
> 8.TABLE\_EXISTS\_ACTION  
> 该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP  
> TABBLE\_EXISTS\_ACTION=\{SKIP | APPEND | TRUNCATE |REPLACE\}  
> 当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;  
> 当设置为APPEND时,会追加数据;  
> 当设置为TRUNCATE时,导入作业会截断表,然后为其追加新数据;  
> 当设置为REPLACE时,导入作业会删除已存在表,重建表并追加数据;

#### 4)导入表（从scott用户中把表dept和emp导入到system用户中） ####

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:system logfile=impdp.log table_exists_action=replace (表空间已存在则替换);

#### 5)导入表空间 ####

impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example logfile=impdp.log;

#### 6)导入整个数据库 ####

impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y logfile=impdp.log;

#### 7)追加数据 ####

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION

## 三、exp/imp导入导出 ##

### 1、exp/imp导入导出 ###

#### 1）远程导出导入 ####

导出

exp 用户名/密码@ip:端口/实例名 file=E:/XXX.DMP full=y

如下：

exp temp/temp@192.168.1.6:1521/orcl file=E:/temp.DMP full=y

导入

imp 用户名/密码@ip:端口/实例名 file=E:/xxx.DMP full=y

如下：

imp temp/temp@192.168.1.6:1521/orcl file=E:/temp.DMP full=y

#### 2）服务器导出导入 ####

imp cms_enterprise/cms_enterprise  file=c:/cms_enterprise1013.DMP full=y

### 2、imp导入不同表空间 ###

#### 1）库信息 ####

原库–表空间：source；用户：source；密码：source。  
目标库–表空间：target；用户：target；密码：target。

#### 2）权限设置 ####

grant resource,connect to target;
    # 赋 DBA 权限  
    grant dba to target;
    # 撤销此权限，这个位置很关键  
    revoke unlimited tablespace from target;
    # 将用户在 System 表空间的配额置为 0  
    alter user target quota 0 on system;
    # 设置在用户在   target表空间配额不受限。经过上述设置后，就可以用 imp 导入数据，数据将会进入指定的 target表空间：  
    alter user target quota unlimited on target;

#### 3）导入方式 ####

window下导入方法

imp target/target fromuser=source touser=target file=D:/source.dmp ignore=y grants=n

linux 下导入方法：

imp b/b file=source.dmp log=source.log fromuser=source touser=target ignore=y commit=y buffer=52428800 indexes=n grants=n constraints=n

## 四、常见异常问题 ##

### 1、ORA-28000: the account is locked… ###

ALTER USER username ACCOUNT UNLOCK;

### 2、oracle11g导出dmp文件时不能导出空表，导致缺表 ###

#### 1）、设置deferred\_segment\_creation参数（以下语句可以在cmd里登录数据库后执行）： ####

查看参数：

show parameter deferred_segment_creation;

设置参数：

alter system set deferred_segment_creation=false；

注意：该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用。

#### 2）、导出以前导入的空表 ####

a,分析用户下的所有表：将该sql语句查询出的结果都执行一遍（目的为了查询空表）

select 'analyze table '||table_name||' compute statistics;' from user_tables;

b,查询该用户下所有的空表：将第二条sql语句的执行结果都执行一遍

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0；

c,执行之后就可以导出空表了

#### 3） expdp导出异常 ####

导出语句：

expdp name/password@orcl directory=backup_path dumpfile=test.dmp logfile=test.log schemas=test;

错误：expdp ORA-12154: TNS:could not resolve the connect identifier specified  
解决方案：  
增加ip端口：

expdp name/password@192.168.6.66:1521/orcl directory=backup_path dumpfile=test.dmp logfile=test.log schemas=test;

#### 4）expdp/impdp出现ORA-39001、ORA-39000 ####

说明：从oracle 10g采用数据泵方式导出，导入到oracle 11g时问题，由于源数据版本与目标数据库版本不一致，遭遇ORA-39001、ORA-39000  
解决方法：  
需要expdp导出时加上目标数据的version  
查看oracle版本：

# 两种方法
    select * from v$version;
    select banner from sys.v_$version;

#### 5）expdp出现ORA-39002、ORA-39070、ORA-39087 ####

**错误语句：**

expdp name/password@192.168.6.66:1521/adc DIRECTORY=backup_path dumpfile=test.dmp schemas=test version=11.2.0.1.0;

**错误描述：**  
ORA-39002: invalid operation  
ORA-39070: Unable to open the log file.  
ORA-39087: directory name BACKUP\_PATH is invalid  
**解决方案：**  
重新创建备份目录  
先删除原先创建的备份目录：

drop directory backup_path;

重新创建新的备份目录：

create or replace directory backup_path as '/db5/wangyaodong/dmp';

赋权限：

grant read,write on directory backup_path to public;