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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
7.把两边表空间读写
SQL>alter tablespace read write;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29492784/viewspace-1208956/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29492784/viewspace-1208956/