溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

dba_tables視圖學習

發(fā)布時間：2020-08-10 18:11:45 來源：ITPUB博客 閱讀：520 作者：yhluffy 欄目：關(guān)系型數(shù)據(jù)庫
DBA_TABLES描述數(shù)據(jù)庫中的所有關(guān)系表。它的列與ALL_TABLES中的列相同。要收集此視圖的統(tǒng)計信息，請使用DBMS_STATS包。
ALL_TABLES描述當前用戶可以訪問的關(guān)系表。要收集此視圖的統(tǒng)計信息，請使用DBMS_STATS包。
USER_TABLES描述當前用戶擁有的關(guān)系表。此視圖不顯示所有者列。
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
指定表所屬的表空間，但是通過查詢可以發(fā) 現(xiàn)有一部分 tablespace 為空的表，一般情況 下要么是分區(qū)表，要么是臨時表，要么是索引組織表（iot  type）
CLUSTER_NAME
VARCHAR2(30)
Name of the cluster, if any, to which the table belongs
Oracle 提供了多種數(shù)據(jù)表存儲結(jié)構(gòu)。我們最常見的就是三種，分別為堆表（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
數(shù)據(jù)塊中剩余百分比的最小值，分區(qū)表的話此列為空
PCT_USED
NUMBER
Minimum percentage of used space in a block; NULL for partitioned tables
數(shù)據(jù)塊中使用百分比的最小值，分區(qū)表的話此列為空
INI_TRANS
NUMBER
Initial number of transactions; NULL for partitioned tables
事務(wù)的初始化值，分區(qū)表的話此列為
MAX_TRANS
NUMBER
Maximum number of transactions; NULL for partitioned tables
事務(wù)的最大值，分區(qū)表的話此列為空
INITIAL_EXTENT
NUMBER
Size of the initial extent (in bytes); NULL for partitioned tables
初始化 extent 大?。ㄒ宰止?jié)為單位），分區(qū)表的話此列為空
NEXT_EXTENT
NUMBER
Size of secondary extents (in bytes); NULL for partitioned tables
下一個 extent 分配大小，分區(qū)表的話此列為空
MIN_EXTENTS
NUMBER
Minimum number of extents allowed in the segment; NULL for partitioned tables
段中分配的區(qū)中的最小值，分區(qū)表的話此列為空
MAX_EXTENTS
NUMBER
Maximum number of extents allowed in the segment; NULL for partitioned tables
段中分配的區(qū)中的最大值，分區(qū)表的話此列為空
PCT_INCREASE
NUMBER
Percentage increase in extent size; NULL for partitioned tables
在 extents 中，增長的比例，分區(qū)表的話此列為空
FREELISTS
NUMBER
Number of process freelists allocated to the segment; NULL for partitioned tables
分配到段中自由列表的數(shù)量，分區(qū)表的話此列為空
FREELIST_GROUPS
NUMBER
Number of freelist groups allocated to the segment; NULL for partitioned tables
分配到段中的自由列表組數(shù)量，分區(qū)表的話此列為空
LOGGING
VARCHAR2(3)
Indicates whether or not changes to the table are logged; NULL for partitioned tables:
  • YES
  • NO
是否記錄日志，分區(qū)表的話此列為空
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
表的行數(shù)
BLOCKS*
NUMBER
Number of used data blocks in the table
表使用過的數(shù)據(jù)塊數(shù)
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.
表中的空塊數(shù)，即沒有使用的塊
只有在使用DBMS_STATS包收集表上的統(tǒng)計信息時，才會填充此列
AVG_SPACE*
NUMBER
Average amount of free space, in bytes, in a data block allocated to the table
分配給表的數(shù)據(jù)塊中的平均可用空間量(以字節(jié)為單位)
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
表中從一個數(shù)據(jù)塊鏈接到另一個數(shù)據(jù)塊的行數(shù)，或者已經(jīng)遷移到新塊的行數(shù)，需要一個鏈接來保存舊的ROWID
表中跨越多個塊的行數(shù)量
AVG_ROW_LEN*
NUMBER
Average length of a row in the table (in bytes)
表中一行的平均長度(以字節(jié)為單位)
AVG_SPACE_FREELIST _BLOCKS
NUMBER
Average freespace of all blocks on a freelist
自由列表中所有塊的平均自由空間
NUM_FREELIST_BLOCKS
NUMBER
Number of blocks on the freelist
自由列表上的塊數(shù)
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)
是否是要在緩沖區(qū)高速緩存 ( 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)
是否是分區(qū)表
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 方法創(chuàng)建的輔助對象
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:
  • DEFAULT
  • KEEP
  • RECYCLE
  • NULL
表對象的默認 buffer,如果沒有被緩存到buffer cache，則顯示為null；分區(qū)表顯示為 NULL
FLASH_CACHE
VARCHAR2(7)
Database Smart Flash Cache hint to be used for table blocks:（11g才有）
  • DEFAULT
  • KEEP
  • NONE
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:
  • DEFAULT
  • KEEP
  • NONE
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)
對于分區(qū)表，指示整個表的統(tǒng)計信息(全局統(tǒng)計信息)是否準確(YES)，或者是否沒有收集這些信息，并且必須根據(jù)底層分區(qū)和子分區(qū)的統(tǒng)計信息進行估計(NO)
作為一個整體（分區(qū)表）表的統(tǒng)計的是否準確表示是否被用戶統(tǒng)計信息 ( YES ) or ( NO )
USER_STATS
VARCHAR2(3)
Indicates whether statistics were entered directly by the user (YES) or not (NO)
表示是否被用戶統(tǒng)計信息 ( YES ) or ( NO )
DURATION
VARCHAR2(15)
Indicates the duration of a temporary table:
  • SYS$SESSION   - Rows are preserved for the duration of the session
  • SYS$TRANSACTION   - Rows are deleted after   COMMIT
Null - Permanent table
如果是臨時表，則表的持續(xù)時間:
? SYS$SESSION : the rows are preserved  for the duration of the session
? SYS$TRANSACTION : the rows are  deleted after COMMIT
分區(qū)表顯示為 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的塊. 
如果要起用，則執(zhí)行 DBMS_REPAIR . SKIP_CORRUPT_BLOCKS  
MONITORING
VARCHAR2(3)
Indicates whether the table has the MONITORING attribute set (YES) or not (NO)
表是否設(shè)置了 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才有）
  • BASIC
  • OLTP
  • QUERY LOW
  • QUERY HIGH
  • ARCHIVE LOW
  • ARCHIVE HIGH
  • NULL
表壓縮的類型
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才有）
表的段是否創(chuàng)建
RESULT_CACHE
VARCHAR2(7)
Result cache mode annotation for the table:     （11g才有）
  • DEFAULT   - Table has not been annotated
  • FORCE
  • MANUAL
結(jié)果緩存中是否表注釋


獲取表的DDL的方法：
1）利用 DBMS_METADATA.GET_DDL，獲取表的 DDL 語句
2）imp.indexfile 和 impdp.sqlfile

1）利用 DBMS_METADATA.GET_DDL，獲取表的 DDL 語句
參考: How To Obtain Table DDL Without Using DBMS_METADATA.GET_DDL(文檔 ID 1922301.1)

首先運行
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
define schema=&1
輸入schema的名字
然后執(zhí)行：
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
         ,2
或者 
set 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）先導出用戶的數(shù)據(jù)
[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

導出用戶數(shù)據(jù)
[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;
向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手