1.在cmd中輸入 exp username/password@連接串
回車，在進(jìn)入如下時(shí)，輸入no，就ok了
導(dǎo)出表數(shù)據(jù)(yes/no)：yes> no
2.
進(jìn)入plsql
找到table那個(gè)大項(xiàng)，點(diǎn)出來，下邊會羅列出許多表
右鍵點(diǎn)中你所需要的那個(gè)表名
找到DBMS_Metadata
然后選項(xiàng)里有ddl
彈出來那個(gè)窗口就是你的表結(jié)構(gòu)，拷貝出來直接在另一個(gè)庫里執(zhí)行就可以啦
-----------------------------補(bǔ)充------------------------
PLSQL里
tools下
export user objects of
按shift批量選擇表
執(zhí)行就行了
3
exp/imp工具；
帶參數(shù)：rows=y —— 帶數(shù)據(jù)導(dǎo)出導(dǎo)入；
rows=n —— 不帶數(shù)據(jù)的導(dǎo)出導(dǎo)入，只移植結(jié)構(gòu)
只導(dǎo)出3張表的結(jié)構(gòu)：
exp user/pasword@dbServerName owner=user tables=(tb1,tb2,tb3) rows=n file=c:\1.dmp
連帶數(shù)據(jù)導(dǎo)出：
exp user/pasword@dbServerName owner=user tables=(tb1,tb2,tb3) rows=y file=c:\2.dmp
imp user2/pasword@dbServerName2 fromuser=user touser=user2 file=c:\1.dmp
或者
imp user2/pasword@dbServerName2 fromuser=user touser=user2 file=c:\2.dmp
3
方法一：
exp userid=scott/tiger owner=scott
imp userid=scott/tiger full=y indexfile=scott.sql
……
more scott.sql
REM CREATE TABLE "SCOTT"."BONUS" ("ENAME" VARCHAR2(10), "JOB"
REM VARCHAR2(9), "SAL" NUMBER, "COMM" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM ... 0 rows
REM CREATE TABLE "SCOTT"."DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME"
REM VARCHAR2(14), "LOC" VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM ... 4 rows
REM CREATE TABLE "SCOTT"."DUMMY" ("DUMMY" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM ... 1 rows
REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0) NOT NULL ENABLE,
REM "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0),
REM "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO"
REM NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM "USERS" LOGGING NOCOMPRESS ;
REM ... 14 rows
REM CREATE TABLE "SCOTT"."SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER,
REM "HISAL" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM "USERS" LOGGING NOCOMPRESS ;
REM ... 5 rows
…………
把前面的REM去了，再去掉最后一行，創(chuàng)建表的DDL就OK了。
方法二：
set pagesize 0
set long 90000
set feedback off
set echo off
spool get_allddl.sql
connect USERNAME/PASSWORD@SID;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
spool off;
My Test:
set pagesize 0
set long 90000
set feedback off
set echo off
spool get_allddl.sql
connect username/password@database;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u where table_name = 'USER_INFO';
spool off;
SET HEADING OFF;
SET ECHO OFF;
SET LONG 90000;
SPOOL D:\test.txt
SELECT dbms_metadata.get_ddl('TABLE','USER_INFO') FROM SYS.DBA_USERS WHERE USERNAME = 'GINGKO';
SPOOL OFF;