Export: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 15:09:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting \ hr/********@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported \ 5.984 KB 2 rows Master table \
****************************************************************************** Dump file set for HR.SYS_EXPORT_TABLE_01 is: /home/oracle/exp_mag.dmp
Job \
或者：
[oracle@rac1 scripts]$ exp hr/oracle@prod file=exp_mag.dmp tables=MAGAZINE_ARTICLES
Export: Release 10.2.0.1.0 - Production on Tue Apr 10 14:22:34 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table MAGAZINE_ARTICLES 2 rows exported Export terminated successfully without warnings.
SQL> delete HR.MAGAZINE_ARTICLES; 2 rows deleted.
SQL> commit; Commit complete.
用exp_mag.dmp文件执行导入：
[oracle@rac1 ~]$ impdp hr/oracle@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES
Import: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 15:17:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Master table \Starting \ hr/********@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table \exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job \
此时发现表已存在，无法导入，于是在导入时加上一个参数 CONTENT=data_only 在导入的时候将HR用户退出Sqlplus，否则导入时会卡住不动。
[oracle@rac1 ~]$ impdp hr/oracle@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES CONTENT=data_only
Import: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 15:46:03
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Master table \Starting \ hr/********@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES CONTENT=data_only Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported \ 5.984 KB 2 rows Job \
至此，导入成功。
或者：
[oracle@rac1 scripts]$ imp hr/oracle@prod file=exp_mag.dmp tables=MAGAZINE_ARTICLES ignore=y
Import: Release 10.2.0.1.0 - Production on Tue Apr 10 14:32:40 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing HR's objects into HR . importing HR's objects into HR
. . importing table \ 2 rows imported Import terminated successfully without warnings.
验证：
SQL> select count(*) from MAGAZINE_ARTICLES;
COUNT(*) ---------- 2
2. Create a new table in the HR schema in the PROD database with the following specifications: 2.1 Table name ORACLE9I_REFERENCES 2.2 Tablespace USERS 2.3 Table structure:
2.3.1 ORACLE9I_ARTICLE ROWID
2.3.2 INSERT_TIME TIMESTAMP WITH LOCAL TIME ZONE
3. For any row in the HR.MAGAZINE_ARTICLES table that contains three or more references to Oracle9i insert the corresponding rowid and a timestamp for the time that it was inserted into the ORACLE9I_REFERENCES table.
CREATE TABLE \(
\
\ TIMESTAMP WITH LOCAL TIME ZONE )
TABLESPACE \
将以上脚本保存为oracle9i_references.sql，然后在Sqlplus中执行： SQL> @oracle9i_references.sql Table created.
SQL> insert into HR.ORACLE9I_REFERENCES select ROWID,SCN_TO_TIMESTAMP(ORA_ROWSCN) from HR.MAGAZINE_ARTICLES where instr(AUTHOR||ARTICLE_NAME||ARTICLE_DATE||ARTICLE_DATA,'Oracle9i',1,3)>0;
1 row created.
INSTR(源字符串 ，要查找的字符串，从第几个字符开始， 要找到第几个匹配的序号) 例如：
SQL> select instr('Oracle9iOracle9iOracle9iOracle9i','Oracle9i',1,3) from dual;
INSTR('ORACLE9IORACLE9IORACLE9IORACLE9I','ORACLE9I',1,3) --------------------------------------------------------
17 返回的是第三个Oracle9i开始的位置
SQL> SELECT * FROM HR.ORACLE9I_REFERENCES;
ORACLE9I_ARTICLE ------------------ INSERT_TIME
--------------------------------------------------------------------------- AAAClkAAAAAACBRAAA 30-MAR-12 05.07.40.528266 PM
Partitioning
1. Create 5 new tablespaces in the PROD database as follows:
1.1 Use the name DATA01, DATA02, DATA03, DATA04 and DATA05. 1.2 Spread the datafiles across different disk directories. 1.3 Each file should be 250MB in size. 1.4 Use uniform extents of 4MB. 1.5 Block size should be 16KB
create tablespace data01 datafile '/u01/app/oracle/oradata/PROD/Disk1/data01.dbf' size 250M autoextend on next 10M
extent management local uniform size 4M segment space management auto blocksize 16k;
create tablespace data02 datafile '/u01/app/oracle/oradata/PROD/Disk2/data02.dbf' size 250M autoextend on next 10M
extent management local uniform size 4M segment space management auto blocksize 16k;
create tablespace data03 datafile '/u01/app/oracle/oradata/PROD/Disk3/data03.dbf' size 250M autoextend on next 10M
extent management local uniform size 4M
segment space management auto blocksize 16k;
create tablespace data04 datafile '/u01/app/oracle/oradata/PROD/Disk4/data04.dbf' size 250M autoextend on next 10M
extent management local uniform size 4M segment space management auto blocksize 16k;
create tablespace data05 datafile '/u01/app/oracle/oradata/PROD/Disk5/data05.dbf' size 250M autoextend on next 10M
extent management local uniform size 4M segment space management auto blocksize 16k;
将以上脚本保存为partition.sql，并在Sqlplus中执行： [oracle@rac1 scripts]$ sqlplus sys/oracle@prod as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 16:08:02 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> @partition.sql
Tablespace created.
Tablespace created.
Tablespace created.
Tablespace created.
Tablespace created.
2. Create a partitioned table named SALES_HISTORY in the SH schema in the PROD database. Use the following specifications:
2.1 The column names and definitions will be the same as the OLTP_USER.SALES table. 2.2 partition the table into 5 different partitions on the SDATE column using the following specifications:
2.2.1 Partition P1 will contain data for 1998 and should be placed in the DATA01 tablespace