六、IMPDP用法
1、导入表
impdp hsiufo/hsiufo directory=dump_dir dumpfile=full.dmp tables=scott.emp remap_schema=scott:scott
注：上图为为有一个全库的逻辑备份 full.dmp，然后删除用户scott的emp表，在full.dmp中导入emp到用户scott
impdp hsiufo/hsiufo directory=dump_dirdumpfile=full.dmp tables=scott.test remap_schema=scott:system
第一种方法表示将EMP表导入到SCOTT方案中,第二种方法表示将test表导入的SYSTEM方案中.
注意,如果要将表导入到其他方案中,必须指定REMAP SCHEMA选项.
2、导入方案
impdp hsiufo/hsiufodirectory=dump_dir dumpfile=full.dmp schemas=scott
Impdp system/manager
DIRECTORY=dump_dir
DUMPFILE=schema.dmp
SCHEMAS=scott REMAP_SCHEMA=scott:system
3、导入表空间
Impdp system/manager DIRECTORY=dump_dirDUMPFILE=tablespace.dmp
TABLESPACES=user01
4、导入数据库
Impdp system/manager DIRECTORY=dump_dirDUMPFILE=full.dmp FULL=y
93、oracle中的数据传送到excel中
（1）、建立emp.sql文件，其输入的代码如下：
set line 120
set pagesize 100
set feedback off
spool f:\excels\emp
select * from emp;
spool off
（2）、执行这个文件
SQL>conn system/manager as sysdba
SQL>@emp.sql
执行命令后，会生成一个emp.LIST的正文文件
（3）、将数据用excel打开
首先打开excel，然后点击菜单栏中的文件-打开选中emp.LIST文件默认下一步直到完成即可。
94、查看闪回功能是否开启
SQL>show parameter bin;
95、设置闪回功能开启或者关闭
SQL>alter system set recyclebin=off/on;
96、查看被删除的对象
SQL>show recyclebin;（select * from recyclebin）
97、清空闪回站中的内容（清空之后，对象将不能恢复）
SQL>purge recyclebin;
98、清空某个表在闪回站中的内容
SQL>purge table zzrk_xxb;
99、恢复某个对象(表)
SQL>flashback table zzrk_xxb to before drop;
100、直接彻底删除对象(表)（将不能恢复）
SQL>drop table zzrk_xxb purge;
101、查看闪回时间段
SQL>show parameter undo_retention; //value值单位是秒,指可以闪回这段时间内的DML操作
102、设置闪回时间段
SQL>alter system set undo_retention=7200;//设置为7200秒，也就是2小时，可以闪回两小时之内的DML操作。
103、时间段恢复实例
SQL>alter table zzrk_xxb enable row movement;//启动行移动功能
SQL>select * from flashback_transaction_query;//查看ＳＣＮ号，确定恢复的时间段（SCN指START_SCN）
SQL>flashback table zzrk_xxb to scn NO.;(NO.指flashback_transaction_query中的START_SCN编号)
104、只读表空间和临时表空间的恢复
假设F:\oracle\product\10.2.0\oradata\jinlian\temp01.dbf这个文件出现问题，会报类似如下的错误“ORA-27037:unable to obtain file status”，则进行如下恢复操作
SQL>alter tablespace temp add tempfile ’ F:\oracle\product\10.2.0\oradata\jinlian\temp02.dbf’ size 20M;
SQL>alter tablespace temp drop tempfile ‘F:\oracle\product\10.2.0\oradata\jinlian\temp01.dbf’;
105、加快数据表空间的恢复
SQL>select * from dba_data_files;
SQL>alter tablespace PIONEER_DATA begin backup;
SQL>host copy F:\disk2\moon\pioneer_data.dbf F:\disk12\moon\
SQL>alter tablespace PIONEER_DATA end backup;
SQL>shutdown immediate
SQL>startup //在启动过程中由于数据文件有问题会出现错误，这个假设是6号文件有误
SQL>alter database datafile 6 offline;
SQL>alter database open;
SQL>select * from v$datafile;//确认6号文件是否脱机状态
SQL>ALTER TABLESPACE PIONEER_DATA RENAME DATAFILE ‘F:\disk2\moon\pioneer_data.dbf’ TO ‘F:\disk12\moon\pioneer_data.dbf’;
SQL>recover datafile 6;
SQL>alter database datafile 6 online;
SQL>select * from v$datafile;//确认数据文件的各种状态
106、使用user_source可以查看用户下的存储过程和存储函数
107、SGA_MAX_SIZE为分配给系统全局区（SGA）最大内存。这个参数不是动态而是静态的，即只能修改该参数在spfile中的值，修改之后还要shutdown数据库，再重新启动之后才能起作用。除了该参数之外，还有两个必须静态修改的与SGA有关的系统参数，它们分别是lock_sga和pre_page_sga。
Lock_sga被设置为true时，整个SGA会被锁在物理内存中。这样就可以避免将SGA的某些部分分配到虚拟内存磁盘上，这可以明显改进大型生产或商业数据系统的效率。该参数的默认值是false。如果IT平台不支持这样的设置，这一参数将被忽略。
如果将pre_page_sga设为true，在实例系统启动时，整个的SGA会被读入物理内存中。这样做虽然增加了实例启动的时间和所需的物理内存，但是可以提高系统的效率。该参数默认值也是false。
（1）显示SGA的值
SQL>show parameter sga
（2）修改lock_sga的值为true
SQL>alter system set lock_sga=true;//这个有可能会出现错误
（3）调整日志缓冲区的大小【日志缓冲大小的值单位是字节】
SQL>alter system set log_buffer=10485760 scope=spfile;//可以使用select 10*1024*1024 from dual;得出要修改的字节大小
设置好之后就shutdown immediate和startup使修改的值生效
oracle tnsping命令、sql*plus使用
1、测试数据库服务的命令：
控制台 tnsping命令 IP地址 端口号 数据库服务名称
cmd->tnsping 192.168.1.123:1521/oracle10g
tnsping命令：如果能够ping通，则说明客户端能解析listener的机器名，而且lister也已经启动，但是并不能说明数据库已经打开，而且tsnping的过程与真正客户端连接的过程也不一致。但是如果不能用tnsping通，则肯定连接不到数据库。
2、查看服务配置的ora文件路径
E:\database\oracle\user\product\11.1.0\db_1\NETWORK\ADMIN\tnsnames.ora
文件内容形如：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.1.123)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
3、使用sql*plus查询数据库
a、登陆sql*plus，其中“主机字符串”是指服务名称：
permanent tablespace
d、查询所有用户：
select * from dba_users;
e、查询符合某些条件的用户：
select * from dba_users where username like 'ABC%';
使用图形工具简介
获得企业管理器控制台的HTTP端口号
C:\oracle\product\10.2.0\db_1\install目录下的一个portlist.ini的正文文件（有时路径不一定是这个路径，可以使用tnsping先确定大体路径）。
定义ORACLE_SID（如果已经定义了SID就可以直接进入下一步）
C:\Documents and Settings\Owner>cd c:\oracle\porduct\10.2.0\db_1\bin
C:\ oracle\porduct\10.2.0\db_1\bin>set ORACLE_SID=jinlian
利用Oracle提供的企业管理器控制程序启动控制台进程
C:\ oracle\porduct\10.2.0\db_1\bin>emctl start dbconsole
利用Oracle提供的企业管理器查看控制台进程的状态
C:\ oracle\porduct\10.2.0\db_1\bin>emctl status dbconsole
使用监听控制程序（lsnrctl）
C:\ oracle\porduct\10.2.0\db_1\bin> lsnrctl
LSNRCTL>help//此命令的帮助文档
利用status命令获取监听器程序当前的状态
LSNRCTL>status
启动监听进程
LSNRCTL>start
注：监听进程是负责处理远程连接的，如果监听进程没有启动，用户是不能进行远程连接的。
使用isqlplus登录界面
C:\ oracle\porduct\10.2.0\db_1\bin>isqlplusctl start
输入网址http://localhost:5560/isqlplus
C:\ oracle\porduct\10.2.0\db_1\bin>isqlplusctl stop//停止isqlplus进程
关闭数据库系统的顺序如下：
（1）在操作系统下，使用isqlplusctl stop命令停止isqlplus进程
（2）在操作系统下，使用emctl stop dbconsole命令停止企业管理器的控制台进程
（3）在操作系统下，使用lsnrctl stop命令停止监听进程
（4）在SQL*Plus中使用shutdown immediate(也可以使用shutdown)命令关闭数据库
（5）使用SQL*Plus的exit命令退出SQL*Plus
10、启动数据库系统的顺序如下：
（1）在操作系统提示下，使用sqlplus/nolog进入SQL*Plus
（2）在SQL*Plus中使用connect sys/”密码” as sysdba以SYSDBA身份登录数据库
（3）使用startup命令启动数据库系统
（4）在操作系统下，使用lsnrctl start启动监听进程
（5）在操作系统下，使用emctl start dbconsole启动企业管理器的控制台进程
（6）在操作系统下，使用isqlplus start启动isqlplus进程
11、通过移动表和索引来减少I/O竞争的实例
（1）使用user_tables获取SCOTT用户中所有的表所存放的表空间信息
SQL>SELECT * FROM user_tables;
（2）使用user_indexes获取SCOTT用户的所有索引所存在的表空间信息
SQL>SELECT * FROM user_indexes;
（3）查看用户SCOTT用户所在的默认表空间
SQL>SELECT * FROM dba_users WHERE username=’SCOTT’;
（4）使用dba_data_files列出数据库中所有的表空间及它们所对应的数据文件
SQL>SELECT * FROM dba_data_files;
（5）修改用户SCOTT的默认表空间，改为PIONEER_DATA
SQL>ALTER USER scott DEFAULT TABLESPACE pioneer_data;
（6）验证是否修改成功
SQL>SELECT * FROM dba_users where username=’SCOTT’;
（7）将用户SCOTT的emp表移动到PIONEER_DATA表空间中
SQL>ALTER TABLE emp MOVE TABLESPACE pioneer_data;
（8）验证emp表是否存放在PIONEER_DATA表空间中
SQL>SELECT * FROM user_tables;
（9）查看索引所存放的表空间和状态信息等
SQL>SELECT * FROM user_indexes;
注：有时发现访问某个表的速度突然变慢，就可以使用此语句查看一下，如果索引的STATUS变成UNUSABLE，这就是变慢的原因。
（10）重建索引并同时将它移动到PIONEER_INDEX表空间中
SQL>ALTER INDEX pk_emp REBUILD TABLESPACE pioneer_index;
（11）查看所建索引是否变成了VALID并且它是否存在了PIONEER_INDEX表空间中
SQL>SELECT * FROM user_indexes;
Oracle SQL
LOWER(列名|表达式)：字符转换成小写
UPPER(列名|表达式)：字符转成大写
INITCAP(列名|表达式)：把每个字的头一个字符转换成大写，其余的转换成小写
CONCAT(str1,str2)：将str1和str2连接成一个字符串
SUBSTR(str,m,[n])：返回str指定的子串，该子串从第m个字符开始，其长度为n。如果n省略则直接到str结尾
如：substr(‘abcdefghijklmn’,8)= hijklmn
INSTR(str1,str2,[m],[n])：返回str2在str1中的数字位置，m表示从第m个字符开始搜索，n表示所给字符串出现的次数，他们的默认值都是1
REPLACE(str,str1,str2)：在str中查找str1，用str2替换str1
ROUND(str,n)：str表示的数值四舍五入到小数点后的n位round(1688.88,-1)=170
TRUNC(str,n)：str表示的数值截取到小数点后的n位，没有四舍五入trunc(168.88,-1)=160
10、MOD(m,n)：将m除以n并取余数
11、MONTHS_BETWEEN(date1,data2)：返回date1和date2之间的月数，如果date1大于date2，其返回的月数为正；反之为负
12、ADD_MONTHS(date,n)：把n个月加到date上
13、NEXT_DAY(date,str)：返回下一个由字符串（星期几）指定的日期next_day(’10-may-02’,’monday’)=13-may-02
14、LAST_DAY(date)：返回date所在月的最后一天
15、TO_CHAR(date,’fmt’)：把日期类型数据转换成变长字符串，其中，fmt为日期模式to_char(date,’yyyy-mm-dd’)
16、TO_NUMBER(str [,’fmt’])：该函数用于把字符串转换成数字
17、TO_DATE(str [,’fmt’])：该函数用于把字符串转换成日期型数据
18、NVL(str1,str2)：如果str1值为空值（NULL），就返回str2，否则返回表达式str1的值
19、NVL2(str1,str2,str3)：如果str1不为空值（NULL），就返回str2，否则返回str3的值
20、NULLIF(str1,str2)：如果str1和str2相等就返回空值（NULL）；如果不等就返回str1
21、使用ON子句的多表连接和附加条件
SQL>select w.empno,w.ename,w.job,w.sal,m.empno,m.ename,d.loc from emp w join manager m on w.mgr=m.empno join dept d on m.deptno=d.deptno where w.job in (‘clerk’,’analyst’);
22、<all为小于最小的，>all为大于最大的；<any为小于最大的，>any为大于最小的，=any等价于in
23、保存sql语句文档和执行其文档
SQL>select * from zzrk_xxb;
SQL>save “d:\zhen\sqlplus” //在d:\zhen文件中创建一个含有select语句的sqlplus.sql文档
SQL>@d:\zhen\sqlplus //执行d:\zhen文件中sqlplus.sql文档语句
24、user_catalog可以看到用户所拥有的所有表的名称和类型，其别名是cat，查询结果一样。
25、修改表结构
SQL>ALTER TABLE user ADD (hiredate DATA);//为表user添加列hiredate
SQL>ALTER TABLE user MODIFY(hiredate DEFAULT SYSDATE);//为表user修改列hiredate
SQL>ALTER TABLE user DROP COLUMN hiredate;//删除表user列hiredate
SQL>RENAME user TO worker;//将表名为user更改为worker
注：如果修改一个对象的名字，则使用该对象的软件或对象需要重新编译或修改，这可能会对系统的效率产生冲击。
26、对表或列添加注释
SQL>COMMIT ON TABLE 表名|COLUMN 表名.列名 IS ‘正文’;
27、截断表和删除表
SQL>TRUNCATE TABLE 表名;
SQL>DROP TABLE 表名;
28、快捷出入表内容
SQL>insert into user(id,name) select pid,pname from emp;
29、基于另一个表的修改、
SQL>update emp_dml set sal=(select losal from salgrate where data like ’2012%’);
30、多例子查询修改记录
SQL>update emp_dml set (job,sal)=(select job,sal from emp_dml where data like ‘2012%’);
31、每执行一条DML语句就自动提交一次事务
SQL>SET AUTOCOMMIT ON//之后的update、insert、delete语句就自动提交数据
32、建立索引
SQL>CREATE INDEX job_sal_index on empcon(job,sal);
33、添加主键
SQL>ALTER TABLE deptcon ADD CONSTRAINT deptcon_pk PRIMARY KEY(deptno);
34、如何使用视图的WITH CHECK OPTION子句
SQL>CREATE OR REPLACE VIEW sales AS
SELECT * FROM emp WHERE depno=30
WITH CHECK OPTION CONSTRAINT sales;
当修改表emp中的depno值时不能违反在创建视图时用WHERE子句所限定的条件
35、修改视图
SQL>UPDATE sales set empname=’zhen’ WHERE id IN (‘12’,’13’);
36、使用WITH READ ONLY则视图不能修改
37、如何创建同义词
SQL>CREATE SYNONYM s FOR supplier;
SQL>SELECT * FROM s和SELECT * FROM supplier结果一样
38、查看用户所具有的权限
SQL>SELECT * FROM SESSION_PRIVS;
39、查看角色所具有的权限
SQL>SELECT * FROM role_sys_privs where role
40、INTERSECT连接两个SQL语句返回两个查询结果中所有相同的数据行
MINUS连接两个SQL语句返回在第1个查询结果中但不在第2个查询结果中的所有数据行。
如何生成脚本文件
SQL>select * from zd_sex;
SQL>save d:\zhen\sex //在d盘下zhen文件夹中生成sex.sql文件
如何编辑脚本文件
SQL>ed d:\sql.sql
如何执行脚本文件
（1）SQL>@d:\sql.sql
（2）SQL>get d:\sql.sql
SQL>/
41、创建oracle定时执行命令job
oracle JOB常见的执行时间
1、每分钟执行
TRUNC(sysdate,'mi')+1/(24*60)
www.2cto.com
2、每天定时执行
例如：
每天凌晨0点执行
TRUNC(sysdate+1)
每天凌晨1点执行
TRUNC(sysdate+1)+1/24
每天早上8点30分执行
TRUNC(SYSDATE+1)+(8*60+30)/(24*60)
3、每周定时执行
例如：
每周一凌晨2点执行
TRUNC(next_day(sysdate,1))+2/24
TRUNC(next_day(sysdate,'星期一'))+2/24
每周二中午12点执行
TRUNC(next_day(sysdate,2))+12/24
TRUNC(next_day(sysdate,'星期二'))+12/24
4、每月定时执行
例如：
每月1日凌晨0点执行
TRUNC(LAST_DAY(SYSDATE)+1)
每月1日凌晨1点执行
TRUNC(LAST_DAY(SYSDATE)+1)+1/24
5、每季度定时执行
每季度的第一天凌晨0点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')
每季度的第一天凌晨2点执行
TRUNC(ADD_MONTHS(SYSDATE,3),'q')+2/24
每季度的最后一天的晚上11点执行
TRUNC(ADD_MONTHS(SYSDATE+ 2/24,3),'q')-1/24
6、每半年定时执行
例如：
每年7月1日和1月1日凌晨1点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),6)+1/24
7、每年定时执行
例如：
每年1月1日凌晨2点执行
ADD_MONTHS(TRUNC(sysdate,'yyyy'),12)+2/24
初始化相关参数job_queue_processes alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位
job_queue_process 表示oracle能够并发的job的数量，可以通过语句
show parameter job_queue_process;
来查看oracle中job_queue_process的值。当job_queue_process值为0时表示全部停止oracle的job，可以通过语句
ALTER SYSTEM SET job_queue_processes = 10;
来调整启动oracle的job。
相关视图： dba_jobs all_jobs user_jobs dba_jobs_running 包含正在运行job相关信息
－－－－－－－－－－－－－－－－－－－－－－－－－
提交job语法：
begin sys.dbms_job.submit(job => :job,
what => 'P_CLEAR_PACKBAL;',
next_date => to_date('04-08-2008 05:44:09', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+ 1/360');
commit;
end;
/
－－－－－－－－－－－－－－－－－－－－－－－－－
创建JOB
variable jobno number;
begin
dbms_job.submit(:jobno, 'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);
commit;
运行JOB SQL> begin
dbms_job.run(:job1);
end;
/
删除JOB
SQL> begin
dbms_job.remove(:job1);
end; /
DBA_JOBS ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列） 类型 描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒
BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数
描述 INTERVAL参数值
每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）
1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24