没有复制功能,顶多创建一个新的表空间PDdta,用相同用户登录, CREATE NEWTABLE TABLESPACE PDdta AS SELECT * FROM OLDTABLE; 因为同一个用户下,不可以有两格相同的表对象。
如果表实在太多,用SPOOL的方法生成脚本来运行:SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='TEST';TABLE_NAME ------------------------------ B BYJC_Z1 MLJC_Z1 SYJC_Z1 T1 TBL TBL2 TDATE TEST TT T_COL_ROW T_COL_STR T_ROW_COL T_ROW_STR14 rows selectedSQL> SPOOL C:\CREATETBL.SQL Started spooling to C:\CREATETBL.SQLSQL> SELECT 'CREATE TABLE '||TABLE_NAME||'_1 TABLESPACE PDdta AS SELECT * FROM '||TABLE_NAME||';' FROM DBA_TABLES WHERE OWNER='TEST';'CREATETABLE'||TABLE_NAME||'_1 -------------------------------------------------------------------------------- CREATE TABLE B_1 TABLESPACE PDdta AS SELECT * FROM B; CREATE TABLE BYJC_Z1_1 TABLESPACE PDdta AS SELECT * FROM BYJC_Z1; CREATE TABLE MLJC_Z1_1 TABLESPACE PDdta AS SELECT * FROM MLJC_Z1; CREATE TABLE SYJC_Z1_1 TABLESPACE PDdta AS SELECT * FROM SYJC_Z1; CREATE TABLE T1_1 TABLESPACE PDdta AS SELECT * FROM T1; CREATE TABLE TBL_1 TABLESPACE PDdta AS SELECT * FROM TBL; CREATE TABLE TBL2_1 TABLESPACE PDdta AS SELECT * FROM TBL2; CREATE TABLE TDATE_1 TABLESPACE PDdta AS SELECT * FROM TDATE; CREATE TABLE TEST_1 TABLESPACE PDdta AS SELECT * FROM TEST; CREATE TABLE TT_1 TABLESPACE PDdta AS SELECT * FROM TT; CREATE TABLE T_COL_ROW_1 TABLESPACE PDdta AS SELECT * FROM T_COL_ROW; CREATE TABLE T_COL_STR_1 TABLESPACE PDdta AS SELECT * FROM T_COL_STR; CREATE TABLE T_ROW_COL_1 TABLESPACE PDdta AS SELECT * FROM T_ROW_COL; CREATE TABLE T_ROW_STR_1 TABLESPACE PDdta AS SELECT * FROM T_ROW_STR;14 rows selectedSQL> SPOOL OFF; Stopped spooling to C:\CREATETBL.SQLSQL>
冷备再还原阿。
我已经有testdta中有1000张表,想创建一个新的PDdta,数据与原testdta相同.
然后imp username/passwd file=... full=y后面的全是新数据库的用户名密码和文件存放位置。
我要拷贝现有的tablespace及里面的表
我已经有testdta中有1000张表,想创建一个新的PDdta,数据与原testdta相同.而testdta保留..
CREATE NEWTABLE TABLESPACE PDdta
AS SELECT * FROM OLDTABLE;
因为同一个用户下,不可以有两格相同的表对象。
------------------------------
B
BYJC_Z1
MLJC_Z1
SYJC_Z1
T1
TBL
TBL2
TDATE
TEST
TT
T_COL_ROW
T_COL_STR
T_ROW_COL
T_ROW_STR14 rows selectedSQL> SPOOL C:\CREATETBL.SQL
Started spooling to C:\CREATETBL.SQLSQL> SELECT 'CREATE TABLE '||TABLE_NAME||'_1 TABLESPACE PDdta AS SELECT * FROM '||TABLE_NAME||';' FROM DBA_TABLES WHERE OWNER='TEST';'CREATETABLE'||TABLE_NAME||'_1
--------------------------------------------------------------------------------
CREATE TABLE B_1 TABLESPACE PDdta AS SELECT * FROM B;
CREATE TABLE BYJC_Z1_1 TABLESPACE PDdta AS SELECT * FROM BYJC_Z1;
CREATE TABLE MLJC_Z1_1 TABLESPACE PDdta AS SELECT * FROM MLJC_Z1;
CREATE TABLE SYJC_Z1_1 TABLESPACE PDdta AS SELECT * FROM SYJC_Z1;
CREATE TABLE T1_1 TABLESPACE PDdta AS SELECT * FROM T1;
CREATE TABLE TBL_1 TABLESPACE PDdta AS SELECT * FROM TBL;
CREATE TABLE TBL2_1 TABLESPACE PDdta AS SELECT * FROM TBL2;
CREATE TABLE TDATE_1 TABLESPACE PDdta AS SELECT * FROM TDATE;
CREATE TABLE TEST_1 TABLESPACE PDdta AS SELECT * FROM TEST;
CREATE TABLE TT_1 TABLESPACE PDdta AS SELECT * FROM TT;
CREATE TABLE T_COL_ROW_1 TABLESPACE PDdta AS SELECT * FROM T_COL_ROW;
CREATE TABLE T_COL_STR_1 TABLESPACE PDdta AS SELECT * FROM T_COL_STR;
CREATE TABLE T_ROW_COL_1 TABLESPACE PDdta AS SELECT * FROM T_ROW_COL;
CREATE TABLE T_ROW_STR_1 TABLESPACE PDdta AS SELECT * FROM T_ROW_STR;14 rows selectedSQL> SPOOL OFF;
Stopped spooling to C:\CREATETBL.SQLSQL>
至于下面的表,则可以用exp,imp来快速复制