如何使用Oracle DataPump从每个表中获取N行的全部对象元数据导出模式?

huangapple go评论53阅读模式
英文:

How to take the export of Schema all objects metadata with N rows from each table in Oracle DataPump?

问题

有时需要对每个表中的有限行数进行模式元数据的备份。在我的示例中,我将从每个表中导出 20 行,并携带所有元数据,您可以相应地修改 .par 文件。将运行两个导出命令:

  1. 仅导出元数据
  2. 从每个表中导出 N 行

此外,您还可以将转储中的DDL提取到 .SQL 文件中。这样,我们可以将数据保存在一个小型的有限大小文件中,以便用于测试目的,并导入到另一个数据库中。

英文:

Sometimes its needed to take a backup of schema metadata of all objects with only limited rows from each table
with Oracle DataPump (export and import).

In my example, I'll take the export of 20 rows from each table with all metadata, you can modify .par accordingly. Will run two export command

  1. Export METADATA_ONLY
  2. Export N Rows from each table

Also, you can extract the DDL from a dump into a .SQL file.

In this way, we can have the data in a small limited-size file for our testing purpose to import into another database.

答案1

得分: 1

在Oracle中创建目录

CREATE DIRECTORY dirname AS '/path/path/path';
GRANT READ, WRITE ON DIRECTORY dirname TO PUBLIC;

第一次导出仅包含指定模式的元数据。

expdp 用户名/密码 SCHEMAS=模式名称 DIRECTORY=DIR_NAME dumpfile=DMP_FILENAME.dmp CONTENT=METADATA_ONLY

第二次导出每个表的前20行数据。

vi exp_filename.par
DIRECTORY=ITO_DATA_PUMP_DIR
DUMPFILE=DMP_FILENAME.dmp
LOGFILE=DMP_FILENAME.log
SCHEMAS=USER
INCLUDE=TABLE:"IN(select table_name from dba_tables where owner ='USER')"
QUERY="where rownum < 20"

expdp username/password parfile=exp_filename.par

第三次导出从导出的.dmp文件中提取DDL。

impdp 用户名/密码 DIRECTORY=DIR_NAME dumpfile=DMP_FILENAME.dmp SQLFILE=exp_rajesh_all.sql

另一个导出的示例,包含多个查询,并且仅保留基于日期列的最新数据,也可根据您的需求进行修改。

DIRECTORY = my_dir
DUMPFILE  = exp_query.dmp
LOGFILE   = exp_query.log
SCHEMAS   = hr, scott
INCLUDE   = TABLE:"IN ('EMP', 'DEPARTMENTS')"

#TABLES    =(scott.emp,scott.dept,scott.salgrade)
QUERY     = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000"

# 将以下3行放在一行上:
QUERY     = hr.departments:"WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)"

# 带有日期过滤器
QUERY="where change_date > to_date('31-Dec-2020','DD-MON-YYYY')"

注意:上述参数文件未经测试,但您可以将其用作参考。此外,我们可以根据导出/导入要求在.par文件中添加其他数据泵参数。

英文:

Create directory in oracle

CREATE DIRECTORY dirname AS &#39;/path/path/path&#39;;
Grant read,write on DIRECTORY dirname to public;

First export run for metadata only for a specified schema.

expdp Username/password SCHEMAS=SCH_NAME DIRECTORY=DIR_NAME dumpfile=DMP_FILENAME.dmp CONTENT=METADATA_ONLY

Second export run for 20 rows from each table.

vi exp_filename.par
DIRECTORY=ITO_DATA_PUMP_DIR
DUMPFILE=DMP_FILENAME.dmp
LOGFILE=DMP_FILENAME.log
SCHEMAS=USER
INCLUDE=TABLE:&quot;IN(select table_name from dba_tables where owner =&#39;USER&#39;)&quot;
QUERY=&quot;where rownum &lt; 20&quot;

expdp username/password parfile=exp_filename.par

Third Export to extract the DDL from an exported .dmp file.

impdp Username/password DIRECTORY=DIR_NAME dumpfile=DMP_FILENAME.dmp SQLFILE=exp_rajesh_all.sql

Another example of exporting with multiple queries and keeping only the latest data based on the date column and also can be modified according to your requirements.

DIRECTORY = my_dir
DUMPFILE  = exp_query.dmp
LOGFILE   = exp_query.log
SCHEMAS   = hr, scott
INCLUDE   = TABLE:&quot;IN (&#39;EMP&#39;, &#39;DEPARTMENTS&#39;)&quot;

#TABLES    =(scott.emp,scott.dept,scott.salgrade)
QUERY     = scott.emp:&quot;WHERE job = &#39;ANALYST&#39; OR sal &gt;= 3000&quot;

# Place following 3 lines on one single line:
QUERY     = hr.departments:&quot;WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = &#39;ANALYST&#39; OR e.salary &gt;= 3000)&quot;

# With date filter
QUERY=&quot;where change_date &gt; to_date(&#39;31-Dec-2020&#39;,&#39;DD-MON-YYYY&#39;)&quot;

Note: Above parameter file is not tested but you can use it as a reference. Also, we can add other data pump parameters as per export/import requirements in the .par file.

huangapple
  • 本文由 发表于 2023年6月15日 20:14:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76482356.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定