1.字符集

set linesize 160

col property_name for a30

col property_value for a50

select property_name,property_value from database_properties;

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------------------------------------

DICT.BASE 2

DEFAULT_TEMP_TABLESPACE TEMPTS

DEFAULT_PERMANENT_TABLESPACE USERS

DEFAULT_EDITION ORA$BASE

Flashback Timestamp TimeZone GMT

TDE_MASTER_KEY_ID

DBTIMEZONE +08:00

DST_UPGRADE_STATE NONE

DST_PRIMARY_TT_VERSION 11

DST_SECONDARY_TT_VERSION 0

DEFAULT_TBS_TYPE SMALLFILE

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

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_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 11.2.0.1.0

GLOBAL_DB_NAME PROD

EXPORT_VIEWS_VERSION 8

WORKLOAD_CAPTURE_MODE

WORKLOAD_REPLAY_MODE

NO_USERID_VERIFIER_SALT 652952C85DE40F38C40CA3BC55F76876

两个库都查看一下

[oracle@gc1:/home/oracle]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

[oracle@gc1:/home/oracle]$ export ORACLE_SID=PROD

[oracle@gc2:/home/oracle]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

[oracle@gc2:/home/oracle]$ export ORACLE_SID=EMREP

2.导出

可以通过exp help=y查看帮助

[oracle@gc2:/home/oracle]$ exp help=y

Export: Release 11.2.0.1.0 - Production on Sun Jun 1 13:20:07 2014

You can let Export prompt you for parameters by entering the EXP

command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed

by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

--------------------------------------------------------------------------

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

VOLSIZE number of bytes to write to each tape volume

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

-----------导出全库用system用户

[oracle@gc2:/home/oracle]$ exp file=prod_full.dmp full=y

Export: Release 11.2.0.1.0 - Production on Sun Jun 1 13:22:11 2014

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export the entire database ...

. exporting tablespace definitions

. exporting profiles

. exporting user definitions

. exporting roles

. exporting resource costs

. exporting rollback segment definitions

...

-----------导出SCOTT用户用SCOTT登录

[oracle@gc1:/home/oracle]$ exp file=scott.dmp

Export: Release 11.2.0.1.0 - Production on Sun Jun 1 13:30:13 2014

Username: scott

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SCOTT

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SCOTT

About to export SCOTT's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SCOTT's tables via Conventional Path ...

. . exporting table DEPT 4 rows exported

. . exporting table DEPT1 4 rows exported

. . exporting table EMP 14 rows exported

. . exporting table SALGRADE 5 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

-----------导出表(导出emp,dept表,导出谁的表就用谁登录)

[oracle@gc1:/home/oracle]$ exp file=emp_dept.dmp tables=emp,dept

Export: Release 11.2.0.1.0 - Production on Sun Jun 1 13:33:29 2014

Username: scott

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table EMP 14 rows exported

. . exporting table DEPT 4 rows exported

Export terminated successfully without warnings.

-----------按条件导出表里的数据

[oracle@gc1:/home/oracle]$ exp file=emp_10.dmp tables=emp query=\" where deptno=10 \"

Export: Release 11.2.0.1.0 - Production on Sun Jun 1 13:41:33 2014

Username: scott

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table EMP 3 rows exported

Export terminated successfully without warnings.

exp file=emp_james.dmp tables=emp query=\"where enmae=\'JAMES\'\"

-----------误删除SCOTT用户后导入

sys@PROD>drop user scott cascade;

User dropped.

sys@PROD>create user scott identified by tiger;

User created.

sys@PROD>grant connect,resource to scott;

Grant succeeded.

同样可以使用imp help=y 查看帮助

[oracle@gc1:/home/oracle]$ imp file=scott.dmp fromuser=scott touser=scott ignore=y

Import: Release 11.2.0.1.0 - Production on Sun Jun 1 17:31:46 2014

Username: scott

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. . importing table "DEPT" 4 rows imported

. . importing table "DEPT1" 4 rows imported

. . importing table "EMP" 14 rows imported

. . importing table "SALGRADE" 5 rows imported

About to enable constraints...

Import terminated successfully without warnings.

-----------误删除emp表,导入

sys@PROD>delete from scott.emp;

14 rows deleted.

sys@PROD>select * from scott.emp;

no rows selected

[oracle@gc1:/home/oracle]$ imp file=emp_dept.dmp tables=emp ignore=y

-----------将SCOTT用户的表导入BOB

sys@PROD>create user bob identified by bob;

User created.

sys@PROD>grant connect,resource to bob;

Grant succeeded.

bob@PROD>select * from tab;

no rows selected

[oracle@gc1:/home/oracle]$ imp file=scott.dmp fromuser=scott touser=bob ignore=y

Import: Release 11.2.0.1.0 - Production on Sun Jun 1 17:45:44 2014

Username: system

Password:

IMP-00058: ORACLE error 28002 encountered

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

export client uses ZHS16GBK character set (possible charset conversion)

. importing SCOTT's objects into BOB

. . importing table "DEPT" 4 rows imported

. . importing table "DEPT1" 4 rows imported

. . importing table "EMP" 14 rows imported

. . importing table "SALGRADE" 5 rows imported

About to enable constraints...

Import terminated successfully with warnings.

-----------导入有主外键约束的表先到子表后导父表、或者导两次、或者把两个表删了再导

-----------迁移表空间OLTP(官方文档-database administration-administrator guid-control+f transpor-Task 2: Pick a Self-Contained Set of Tablespaces)

1.在两个库上建目录

cd /home/oracle

mkdir dir

SQL>create directory dir as '/home/oracle/dir';

SQL>grant all on directory dir to public

2.在对面创建用户,解锁system

SQL>create user oltp identified by oltp;

SQL>grant connect,resource to oltp;

SQL>alter user system identified by manager account unlock;

3.检查自包含

SQL>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('OLTP', TRUE); 通过官方文档找到

如果有约束,把约束删了

SQL>alter table XXX drop constraint xxx cascade;

4.把表空间只读

SQL>alter tablespace oltp read only;

5.导出,这里用数据泵databump

如果用imp exp要用sys用户，username：sys as sysdba,而databump可以用sys和system用户

[oracle@gc1:/home/oracle]$ expdp dumpfile=oltp.dmp directory=dir transport_tablespaces=oltp

Export: Release 11.2.0.1.0 - Production on Sun Jun 1 13:41:33 2014

Username: system

Password:

导出后,还要将数据文件也拷过去

[oracle@gc1:/home/oracle]$ scp oltp.dmp gc2:/home/oracle/dir

[oracle@gc1:/home/oracle]$ scp /u01/app/oracle/oradata/PROD/disk3/oltp01.dbf gc2:/u01/app/oracle/oradata/EMREP/

检查一下是否过去了

6.导入对库

[oracle@gc1:/home/oracle/dir]$ impdp dumpfile=oltp.dmp directory=dir transport_datafiles='/u01/app/oracle/oradata/EMREP/oltp01.dbf'

Import: Release 11.2.0.1.0 - Production on Sun Jun 1 13:41:33 2014

Username: system

Password:

7.把两边表空间读写

SQL>alter tablespace read write;

ITPub博客

逻辑备份导入导出+传输表空间(OCM)

最新文章