November 15 2014

传输表空间不会带序列,存储过程等

SQL> select name from v$database;

NAME
———
PROD

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u01/app/oracle/oradata/PROD/users01.dbf
/u01/app/oracle/oradata/PROD/undotbs01.dbf
/u01/app/oracle/oradata/PROD/sysaux01.dbf
/u01/app/oracle/oradata/PROD/system01.dbf

SQL>
SQL> create tablespace testseq datafile ‘/u01/app/oracle/oradata/PROD/testseq.dbf’ size 50M;

Tablespace created.

SQL> create user testseq identified by testseq default tablespace testseq;

User created.

SQL> grant dba to testseq;

Grant succeeded.

SQL> conn testseq/testseq
Connected.
SQL> create sequence nocache ;

Sequence created.

SQL> select nocache.nextval from dual;

NEXTVAL
———-
1

SQL> /
NEXTVAL
———-
2

SQL> /

NEXTVAL
———-
3

SQL> CREATE SEQUENCE CACHEd CACHE 20;

Sequence created.

SQL>
SQL> select CACHEd.nextval from dual;

NEXTVAL
———-
1

SQL> /

NEXTVAL
———-
2

SQL> create table t as select * from dba_objects;

Table created.

SQL> select object_name from user_objects;

OBJECT_NAME
——————————————————————————–
NOCACHE
CACHED
T

SQL> alter tablespace testseq read only;

Tablespace altered.

SQL>

 

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ucjmh ~]$ expdp system/oracle directory=dmp dumpfile=testseq.dmp transport_tablespaces=testseq;

Export: Release 11.2.0.3.0 – Production on Sun Nov 2 05:22:51 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″: system/******** directory=dmp dumpfile=testseq.dmp transport_tablespaces=testseq
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/dmp/testseq.dmp
******************************************************************************
Datafiles required for transportable tablespace TESTSEQ:
/u01/app/oracle/oradata/PROD/testseq.dbf
Job “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 05:23:38

[oracle@ucjmh ~]$ export ORACLE_SID=EMREP
[oracle@ucjmh ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 2 05:24:30 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@ucjmh ~]$ export ORACLE_SID=emrep
[oracle@ucjmh ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 2 05:24:36 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user testseq identified by testseq;

User created.

SQL> grant dba to testseq;

Grant succeeded.

SQL> exit

 

 

[oracle@ucjmh ~]$ impdp system/oracle dumpfile=’testseq.dmp’ directory=dmp transport_datafiles=’/u01/app/oracle/oradata/emrep/testseq.dbf’

Import: Release 11.2.0.3.0 – Production on Sun Nov 2 05:28:23 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″: system/******** dumpfile=testseq.dmp directory=dmp transport_datafiles=/u01/app/oracle/oradata/emrep/testseq.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at 05:28:26

[oracle@ucjmh ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 2 05:28:31 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn testseq/testseq
Connected.
SQL> select object_name from user_objects;

OBJECT_NAME
——————————————————————————–
T



Copyright 2019. All rights reserved.

Posted 2014年11月15日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *