将官档中imp实验一一转为自己的内容.
利用exp导出的scott用户emp表文件,在重新导入时将scott用户的表文件重新定义成分区表.
官方示例:
Example 3: Repartitioning a Table on a Different Column
ps. 这次先在word里面编辑的,然后粘到空间上,格式变得真难看呀. 附地址留用.pdf文件下载地址:http://space.itpub.net/?uid-11780477-action-viewspace-itemid-742445
二 操作环境
OS info
$ lsb_release -a
LSB Version: :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: OracleServer
Description: Oracle Linux Server release 6.2
Release: 6.2
Codename: n/a
$
$uname -a
Linux stu00 2.6.32-300.3.1.el6uek.i686 #1 SMP Fri Dec 9 18:39:00 EST 2011 i686 i686 i386 GNU/Linux
DB info
$export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
$sqlplus / as sysdba <
> set lines 150
> COL PRODUCT FORMAT A55
> COL VERSION FORMAT A15
> COL STATUS FORMAT A15
> SELECT * FROM PRODUCT_COMPONENT_VERSION;
> col value for a50
> set pages 50
> select * from nls_database_parameters;
>
> archive log list;
> EOF
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Aug 25 18:35:24 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
SQL> SQL> SQL> SQL> SQL>
PRODUCT VERSION STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL 10.2.0.4.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.4.0 Prod
PL/SQL 10.2.0.4.0 Production
TNS for Linux: 10.2.0.4.0 Production
SQL> SQL> SQL>
PARAMETER VALUE
------------------------------------------------------------ --------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 10.2.0.4.0
20 rows selected.
SQL> SQL> Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 61
Next log sequence to archive 63
Current log sequence 63
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
$
other
脚本文件存储目录: /home/oracle/db_script/exp_imp
三 过程设计
3.1 exp导出表scott.emp到dmp文件
3.2 从数据库中drop scott.emp表
3.3 创建新定义的scott.emp分区表
create table emp
(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
partition by range (deptno)
(
partition dept_low values less than (15)
tablespace users,
partition dept_mid values less than (25)
tablespace users,
partition dept_high values less than (35)
tablespace users
);
3.4 imp导入
3.5 查询
四 详细步骤操作
4.1 exp导出表scott.emp到dmp文件
$export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
$export ORACLE_SID=testa
$exp scott/tiger tables=emp file=empexp.dat
Export: Release 10.2.0.4.0 - Production on Sat Aug 25 19:07:15 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.
$
(注:因为数据库中的字符集设置为WE8ISO8859P1,因此为了防止EXP-00091 Exporting questionable statistics 错误产生,将exp客户端也设置字符集为WE8ISO8859P1.
4.2 从数据库中drop scott.emp表
$sqlplus scott/tiger
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Aug 25 19:10:16 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
SQL> drop table emp;
Table dropped.
SQL>
4.3 创建新定义的scott.emp分区表
SQL> drop table emp;
Table dropped.
SQL> create table emp
2 (
3 empno number(4) not null,
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7,2),
9 comm number(7,2),
10 deptno number(2)
11 )
12 partition by range (deptno)
13 (
14 partition dept_low values less than (15)
tablespace users,
partition dept_mid values less than (25)
15 16 17 tablespace users,
18 partition dept_high values less than (35)
19 tablespace users
20 );
Table created.
SQL>
4.4 imp导入
$imp scott/tiger tables=emp file=empexp.dat ignore=y
Import: Release 10.2.0.4.0 - Production on Sat Aug 25 19:20:16 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
$
4.5 查询
SQL> conn scott/tiger
Connected.
SQL> select empno, deptno from emp partition (dept_low);
EMPNO DEPTNO
---------- ----------
7782 10
7839 10
7934 10
SQL> select empno, deptno from emp partition (dept_mid);
EMPNO DEPTNO
---------- ----------
7369 20
7566 20
7788 20
7876 20
7902 20
SQL>select empno, deptno from emp partition (dept_high);
EMPNO DEPTNO
---------- ----------
7499 30
7521 30
7654 30
7698 30
7844 30
7900 30
6 rows selected.
SQL>
五 个人总结
利用imp重定义表,将表导出到dmp文件后.再将表删除重新定义.imp的时候添加ignore=y忽视创建时的错误就可以了.这个缺点就是得删表,对应用会有影响.在线重定义相对来说倒是方便的多了.
六 资料参考引用
Oracle® Database Utilities
10g Release 2 (10.2)
Part Number B14215-01
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-742442/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-742442/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
