oracle导出表结构

在cmd中输入 exp username/password@连接串

回车在进入如下时输入no就ok了

导出表数据(yes/no)yes> no

进入plsql

找到table那个大项点出来下边会罗列出许多表

右键点中你所需要的那个表名

找到DBMS_Metadata

然后选项里有ddl

弹出来那个窗口就是你的表结构拷贝出来直接在另一个库里执行就可以啦

补充

PLSQL里

tools下

export user objects of

按shift批量选择表

执行就行了

exp/imp工具

带参数rows=y —— 带数据导出导入

rows=n —— 不带数据的导出导入只移植结构

只导出张表的结构

exp user/pasword@dbServerName owner=user tables=(tbtbtb) rows=n file=c:\dmp

连带数据导出

exp user/pasword@dbServerName owner=user tables=(tbtbtb) rows=y file=c:\dmp

imp user/pasword@dbServerName fromuser=user touser=user file=c:\dmp

或者

imp user/pasword@dbServerName fromuser=user touser=user file=c:\dmp

方法一

exp userid=scott/tiger owner=scott

imp userid=scott/tiger full=y indexfile=scottsql

……

more scottsql

REM CREATE TABLE SCOTTBONUS (ENAME VARCHAR() JOB

REM VARCHAR() SAL NUMBER COMM NUMBER) PCTFREE PCTUSED

REM INITRANS MAXTRANS STORAGE(INITIAL FREELISTS FREELIST

REM GROUPS ) TABLESPACE USERS LOGGING NOCOMPRESS ;

REM rows

REM CREATE TABLE SCOTTDEPT (DEPTNO NUMBER( ) DNAME

REM VARCHAR() LOC VARCHAR()) PCTFREE PCTUSED INITRANS

REM MAXTRANS STORAGE(INITIAL FREELISTS FREELIST GROUPS )

REM TABLESPACE USERS LOGGING NOCOMPRESS ;

REM rows

REM CREATE TABLE SCOTTDUMMY (DUMMY NUMBER) PCTFREE PCTUSED

REM INITRANS MAXTRANS STORAGE(INITIAL FREELISTS FREELIST

REM GROUPS ) TABLESPACE USERS LOGGING NOCOMPRESS ;

REM rows

REM CREATE TABLE SCOTTEMP (EMPNO NUMBER( ) NOT NULL ENABLE

REM ENAME VARCHAR() JOB VARCHAR() MGR NUMBER( )

REM HIREDATE DATE SAL NUMBER( ) COMM NUMBER( ) DEPTNO

REM NUMBER( )) PCTFREE PCTUSED INITRANS MAXTRANS

REM STORAGE(INITIAL FREELISTS FREELIST GROUPS ) TABLESPACE

REM USERS LOGGING NOCOMPRESS ;

REM rows

REM CREATE TABLE SCOTTSALGRADE (GRADE NUMBER LOSAL NUMBER

REM HISAL NUMBER) PCTFREE PCTUSED INITRANS MAXTRANS

REM STORAGE(INITIAL FREELISTS FREELIST GROUPS ) TABLESPACE

REM USERS LOGGING NOCOMPRESS ;

REM rows

…………

把前面的REM去了再去掉最后一行创建表的DDL就OK了

方法二

set pagesize

set long

set feedback off

set echo off

spool get_allddlsql

connect USERNAME/PASSWORD@SID;

SELECT DBMS_METADATAGET_DDL(TABLEutable_name)

FROM USER_TABLES u;

SELECT DBMS_METADATAGET_DDL(INDEXuindex_name)

FROM USER_INDEXES u;

spool off;

My Test:

set pagesize

set long

set feedback off

set echo off

spool get_allddlsql

connect username/password@database;

SELECT DBMS_METADATAGET_DDL(TABLEutable_name)

FROM USER_TABLES u where table_name = USER_INFO;

spool off;

SET HEADING OFF;

SET ECHO OFF;

SET LONG ;

SPOOL D:\testtxt

SELECT dbms_metadataget_ddl(TABLEUSER_INFO) FROM SYSDBA_USERS WHERE USERNAME = GINGKO;

SPOOL OFF;