Category: Oracle Database

How to use query in EXPDP

5 Comments

在datapump export中Oracle提供了query参数，允许只导出满足query条件的记录。

在Oracle 10gR2的官方文档中，提到的语法是：

QUERY = [schema.][table_name:] query_clause

并且给出了例子：

QUERY=employees:'”WHERE department_id > 10 AND salary > 10000″‘

但是实际上这个例子是错误的，按照例子中的语法，则会碰到ORA-06502错误。

D:\Temp>type emp_query.par
QUERY=emp:'"WHERE rownum<4"'
NOLOGFILE=y
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp1.dmp

D:\Temp>expdp scott/tiger parfile=emp_query.par

Export: Release 11.1.0.6.0 - Production on Thursday, 26 February, 2009 18:07:24

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Oracle Database Vault
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=emp_query.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\ADMIN\ORCL11G\DPDUMP\EXP1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 18:08:04

这份文档错误在Oracle 11gR1的官方文档中已经修正了。正确的语法，是去除掉例子中query字串的前后单引号：QUERY=emp:”WHERE rownum<4"

D:\Temp>type emp_query.par
QUERY=emp:"WHERE rownum<4"
NOLOGFILE=y
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp1.dmp

D:\Temp>expdp scott/tiger parfile=emp_query.par reuse_dumpfiles=y

Export: Release 11.1.0.6.0 - Production on Thursday, 26 February, 2009 18:16:59

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Oracle Database Vault
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=emp_query.par reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.125 KB       3 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\ORACLE\ADMIN\ORCL11G\DPDUMP\EXP1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:17:32

更详细的Query用法可以参看Metalink Note：277010.1。

Delete Expired VS. Delete Obsolete

Leave a comment

今天有朋友问，到底RMAN里面的命令delete expired和delete obsolete有什么区别？

先来看一下官方文档中的解释。via Backup and Recovery User Guide

delete exipired

If you run CROSSCHECK, and if RMAN cannot locate the files, then it updates their records in the RMAN repository to EXPIRED status. You can then use the DELETE EXPIRED command to remove records of expired backups and copies from the RMAN repository.

delete obsolete

The RMAN DELETE command supports an OBSOLETE option, which deletes backups that are no longer needed to satisfy specified recoverability requirements. You can delete files obsolete according to the configured default retention policy, or another retention policy that you specify as an option to the DELETE OBSOLETE command. As with other forms of the DELETE command, the files deleted are removed from backup media, deleted from the recovery catalog, and marked as DELETED in the control file.

delete expired删除的是那些本来RMAN以为存在但是实际上在磁盘或者磁带上已经被删除了的信息，删除的只是RMAN资料库中的记录；delete obsolete则删除旧于备份保留策略定义的备份数据同时也更新RMAN资料库以及控制文件。

那么再形象一点儿解释的话，就是“在不在”和“要不要”的区别。expired是不在了，obsolete是不要了。

不在的并不一定是不需要的。
不需要的也不一定就不在了。

外一句：数据库管理的时候，痛苦的是不需要的还在，还占据着资源；比这更痛苦的是，需要的已经不在了，想恢复却找不到备份集。 😀

New pseudo Column ORA_ROWSCN after 10g

Leave a comment

在Oracle 10g之后版本中提供了一个新的伪列，ORA_ROWSCN，该伪列用来显示行级的SCN。

注意：只有在create table时加上ROWDEPENDENCIES关键字，才可以启动行级SCN的记录，否则ORA_ROWSCN只会显示该行所在的block头部记录的SCN。ROWDEPENDENCIES关键字只有在创建表的时候可以使用，表一旦被创建该属性则无法变更。

SQL> select ora_rowscn,a.* from dept a;
 
ORA_ROWSCN DEPTNO DNAME          LOC
---------- ------ -------------- -------------
   1514356     10 ACCOUNTING     NEW YORK
   1514356     20 RESEARCH       DALLAS
   1514356     30 SALES          CHICAGO
   1514356     40 OPERATIONS     BOSTON

怎么使用ORA_ROWSCN则需要发挥大家智慧。下面是两则有趣的例子。

1. 如何使用ORA_ROWSCN来避免更新在选取了之后又被其它会话更新过的记录，从而避免使用select … for update来锁定数据行。
ORA_ROWSCN: The pseudo Column

2. 如何在不开启Audit的情况下，估算某表被最新改动的时间（不是DDL改动，而是DML改动）。
When was a table last changed?

在你们的应用中使用到这个伪列了吗？