杂乱无章之Oracle(二)

六、IMPDP用法

1、导入表

impdp hsiufo/hsiufo directory=dump_dir dumpfile=full.dmp tables=scott.emp remap_schema=scott:scott

杂乱无章之Oracle(二)

注:上图为为有一个全库的逻辑备份 full.dmp,然后删除用户scott的emp表,在full.dmp中导入emp到用户scott

impdp hsiufo/hsiufo directory=dump_dirdumpfile=full.dmp tables=scott.test remap_schema=scott:system

杂乱无章之Oracle(二)

第一种方法表示将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

杂乱无章之Oracle(二)

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号,确定恢复的时间段(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

杂乱无章之Oracle(二)

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,其中“主机字符串”是指服务名称:

杂乱无章之Oracle(二)

杂乱无章之Oracle(二)

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(二)

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

上一篇:Linux Object-C 编译环境安装


下一篇:openjudge(二)