In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
DBA_TABLES describes all the relational tables in the database. Its columns are the same as those in ALL_TABLES. To collect statistics for this view, use the DBMS_STATS package. ALL_TABLES describes the relational tables that the current user can access. To collect statistics for this view, use the DBMS_STATS package. USER_TABLES describes the relationship tables owned by the current user. This view does not display the owner column. Owner of ColumnDatatypeNULLDescriptionOWNERVARCHAR2 (30) NOT NULLOwner of the table table TABLE_NAMEVARCHAR2 (30) NOT NULLName of the table table name TABLESPACE_NAMEVARCHAR2 (30)
Name of the tablespace containing the table; NULL for partitioned, temporary, and and index-organized tables specify the tablespace to which the table belongs, but you can send some existing tables with empty tablespace by query, usually either partitioned tables, temporary tables, or index organization tables (iot type) CLUSTER_NAMEVARCHAR2 (30)
Name of the cluster, if any, to which the table belongs Oracle provide a variety of data table storage structures. The three most common ones are HeapTable, Index Organization Table (IOT) and ClusterTable IOT_NAMEVARCHAR2 (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. The index to which the overflow or mapping table entry belongs organizes the name of the table, if any. If the IOT_TYPE column is not empty, it contains the base table name. STATUSVARCHAR2 (8)
If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID) if the previous delete table operation fails, indicates whether the table is unavailable (unavailable) or valid (valid) PCT_FREENUMBER
The minimum percentage remaining in the Minimum percentage of free space in a block; NULL for partitioned tables block, which is listed as an empty PCT_USEDNUMBER if the table is partitioned
The minimum percentage used in the Minimum percentage of used space in a block; NULL for partitioned tables block, which is listed as an empty INI_TRANSNUMBER if the table is partitioned
The initialization value of the Initial number of transactions; NULL for partitioned tables transaction, which is listed as MAX_TRANSNUMBER if the table is partitioned
The maximum value of the Maximum number of transactions; NULL for partitioned tables transaction, which is listed as an empty INITIAL_EXTENTNUMBER if the table is partitioned
Size of the initial extent (in bytes); NULL for partitioned tables initializes the extent size in bytes, which is listed as an empty NEXT_EXTENTNUMBER if the table is partitioned
Size of secondary extents (in bytes); NULL for partitioned tables the next extent to allocate the size, which is listed as an empty MIN_EXTENTSNUMBER if the table is partitioned
The minimum value in the range allocated in the Minimum number of extents allowed in the segment; NULL for partitioned tables section, which is listed as empty MAX_EXTENTSNUMBER if the table is partitioned
The maximum value in the range allocated in the Maximum number of extents allowed in the segment; NULL for partitioned tables section, which is listed as an empty PCT_INCREASENUMBER if the table is partitioned
Percentage increase in extent size; NULL for partitioned tables in extents, the proportion of growth, partitioned table, then this is listed as empty FREELISTSNUMBER
The number of free lists assigned by Number of process freelists allocated to the segment; NULL for partitioned tables to the segment, which is listed as an empty FREELIST_GROUPSNUMBER if the table is partitioned
The number of free list groups assigned by Number of freelist groups allocated to the segment; NULL for partitioned tables to the segment, which is listed as empty LOGGINGVARCHAR2 (3) if the table is partitioned
Whether Indicates whether or not changes to the table are logged; NULL for partitioned tables:YESNO logs or not. If you partition the table, this column is empty BACKED_UPVARCHAR2 (1)
Whether Indicates whether the table has been backed up since the last modification (Y) or not (N) backs up NUM_ROWS*NUMBER after last modification
Number of rows in the Number of rows in the table table BLOCKS*NUMBER
Number of blocks used by the Number of used data blocks in the table table EMPTY_BLOCKSNUMBER
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. The number of empty blocks in the table, that is, unused blocks, will populate this column AVG_SPACE*NUMBER only if you use the DBMS_STATS package to collect statistics on the table
The average amount of free space (in bytes) CHAIN_CNT*NUMBER in the blocks allocated to the table by Average amount of free space, in bytes, in a data block allocated to the table
The number of rows in the 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 table linked from one block to another, or the number of rows that have been migrated to a new block, requires a link to hold the number of rows in the old ROWID table that span multiple blocks AVG_ROW_LEN*NUMBER
The average length of a row in the Average length of a row in the table (in bytes) table (in bytes) AVG_SPACE_FREELIST _ BLOCKSNUMBER
Average free space NUM_FREELIST_BLOCKSNUMBER of all blocks in Average freespace of all blocks on a freelist free list
Number of blocks on Number of blocks on the freelist Free list DEGREEVARCHAR2 (10)
Number of threads per instance for scanning the table, or DEFAULT how many threads per instance can scan the table or the default and behavior of the table at the same time 1INSTANCESVARCHAR2 (10)
How many instances of Number of instances across which the table is to be scanned and or DEFAULT can scan the table at the same time. The default is 1CACHEVARCHAR2 (5).
Whether Indicates whether the table is to be cached in the buffer cache (Y) or not (N) is to cache (Y) or (N) TABLE_LOCKVARCHAR2 (8) in the buffer
Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED) whether to lock the table (ENABLED) or (DISABLED) SAMPLE_SIZENUMBER
Sample size used in analyzing this table analyzes the sample size LAST_ANALYZEDDATE used for this table
Time of Date on which this table was most recently analyzed's last analysis table PARTITIONEDVARCHAR2 (3)
Is Indicates whether the table is partitioned (YES) or not (NO) a partition table IOT_TYPEVARCHAR2 (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. 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, IOT_TYPE is empty. TEMPORARYVARCHAR2 (1)
Is Indicates whether the table is temporary (Y) or not (N) a temporary table SECONDARYVARCHAR2 (1)
Whether Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) is a helper object NESTEDVARCHAR2 (3) created by the ODCIIndexCreate method
Is Indicates whether the table is a nested table (YES) or not (NO) a nested table (YES) or (NOBUFFER_POOLVARCHAR2 (7))
The default buffer of the Buffer pool for the table; NULL for partitioned tables:DEFAULTKEEPRECYCLENULL table object, if not cached to buffer cache, is displayed as null; partitioned table as NULLFLASH_CACHEVARCHAR2 (7)
Database Smart Flash Cache hint to be used for table blocks: (11g only) DEFAULTKEEPNONESolaris and Oracle Linux functionality only. Smart Flash Cache prompt for table blocks (Solaris and Oracle Linux only) CELL_FLASH_CACHEVARCHAR2 (7)
Cell flash cache hint to be used for table blocks:DEFAULTKEEPNONESee Also: Oracle Exadata Storage Server Software documentation for more information Cell flash cache prompt for table block ROW_MOVEMENTVARCHAR2 (8)
Whether GLOBAL_STATSVARCHAR2 is enabled for Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED) row migration (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) for partitioned tables, indicates whether the statistics (global statistics) of the entire table are accurate (YES) or whether they are not collected And must estimate (NO) whether the statistics of the table as a whole (partition table) are accurately represented by user statistics (YES) or (NO) USER_STATSVARCHAR2 (3) based on the statistics of the underlying partitions and subpartitions.
Indicates whether statistics were entered directly by the user (YES) or not (NO) indicates whether or not user statistics (YES) or (NO) DURATIONVARCHAR2 (15)
Indicates the duration of a temporary table:SYS$SESSION-Rows are preserved for the duration of the sessionSYS$TRANSACTION-Rows are deleted after COMMITNull-Permanent table if it is a temporary table, the duration of the table: SYS$SESSION: the rows are preserved for the duration of the session SYS$TRANSACTION: the rows are deleted after COMMIT partition table is displayed as NULL empty SKIP_CORRUPTVARCHAR2 (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. Whether to ignore blocks marked corrupt during table and index scans. If you want to use it, execute DBMS_REPAIR. SKIP_CORRUPT_BLOCKSMONITORINGVARCHAR2 (3)
Whether the Indicates whether the table has the MONITORING attribute set (YES) or not (NO) table has the MONITORING property set CLUSTER_OWNERVARCHAR2 (30)
Owner of Owner of the cluster, if any, to which the table belongs Cluster Table DEPENDENCIESVARCHAR2 (8)
Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED) Row-level dependency tracing is enabled (ENABLED) or (DISABLED) COMPRESSIONVARCHAR2 (8)
Indicates whether table compression is enabled (ENABLED) or not (DISABLED); whether the NULL for partitioned tables table compresses COMPRESS_FORVARCHAR2 (12)
Default compression for what kind of operations: (11g only) BASICOLTPQUERY LOWQUERY HIGHARCHIVE LOWARCHIVE HIGHNULL table compression type DROPPEDVARCHAR2 (3)
Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); whether the NULL for partitioned tables table has been DROP into the Recycle Bin READ_ONLYVARCHAR2 (3)
Indicates whether the table IS READ-ONLY (YES) or not (NO) (11g only) whether the table is read-only SEGMENT_CREATEDVARCHAR2 (3)
Indicates whether the table segment is created (YES) or not (NO) (11g only) whether the segment of the table creates RESULT_CACHEVARCHAR2 (7)
Result cache mode annotation for the table: (11g only) DEFAULT-Table has not been annotatedFORCEMANUAL whether the table comments are in the result cache
The method of obtaining the DDL of a table: 1) using DBMS_METADATA.GET_DDL to obtain the DDL statement of the table; 2) imp.indexfile and impdp.sqlfile 1) using DBMS_METADATA.GET_DDL Get the DDL statement reference for the table: How To Obtain Table DDL Without Using DBMS_METADATA.GET_DDL (document ID 1922301.1) first run set echo offset heading offset feedback offset verify offset pagesize 0set linesize 132define schema=&1 and enter the name of schema and then execute: define CR=chr (10) define TAB=chr (9) col x noprintcol y noprintSELECT TABLE_NAME Y, 0 X 'CREATE TABLE' | | RTRIM (TABLE_NAME) |'('FROM DBA_TABLESWHERE OWNER = UPPER (' & schema') UNIONSELECT 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, 'NOT NULL', NULL,' NOT NULL', NULL)) FROM DBA_TAB_COLUMNS TC, DBA_OBJECTS OWHERE O.OWNER = TC.OWNER AND O.OBJECT_NAME = TC.TABLE_NAME AND O.OBJECT_TYPE = 'TABLE' AND O.OWNER = UPPER (' & schema') UNIONSELECT 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 | | 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 | & CRFROM DBA_TABLESWHERE OWNER = UPPER ('& schema') ORDER BY 1 2 or
Set pagesize 0set long 90000set feedback offset echo offspool table_ddl.sqlselect 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; for example: set pagesize 0set long 90000set feedback offset echo offspool table_ddl.sqlselect 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.sqlSQL > select dbms_metadata.get_ddl (' TABLE','DEMO2','DEMO') from dual CREATE TABLE "DEMO". "DEMO2" ("OWNER" VARCHAR2 (30) "OBJECT_NAME" VARCHAR2 "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 255NOCOMPRESS 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 255COMPUTE 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 and impdp.sqlfile# example: 1.indexfile1) first export user's data [oracle@oracle11g ~] $exp demo/demo file=test.dmp owner=demo log=test.log;2) get these DDL statements [oracle@oracle11g ~] $imp demo/demo file=test.dmp fromuser=demo touser=demo indexfile=test.sql from dump file
2.sqlfile
Export user data [oracle@oracle11g ~] $expdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp schemas=demo; to get DDL statement [oracle@oracle11g ~] $impdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp sqlfile=demo.sql
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.