溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
|
Column |
Datatype |
NULL |
Description |
|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the table
表的擁有者 |
|
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the table
表名 |
|
TABLESPACE_NAME |
VARCHAR2(30) |
|
Name of the tablespace containing the table; NULL for partitioned, temporary, and index-organized tables
指定表所屬的表空間，但是通過查詢可以發
現有一部分 tablespace 為空的表，一般情況
下要么是分區表，要么是臨時表，要么是索引組織表（iot
type） |
|
CLUSTER_NAME |
VARCHAR2(30) |
|
Name of the cluster, if any, to which the table belongs
Oracle 提供了多種數據表存儲結構。我們最常見的就是三種，分別為堆表（HeapTable）、索引組織表（Index Organization Table，簡稱為 IOT）和聚簇表（ClusterTable） |
|
IOT_NAME |
VARCHAR2(30) |
|
Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If the
IOT_TYPE
column is not NULL, then this column contains the base table name.
溢出或映射表條目所屬的索引組織表的名稱(如果有的話)。如果IOT_TYPE列不為空，則此列包含基表名。 |
|
STATUS |
VARCHAR2(8) |
|
If a previous
DROP TABLE
operation failed, indicates whether the table is unusable (
UNUSABLE
) or valid (
VALID
)
如果先前的刪除表操作失敗，則指示該表是不可用的(不可用的)還是有效的(有效的) |
|
PCT_FREE |
NUMBER |
|
Minimum percentage of free space in a block; NULL for partitioned tables
數據塊中剩余百分比的最小值，分區表的話此列為空 |
|
PCT_USED |
NUMBER |
|
Minimum percentage of used space in a block; NULL for partitioned tables
數據塊中使用百分比的最小值，分區表的話此列為空 |
|
INI_TRANS |
NUMBER |
|
Initial number of transactions; NULL for partitioned tables
事務的初始化值，分區表的話此列為 |
|
MAX_TRANS |
NUMBER |
|
Maximum number of transactions; NULL for partitioned tables
事務的最大值，分區表的話此列為空 |
|
INITIAL_EXTENT |
NUMBER |
|
Size of the initial extent (in bytes); NULL for partitioned tables
初始化 extent 大小（以字節為單位），分區表的話此列為空 |
|
NEXT_EXTENT |
NUMBER |
|
Size of secondary extents (in bytes); NULL for partitioned tables
下一個 extent 分配大小，分區表的話此列為空 |
|
MIN_EXTENTS |
NUMBER |
|
Minimum number of extents allowed in the segment; NULL for partitioned tables
段中分配的區中的最小值，分區表的話此列為空 |
|
MAX_EXTENTS |
NUMBER |
|
Maximum number of extents allowed in the segment; NULL for partitioned tables
段中分配的區中的最大值，分區表的話此列為空 |
|
PCT_INCREASE |
NUMBER |
|
Percentage increase in extent size; NULL for partitioned tables
在 extents 中，增長的比例，分區表的話此列為空 |
|
FREELISTS |
NUMBER |
|
Number of process freelists allocated to the segment; NULL for partitioned tables
分配到段中自由列表的數量，分區表的話此列為空 |
|
FREELIST_GROUPS |
NUMBER |
|
Number of freelist groups allocated to the segment; NULL for partitioned tables
分配到段中的自由列表組數量，分區表的話此列為空 |
|
LOGGING |
VARCHAR2(3) |
|
Indicates whether or not changes to the table are logged; NULL for partitioned tables:
是否記錄日志，分區表的話此列為空 |
|
BACKED_UP |
VARCHAR2(1) |
|
Indicates whether the table has been backed up since the last modification (Y) or not (N)
在上一次修改過后是否備份 |
|
NUM_ROWS* |
NUMBER |
|
Number of rows in the table
表的行數 |
|
BLOCKS* |
NUMBER |
|
Number of used data blocks in the table
表使用過的數據塊數 |
|
EMPTY_BLOCKS |
NUMBER |
|
Number of empty (never used) data blocks in the table. This column is populated only if you collect statistics on the table using the DBMS_STATS package.
表中的空塊數，即沒有使用的塊
只有在使用DBMS_STATS包收集表上的統計信息時，才會填充此列 |
|
AVG_SPACE* |
NUMBER |
|
Average amount of free space, in bytes, in a data block allocated to the table
分配給表的數據塊中的平均可用空間量(以字節為單位) |
|
CHAIN_CNT* |
NUMBER |
|
Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID
表中從一個數據塊鏈接到另一個數據塊的行數，或者已經遷移到新塊的行數，需要一個鏈接來保存舊的ROWID
表中跨越多個塊的行數量 |
|
AVG_ROW_LEN* |
NUMBER |
|
Average length of a row in the table (in bytes)
表中一行的平均長度(以字節為單位) |
|
AVG_SPACE_FREELIST _BLOCKS
|
NUMBER |
|
Average freespace of all blocks on a freelist
自由列表中所有塊的平均自由空間
|
|
NUM_FREELIST_BLOCKS
|
NUMBER |
|
Number of blocks on the freelist
自由列表上的塊數 |
|
DEGREE |
VARCHAR2(10) |
|
Number of threads per instance for scanning the table, or DEFAULT
每個實例有多少線程可以同時掃描表或者表的默認并行為 1 |
|
INSTANCES |
VARCHAR2(10) |
|
Number of instances across which the table is to be scanned, or DEFAULT
多少實例可以同時掃描表，默認值為1 |
|
CACHE |
VARCHAR2(5) |
|
Indicates whether the table is to be cached in the buffer cache (Y) or not (N)
是否是要在緩沖區高速緩存 ( Y ) or ( N ) |
|
TABLE_LOCK |
VARCHAR2(8) |
|
Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED)
是否鎖表 ( ENABLED ) or ( DISABLED ) |
|
SAMPLE_SIZE |
NUMBER |
|
Sample size used in analyzing this table
分析這個表所使用的樣本大小 |
|
LAST_ANALYZED |
DATE |
|
Date on which this table was most recently analyzed
最近一次分析表的時間 |
|
PARTITIONED |
VARCHAR2(3) |
|
Indicates whether the table is partitioned (YES) or not (NO)
是否是分區表 |
|
IOT_TYPE |
VARCHAR2(12) |
|
If the table is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING. If the table is not an index-organized table, then IOT_TYPE is NULL.
如果表是一個索引組織的表，那么IOT_TYPE是IOT、IOT_OVERFLOW或IOT_MAPPING。如果表不是索引組織的表，則IOT_TYPE為空。 |
|
TEMPORARY |
VARCHAR2(1) |
|
Indicates whether the table is temporary (Y) or not (N)
是否是臨時表 |
|
SECONDARY |
VARCHAR2(1) |
|
Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N)
是否是通過 ODCIIndexCreate 方法創建的輔助對象 |
|
NESTED |
VARCHAR2(3) |
|
Indicates whether the table is a nested table (YES) or not (NO)
是否是 nested 表 ( YES ) or ( NO |
|
BUFFER_POOL |
VARCHAR2(7) |
|
Buffer pool for the table; NULL for partitioned tables:
表對象的默認 buffer,如果沒有被緩存到buffer cache，則顯示為null；分區表顯示為 NULL |
|
FLASH_CACHE |
VARCHAR2(7) |
|
Database Smart Flash Cache hint to be used for table blocks:（11g才有）
Solaris and Oracle Linux functionality only.
Smart Flash Cache 提示用于表塊(僅限于 Solaris and Oracle Linux) |
|
CELL_FLASH_CACHE |
VARCHAR2(7) |
|
Cell flash cache hint to be used for table blocks:
See Also:
Oracle Exadata Storage Server Software documentation for more information
Cell flash cache 提示用于表塊 |
|
ROW_MOVEMENT |
VARCHAR2(8) |
|
Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED)
行遷移是否開啟 |
|
GLOBAL_STATS |
VARCHAR2(3) |
|
For partitioned tables, indicates whether statistics for the table as a whole (global statistics) are accurate (YES) or whether they were not collected and have to be estimated from statistics on underlying partitions and subpartitions (NO)
對于分區表，指示整個表的統計信息(全局統計信息)是否準確(YES)，或者是否沒有收集這些信息，并且必須根據底層分區和子分區的統計信息進行估計(NO)
作為一個整體（分區表）表的統計的是否準確表示是否被用戶統計信息 ( YES ) or ( NO ) |
|
USER_STATS |
VARCHAR2(3) |
|
Indicates whether statistics were entered directly by the user (YES) or not (NO)
表示是否被用戶統計信息 ( YES ) or ( NO ) |
|
DURATION |
VARCHAR2(15) |
|
Indicates the duration of a temporary table:
Null - Permanent table
如果是臨時表，則表的持續時間:
? SYS$SESSION : the rows are preserved
for the duration of the session
? SYS$TRANSACTION : the rows are
deleted after COMMIT
分區表顯示為 NULL 空 |
|
SKIP_CORRUPT |
VARCHAR2(8) |
|
Indicates whether Oracle Database ignores blocks marked corrupt during table and index scans (ENABLED) or raises an error (DISABLED). To enable this feature, run the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS procedure.
在表和索引掃描時候是否無視標記為 corrupt的塊.
如果要起用，則執行 DBMS_REPAIR . SKIP_CORRUPT_BLOCKS |
|
MONITORING |
VARCHAR2(3) |
|
Indicates whether the table has the MONITORING attribute set (YES) or not (NO)
表是否設置了 MONITORING 屬性 |
|
CLUSTER_OWNER |
VARCHAR2(30) |
|
Owner of the cluster, if any, to which the table belongs
簇表的擁有者 |
|
DEPENDENCIES |
VARCHAR2(8) |
|
Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED)
行級依賴跟蹤是否開啟( ENABLED ) or ( DISABLED ) |
|
COMPRESSION |
VARCHAR2(8) |
|
Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables
表是否壓縮 |
|
COMPRESS_FOR |
VARCHAR2(12) |
|
Default compression for what kind of operations:（11g才有）
表壓縮的類型 |
|
DROPPED |
VARCHAR2(3) |
|
Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables
表是否被 DROP 到了回收站中 |
|
READ_ONLY |
VARCHAR2(3) |
|
Indicates whether the table IS READ-ONLY (YES) or not (NO) （11g才有）
表是否是只讀的 |
|
SEGMENT_CREATED |
VARCHAR2(3) |
|
Indicates whether the table segment is created (YES) or not (NO) （11g才有）
表的段是否創建 |
|
RESULT_CACHE |
VARCHAR2(7) |
|
Result cache mode annotation for the table:
（11g才有）
結果緩存中是否表注釋 |
|
|
|
|
|
首先運行
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
define schema=&1
輸入schema的名字
然后執行：
define CR=chr(10)
define TAB=chr(9)
col x noprint
col y noprint
SELECT TABLE_NAME Y
,0 X
,'CREATE TABLE ' || RTRIM(TABLE_NAME) || '('
FROM DBA_TABLES
WHERE OWNER = UPPER('&schema')
UNION
SELECT TC.TABLE_NAME Y
,COLUMN_ID X
,DECODE(COLUMN_ID, 1, ' ', ' ,') || RTRIM(COLUMN_NAME) || &TAB || &TAB ||
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, ')'), ')')) || &TAB || &TAB ||
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('&schema')
UNION
SELECT TABLE_NAME Y
,999999 X
,')' || &CR || ' STORAGE(' || &CR || ' INITIAL ' || INITIAL_EXTENT || &CR ||
' NEXT ' || NEXT_EXTENT || &CR || ' MINEXTENTS ' || MIN_EXTENTS || &CR ||
' MAXEXTENTS ' || MAX_EXTENTS || &CR || ' PCTINCREASE ' ||
PCT_INCREASE || ')' || &CR || ' INITRANS ' || INI_TRANS || &CR ||
' MAXTRANS ' || MAX_TRANS || &CR || ' PCTFREE ' || PCT_FREE || &CR ||
' PCTUSED ' || PCT_USED || &CR || ' PARALLEL (DEGREE ' ||
RTRIM(DEGREE) || ') ' || &CR || ' TABLESPACE ' ||
RTRIM(TABLESPACE_NAME) || &CR || '/' || &CR || &CR
FROM DBA_TABLES
WHERE OWNER = UPPER('&schema')
ORDER BY 1
,2set pagesize 0
set long 90000
set feedback off
set echo off
spool table_ddl.sql
select dbms_metadata.get_ddl('TABLE','tablename','username') from dual;
select dbms_metadata.get_ddl('VIEW','viewname','username') from dual;
select dbms_metadata.get_ddl('INDEX','indexname','username') from dual;
spool off;
例如：
set pagesize 0
set long 90000
set feedback off
set echo off
spool table_ddl.sql
select dbms_metadata.get_ddl('TABLE','DEMO2','DEMO') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_ID_DEMO2','DEMO') from dual;
spool off;
[oracle@oracle11g ~]$ cat table_ddl.sql
SQL> select dbms_metadata.get_ddl('TABLE','DEMO2','DEMO') from dual;
CREATE TABLE "DEMO"."DEMO2"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL> select dbms_metadata.get_ddl('INDEX','IDX_ID_DEMO2','DEMO') from dual;
CREATE INDEX "DEMO"."IDX_ID_DEMO2" ON "DEMO"."DEMO2" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL> spool off;2）imp.indexfile 和 impdp.sqlfile
#示例： 1.indexfile 1）先導出用戶的數據 [oracle@oracle11g ~]$ exp demo/demo file=test.dmp owner=demo log=test.log; 2)從 dump 文件獲取這些 DDL 語句 [oracle@oracle11g ~]$ imp demo/demo file=test.dmp fromuser=demo touser=demo indexfile=test.sql;
2.sqlfile
導出用戶數據 [oracle@oracle11g ~]$ expdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp schemas=demo; 獲取 DDL 語句 [oracle@oracle11g ~]$ impdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp sqlfile=demo.sql;
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
