我想在oracle中，导出scott用户下所有表的表结构，最好是像informix中的那种形成SQL语句的，可有什么办法吗？菜鸟问题！！！！！
| yuanye1014 回复于：2003-12-17 13:37:34
| 精华区里的先看看...
| | OCPHUI 回复于：2003-12-17 13:40:42
| exp scott/passwd tables=(all table name) rows=n file=filename.dmp
log=logfile.log
至于所有Table 的名稱﹐可以通過sql取得。
| | wqpost 回复于：2003-12-25 19:28:41
| 我只想导出表结构呀，如导出来的是
create table
create index
什么的
| | lu.qinyuan 回复于：2003-12-25 21:56:21
| 用下面的脚本可以生成某个用户下的表的DDL语句：
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool tbl_&&SCHEMA_NAME
set termout off
col x noprint
col y noprint
select 'rem **** Create Table DDL for '||chr(10)||
'rem **** '||username||''''||'s tables'||chr(10)||chr(10)
from dba_users
where username = upper ('&&1')
/
select table_name y,
0 x,
'create table ' ||
rtrim(table_name) ||
'('
from dba_tables
where owner = upper('&&1')
union
select tc.table_name y,
column_id x,
rtrim(decode(column_id,1,null,','))||
rtrim(column_name)|| ' ' ||
rtrim(data_type) ||
rtrim(decode(data_type,'DATE',null,'LONG',null,
'NUMBER',decode(to_char(data_precision),null,null,'('),
'(')) ||
rtrim(decode(data_type,
'DATE',null,
'CHAR',data_length,
'VARCHAR2',data_length,
'NUMBER',decode(to_char(data_precision),null,null,
to_char(data_precision) || ',' || to_char(data_scale)),
'LONG',null,
'******ERROR')) ||
rtrim(decode(data_type,'DATE',null,'LONG',null,
'NUMBER',decode(to_char(data_precision),null,null,')'),
')')) || ' ' ||
rtrim(decode(nullable,'N','NOT NULL',null))
from dba_tab_columns tc,
dba_objects o
where o.owner = tc.owner
and o.object_name = tc.table_name
and o.object_type = 'TABLE'
and o.owner = upper('&&1')
union
select table_name y,
999999 x,
')' || chr(10)
||' STORAGE(' || chr(10)
||' INITIAL ' || initial_extent || chr(10)
||' NEXT ' || next_extent || chr(10)
||' MINEXTENTS ' || min_extents || chr(10)
||' MAXEXTENTS ' || max_extents || chr(10)
||' PCTINCREASE '|| pct_increase || ')' ||chr(10)
||' INITRANS ' || ini_trans || chr(10)
||' MAXTRANS ' || max_trans || chr(10)
||' PCTFREE ' || pct_free || chr(10)
||' PCTUSED ' || pct_used || chr(10)
||' PARALLEL (DEGREE ' || DEGREE || ') ' || chr(10)
||' TABLESPACE ' || rtrim(tablespace_name) ||chr(10)
||'/'||chr(10)||chr(10)
from dba_tables
where owner = upper('&&1')
order by 1,2
/
| | eagle_fan 回复于：2003-12-26 09:08:26
| perfect
| | orclist 回复于：2003-12-26 10:08:17
| 使用第三方的工具，如TOAD，会便利一些。
| | text2002 回复于：2003-12-26 13:15:15
| [quote:1d6234df0f="lu.qinyuan"]用下面的脚本可以生成某个用户下的表的DDL语句：
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool tbl_&&..........[/quote:1d6234df0f]
不懂。。
这个sqlplus脚本不能产生一下吗？我用create as...导出来的。
CREATE TABLE "SYSTEM"."SCH_BASE_TAB" ("SCH_CODE" NUMBER(6)
DEFAULT 0 NOT NULL, "NAME" NVARCHAR2(30) DEFAULT '', "FOR_SHORT"
NVARCHAR2(20) DEFAULT '', "ADDRESS" NVARCHAR2(60) DEFAULT '',
"POSTCODE" NCHAR(6) DEFAULT '', "PHONE" NVARCHAR2(30)
DEFAULT '', "HOMEPAGE" NVARCHAR2(30) DEFAULT '', "DEFA_SCH_OFF"
NUMBER(7) DEFAULT 0,
CONSTRAINT "SCH_BASE_TAB_PK" PRIMARY KEY("SCH_CODE")
USING INDEX
TABLESPACE "INDX"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PCTFREE 10 INITRANS 2 MAXTRANS 255)
TABLESPACE "KCGX" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING;
| | lu.qinyuan 回复于：2003-12-26 21:30:48
| [quote:3d93c86ae3="text2002"]
不懂。。
这个sqlplus脚本不能产生一下吗？我用create as...导出来的。
CREATE TABLE "SYSTEM"."SCH_BASE_TAB" ("SCH_CODE" NUMBER(6)
DEFAULT 0 NOT NULL, "NAME" NVARCHAR2(30) DEFAULT '', "FOR..........[/quote:3d93c86ae3]
能不能说的详细一点，怎么用create as 得到的？
| | doni 回复于：2003-12-27 00:28:27
| exp .... rows=n ...
就是导出表结构
| | yllmm80512 回复于：2003-12-28 15:38:40
| exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000
| | rman9i 回复于：2004-01-31 03:04:22
| 试一下这个：
c:\>exp userid=scott/tiger owner=scott
c:\>imp userid=scott/tiger full=y indexfile=scott.sql
注意着时import并没有真正写入数据库，而是将DDL写在scott.sql里。
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去了，再去掉最后一行。创建表的DDL就做好了。
| | taocb006 回复于：2004-02-01 08:35:59
| set pagesize 0
set long 90000
set feedback off
set echo off
spool get_allddl.sql
connect [color=orange:cecfd5ad3c]USERNAME/PASSWORD@SID[/color:cecfd5ad3c];
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;
| | jrlee 回复于：2004-02-02 09:39:04
| 表结构可以在DBA_TAB_COLUMNS系统中或视图中找出每个表结构的详细信息。
select * from DBA_tab_columns from OWNER='OWNERNAME'
| | zwen 回复于：2004-02-11 15:23:04
| 菜鸟想知道怎样在php下实现oracle表结构的导出啊？
| |
|
