问题描述:
1。想要导出用户CAI下的所有表结构到sql文件
2。网上搜索的解决方案如下:
需要登陆oralce用户: sqlplus 用户名/密码@实例名 SQL> set serveroutput on
SQL> set long 999999
SQL> set feedback off
SQL> spool temp.sql
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',table_name) FROM USER_TABLES;
SQL> spool off 这样就可以将表结构导出到temp.sql文件了。 3。我登录的CAI用户,运行这些SQL,得到如下信息:
SQL> select DBMS_METADATA.GET_DDL("TABLE",table_name) FROM USER_TABLES;
select DBMS_METADATA.GET_DDL("TABLE",table_name) FROM USER_TABLES
*
ERROR at line 1:
ORA-00904: "TABLE": invalid identifier请问问题在哪里,哪位大侠能解释下上面select语句,并给出解决方案。不胜感激!
1。想要导出用户CAI下的所有表结构到sql文件
2。网上搜索的解决方案如下:
需要登陆oralce用户: sqlplus 用户名/密码@实例名 SQL> set serveroutput on
SQL> set long 999999
SQL> set feedback off
SQL> spool temp.sql
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',table_name) FROM USER_TABLES;
SQL> spool off 这样就可以将表结构导出到temp.sql文件了。 3。我登录的CAI用户,运行这些SQL,得到如下信息:
SQL> select DBMS_METADATA.GET_DDL("TABLE",table_name) FROM USER_TABLES;
select DBMS_METADATA.GET_DDL("TABLE",table_name) FROM USER_TABLES
*
ERROR at line 1:
ORA-00904: "TABLE": invalid identifier请问问题在哪里,哪位大侠能解释下上面select语句,并给出解决方案。不胜感激!
SQL> set long 999999 -- 设置最大可返回的 lob 类型数据的大小
SQL> set feedback off -- 关闭执行查询时的反馈信息(查询影响的行数)
SQL> spool temp.sql -- 将查询结果保存到 temp.sql 文件
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',table_name) FROM USER_TABLES; -- 通过 dbms_metadata.get_ddl 函数获取用户表的定义
SQL> spool off -- 关闭保存lz 的问题是 'TABLE',单引号。
权限要足够
select DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME) FROM USER_TABLES
scott@LYMORA> select 'select dbms_metadataa.get_ddl(''TABLE'',' from dual;'SELECTDBMS_METADATAA.GET_DDL(''TABLE'','
----------------------------------------------------------------------------
select dbms_metadataa.get_ddl('TABLE',已选择 1 行。已用时间: 00: 00: 00.03
scott@LYMORA> select 'select dbms_metadataa.get_ddl(''TABLE'','''||table_name||''');'
2 from user_tables;'SELECTDBMS_METADATAA.GET_DDL(''TABLE'','''||TABLE_NAME||''');'
--------------------------------------------------------------------------------------------------------
select dbms_metadataa.get_ddl('TABLE','TTT');
select dbms_metadataa.get_ddl('TABLE','DEPT');
select dbms_metadataa.get_ddl('TABLE','EMP');
select dbms_metadataa.get_ddl('TABLE','BONUS');
select dbms_metadataa.get_ddl('TABLE','SALGRADE');
select dbms_metadataa.get_ddl('TABLE','FRUIT_TB');
select dbms_metadataa.get_ddl('TABLE','SALE_ADDR');
select dbms_metadataa.get_ddl('TABLE','SALE_AMOUNT');
select dbms_metadataa.get_ddl('TABLE','TDATE');
select dbms_metadataa.get_ddl('TABLE','T1');
select dbms_metadataa.get_ddl('TABLE','TT');
select dbms_metadataa.get_ddl('TABLE','TEMP_STATISTICS');
select dbms_metadataa.get_ddl('TABLE','B');
select dbms_metadataa.get_ddl('TABLE','C');
select dbms_metadataa.get_ddl('TABLE','AREA_NUM');
select dbms_metadataa.get_ddl('TABLE','TELL');
select dbms_metadataa.get_ddl('TABLE','EMPT');
select dbms_metadataa.get_ddl('TABLE','ORD');
select dbms_metadataa.get_ddl('TABLE','XS');
select dbms_metadataa.get_ddl('TABLE','T_MODEL_TYPE');
select dbms_metadataa.get_ddl('TABLE','TEST1');
select dbms_metadataa.get_ddl('TABLE','TEACHER3');
select dbms_metadataa.get_ddl('TABLE','TEACHER2');
select dbms_metadataa.get_ddl('TABLE','TEACHER');
select dbms_metadataa.get_ddl('TABLE','YUQINGDONGTAI');
select dbms_metadataa.get_ddl('TABLE','A');
select dbms_metadataa.get_ddl('TABLE','T_LOG');
select dbms_metadataa.get_ddl('TABLE','T');
select dbms_metadataa.get_ddl('TABLE','TEST_TAB');
select dbms_metadataa.get_ddl('TABLE','FRUIT');
select dbms_metadataa.get_ddl('TABLE','VELTRACE');
select dbms_metadataa.get_ddl('TABLE','TB_TEST');
select dbms_metadataa.get_ddl('TABLE','MEM_FRUIT');已选择33行。已用时间: 00: 00: 00.18
scott@LYMORA> spool off;
scott@LYMORA>
select dbms_metadataa.get_ddl('TABLE','TTT');
select dbms_metadataa.get_ddl('TABLE','DEPT');
select dbms_metadataa.get_ddl('TABLE','EMP');
select dbms_metadataa.get_ddl('TABLE','BONUS');
select dbms_metadataa.get_ddl('TABLE','SALGRADE');
select dbms_metadataa.get_ddl('TABLE','FRUIT_TB');
select dbms_metadataa.get_ddl('TABLE','SALE_ADDR');
select dbms_metadataa.get_ddl('TABLE','SALE_AMOUNT');
select dbms_metadataa.get_ddl('TABLE','TDATE');
select dbms_metadataa.get_ddl('TABLE','T1');
select dbms_metadataa.get_ddl('TABLE','TT');
select dbms_metadataa.get_ddl('TABLE','TEMP_STATISTICS');
select dbms_metadataa.get_ddl('TABLE','B');
select dbms_metadataa.get_ddl('TABLE','C');
select dbms_metadataa.get_ddl('TABLE','AREA_NUM');
select dbms_metadataa.get_ddl('TABLE','TELL');
select dbms_metadataa.get_ddl('TABLE','EMPT');
select dbms_metadataa.get_ddl('TABLE','ORD');
select dbms_metadataa.get_ddl('TABLE','XS');
select dbms_metadataa.get_ddl('TABLE','T_MODEL_TYPE');
select dbms_metadataa.get_ddl('TABLE','TEST1');
select dbms_metadataa.get_ddl('TABLE','TEACHER3');
select dbms_metadataa.get_ddl('TABLE','TEACHER2');
select dbms_metadataa.get_ddl('TABLE','TEACHER');
select dbms_metadataa.get_ddl('TABLE','YUQINGDONGTAI');
select dbms_metadataa.get_ddl('TABLE','A');
select dbms_metadataa.get_ddl('TABLE','T_LOG');
select dbms_metadataa.get_ddl('TABLE','T');
select dbms_metadataa.get_ddl('TABLE','TEST_TAB');
select dbms_metadataa.get_ddl('TABLE','FRUIT');
select dbms_metadataa.get_ddl('TABLE','VELTRACE');
select dbms_metadataa.get_ddl('TABLE','TB_TEST');
select dbms_metadataa.get_ddl('TABLE','MEM_FRUIT');
scott@LYMORA> spool D:\scott_tableinfo.txt
scott@LYMORA> select 'select dbms_metadata.get_ddl(''TABLE'','''||table_name||''') from dual;'
2 from user_tables;'SELECTDBMS_METADATA.GET_DDL(''TABLE'','''||TABLE_NAME||''')FROMDUAL;'
------------------------------------------------------------------------------------------------------------------------
select dbms_metadata.get_ddl('TABLE','TTT') from dual;
select dbms_metadata.get_ddl('TABLE','DEPT') from dual;
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
select dbms_metadata.get_ddl('TABLE','BONUS') from dual;
select dbms_metadata.get_ddl('TABLE','SALGRADE') from dual;
select dbms_metadata.get_ddl('TABLE','FRUIT_TB') from dual;
select dbms_metadata.get_ddl('TABLE','SALE_ADDR') from dual;
select dbms_metadata.get_ddl('TABLE','SALE_AMOUNT') from dual;
select dbms_metadata.get_ddl('TABLE','TDATE') from dual;
select dbms_metadata.get_ddl('TABLE','T1') from dual;
select dbms_metadata.get_ddl('TABLE','TT') from dual;
select dbms_metadata.get_ddl('TABLE','TEMP_STATISTICS') from dual;
select dbms_metadata.get_ddl('TABLE','B') from dual;
select dbms_metadata.get_ddl('TABLE','C') from dual;
select dbms_metadata.get_ddl('TABLE','AREA_NUM') from dual;
select dbms_metadata.get_ddl('TABLE','TELL') from dual;
select dbms_metadata.get_ddl('TABLE','EMPT') from dual;
select dbms_metadata.get_ddl('TABLE','ORD') from dual;
select dbms_metadata.get_ddl('TABLE','XS') from dual;
select dbms_metadata.get_ddl('TABLE','T_MODEL_TYPE') from dual;
select dbms_metadata.get_ddl('TABLE','TEST1') from dual;
select dbms_metadata.get_ddl('TABLE','TEACHER3') from dual;
select dbms_metadata.get_ddl('TABLE','TEACHER2') from dual;
select dbms_metadata.get_ddl('TABLE','TEACHER') from dual;
select dbms_metadata.get_ddl('TABLE','YUQINGDONGTAI') from dual;
select dbms_metadata.get_ddl('TABLE','A') from dual;
select dbms_metadata.get_ddl('TABLE','T_LOG') from dual;
select dbms_metadata.get_ddl('TABLE','T') from dual;
select dbms_metadata.get_ddl('TABLE','TEST_TAB') from dual;
select dbms_metadata.get_ddl('TABLE','FRUIT') from dual;
select dbms_metadata.get_ddl('TABLE','VELTRACE') from dual;
select dbms_metadata.get_ddl('TABLE','TB_TEST') from dual;
select dbms_metadata.get_ddl('TABLE','MEM_FRUIT') from dual;已选择33行。已用时间: 00: 00: 00.10
scott@LYMORA> spool off;
scott@LYMORA>-- 整理 D:\scott_tableinfo.txt 文件(保留所有 select dbms_ 数据行就OK啦)
select dbms_metadata.get_ddl('TABLE','TTT') from dual;
select dbms_metadata.get_ddl('TABLE','DEPT') from dual;
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
select dbms_metadata.get_ddl('TABLE','BONUS') from dual;
select dbms_metadata.get_ddl('TABLE','SALGRADE') from dual;
select dbms_metadata.get_ddl('TABLE','FRUIT_TB') from dual;
select dbms_metadata.get_ddl('TABLE','SALE_ADDR') from dual;
select dbms_metadata.get_ddl('TABLE','SALE_AMOUNT') from dual;
select dbms_metadata.get_ddl('TABLE','TDATE') from dual;
select dbms_metadata.get_ddl('TABLE','T1') from dual;
select dbms_metadata.get_ddl('TABLE','TT') from dual;
select dbms_metadata.get_ddl('TABLE','TEMP_STATISTICS') from dual;
select dbms_metadata.get_ddl('TABLE','B') from dual;
select dbms_metadata.get_ddl('TABLE','C') from dual;
select dbms_metadata.get_ddl('TABLE','AREA_NUM') from dual;
select dbms_metadata.get_ddl('TABLE','TELL') from dual;
select dbms_metadata.get_ddl('TABLE','EMPT') from dual;
select dbms_metadata.get_ddl('TABLE','ORD') from dual;
select dbms_metadata.get_ddl('TABLE','XS') from dual;
select dbms_metadata.get_ddl('TABLE','T_MODEL_TYPE') from dual;
select dbms_metadata.get_ddl('TABLE','TEST1') from dual;
select dbms_metadata.get_ddl('TABLE','TEACHER3') from dual;
select dbms_metadata.get_ddl('TABLE','TEACHER2') from dual;
select dbms_metadata.get_ddl('TABLE','TEACHER') from dual;
select dbms_metadata.get_ddl('TABLE','YUQINGDONGTAI') from dual;
select dbms_metadata.get_ddl('TABLE','A') from dual;
select dbms_metadata.get_ddl('TABLE','T_LOG') from dual;
select dbms_metadata.get_ddl('TABLE','T') from dual;
select dbms_metadata.get_ddl('TABLE','TEST_TAB') from dual;
select dbms_metadata.get_ddl('TABLE','FRUIT') from dual;
select dbms_metadata.get_ddl('TABLE','VELTRACE') from dual;
select dbms_metadata.get_ddl('TABLE','TB_TEST') from dual;
select dbms_metadata.get_ddl('TABLE','MEM_FRUIT') from dual;
-- 然后再次 spool:scott@LYMORA> spool D:\scott_tableinfo2.txt
scott@LYMORA> @D:\scott_tableinfo.txt
......scott@LYMORA> spool off;-- 此时:D:\scott_tableinfo2.txt 就是你要的表结构文件!
我的是linux系统,这个文件在/home/oracle/下面。不过还是谢谢你哦
你的SQL里写的scott@LYMORA> select 'select dbms_metadata.get_ddl(''TABLE'','''||table_name||''') from dual;'
2 from user_tables;
括号里面的不需要改动吗?分别代表什么呢?我是菜鸟一只,很抱歉打扰你了
我的是linux的,工作要求。
我装了SQL DEVELOPER 但是不太会用,没有PL/SQL那么强大