Oracle命令

Oracle Tables

Version 11.1

General Table Related Data Dictionary Objects tab$ col$ also ALL & USER versions of these: dba_col_comments dba_external_tables dba_external_locations dba_partial_drop_tabs dba_tables dba_tab_cols dba_tab_columns dba_tab_col_statistics dba_tab_comments dba_tab_histograms dba_tab_modifications dba_tab_privs dba_tab_statistics dba_tab_stats_history dba_unused_col_tabs Table Related System Privileges alter any table flashback any table backup any table insert any table create any table lock any table comment any table select any table create table under any table delete any table update any table drop any table Types Of Tables Table Type Description Heap Default Oracle table type Global Temporary Two different types depending Clusters One or more tables in a single database block External External files readable as tables Index Organized Merger between a table and an index Partitions Partition/Subpartitioned by hash, range, or list XML XML Tables How Rows Are Stored The format of a row is: row header, column length - value; column_length - value; column_length - value; .... The length of a field is stored as an attribute in the row. If the column name is "LAST_NAME" and the column is defined as CHAR(20) it is be stored as :20:Morgan--------------: If the column name is "LAST_NAME" and the column is defined as VARCHAR2(20) it is stored as  :6:Morgan: Oracle starts adding rows from the end of the block towards the block header. In this way, the block header can grow if required. To see the actual block and row as stored by Oracle use the DBMS_ROWID built-in package.   Heap Table Create Table With a Single Columns CREATE TABLE <table_name> ( <column_name>  <column_data_type>); CREATE TABLE one_col ( last_name VARCHAR2(25)); desc one_col desc user_tables SELECT table_name, tablespace_name FROM user_tables; desc user_tab_columns  -- stop using this view ... here's why desc user_tab_cols SELECT column_id, column_name FROM user_tab_cols WHERE table_name = 'ONE_COL' ORDER BY column_id; Create Table With Multiple Columns CREATE TABLE <table_name> ( <column_name>  <column_data_type>, <column_name>  <column_data_type>); CREATE TABLE multi_col ( pid   NUMBER(5), fname VARCHAR2(20), lname VARCHAR2(25)); desc multi_col SELECT column_id, column_name FROM user_tab_cols WHERE table_name = 'MULTI_COL' ORDER BY column_id; Create Table Using Select Statement With Data CREATE TABLE <table_name> TABLESPACE <tablespace_name> AS <select statement>; CREATE TABLE ctas AS SELECT table_name, tablespace_name FROM all_tables; SELECT * FROM ctas; Create Table Using Select Statement With No Data Using Tablespace Defaults CREATE TABLE <table_name> TABLESPACE <tablespace_name> AS <select statement> WHERE <Boolean False>; CREATE TABLE ctas_nodata AS SELECT table_name, tablespace_name FROM all_tables WHERE 1=2; SELECT * FROM ctas_nodata;   Create Table Options BUFFER POOL Defines a default buffer pool (cache) for a schema object. <KEEP | RECYCLE | DEFAULT> CREATE TABLE buffer_test ( testcol  VARCHAR2(20)) STORAGE (buffer_pool DEFAULT); SELECT table_name, buffer_pool FROM user_tables; ALTER TABLE buffer_test STORAGE (buffer_pool RECYCLE); SELECT table_name, buffer_pool FROM user_tables; ALTER TABLE buffer_test STORAGE (buffer_pool KEEP); SELECT table_name, buffer_pool FROM user_tables; Note: Additional information can be found here. CACHE Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE, then: In a CREATE TABLE statement, NOCACHE is the default In an ALTER TABLE statement, the existing value is not changed. CACHE For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior. NOCACHE For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage. As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value either is not brought into the buffer cache or is brought into the buffer cache and placed at the least recently used end of the LRU list. The latter is the default behavior. <CACHE | NOCACHE> CREATE TABLE heap_cache ( testcol  VARCHAR2(20)) CACHE; SELECT table_name, cache FROM user_tables WHERE table_name = 'HEAP_CACHE'; ALTER TABLE heap_cache NOCACHE; SELECT table_name, cache FROM user_tables WHERE table_name = 'HEAP_CACHE'; CACHE READS CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations but not during write operations. < CACHE READS | NOCACHE> CREATE TABLE cache_test ( testlob BLOB) LOB (testlob) STORE AS (CACHE READS); SELECT table_name, cache FROM user_lobs; ALTER TABLE cache_test MODIFY LOB (testlob) (NOCACHE); DEFAULT The value inserted into the column if the insert or update would leave the column value NULL. <DEFAULT <value> | NULL> CREATE TABLE default_test ( active     VARCHAR2(1) DEFAULT 'Y', created_by VARCHAR2(30) DEFAULT USER, created_on TIMESTAMP DEFAULT SYSTIMESTAMP); set long 100000 set linesize 121 col data_default format a50 SELECT column_name, data_default FROM user_tab_cols WHERE table_name = 'DEFAULT_TEST'; ALTER TABLE default_test MODIFY (active DEFAULT 'N'); SELECT column_name, data_default FROM user_tab_cols WHERE table_name = 'DEFAULT_TEST'; ALTER TABLE default_test MODIFY (active DEFAULT NULL); SELECT column_name, data_default FROM user_tab_cols WHERE table_name = 'DEFAULT_TEST'; INSERT INTO default_test (active) VALUES ('X'); SELECT * FROM default_test; INSERT INTO default_test (active, created_by) VALUES ('Y', 'Morgan'); SELECT * FROM default_test; UPDATE default_test SET created_by = DEFAULT; SELECT * FROM default_test; Encryption Specification Valid algorithms are 3DES168, AES128, AES192, and AES256 USING '<encryption_algorithm>' IDENTIFIED BY <password> [NO] [SALT] See Transparent Data Encryption Flashback Archive FLASHBACK [ARCHIVE <flashback_archive>] or NO FLASHBACK ARCHIVE See Flashback Archive Page FREELISTS The number of lists maintained on a table that can be used to identify a block available for insert. Set this to 1 on all tables except those receiving very large numbers of simultaneous inserts. When a process requests a free list, it uses a 'hashing' function to select which free list based on the process id. Using a prime number with such mechanisms usually reduces the number of collisions that occur if the input is randomly distributed. Therefore, if you need more than one free list make the number of lists a prime number (for example 1, 2, 3, 5, 7, 11, 13, 17, 19, 23, .... for optimal performance). Oracle ignores a setting of FREELISTS if the tablespace in which the object resides is in automatic segment-space management mode. FREELISTS <integer> SELECT table_name, freelists FROM user_tables; FREELIST GROUPS The number of groups of free lists for the database object you are creating. The database uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group. This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO. FREELIST GROUPS <integer> SELECT table_name, freelist_groups FROM user_tables; INITIAL In a tablespace that is specified as EXTENT MANAGEMENT LOCAL. The database uses the value of INITIAL in conjunction with the extent size for the tablespace to determine the initial amount of space to reserve for the object. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then the database must allocate one 5M extent, because that is the uniform size of extents for the tablespace. If the extent size of the tablespace is smaller than the value of INITIAL, then the initial amount of space allocated will in fact be more than one extent. INITIAL <integer> <K | M | G> SELECT tablespace_name, initial_extent FROM user_tablespaces SELECT table_name, tablespace_name, initial_extent FROM user_tables; INITRANS Specifies the number of DML transaction entries for which space is initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated segment. Oracle uses control information stored in the data block to indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE. Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. INITRANS <integer> SELECT table_name, ini_trans FROM user_tables; LOGGING Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING). The logging attribute of the table is independent of that of its indexes. This attribute also specifies whether subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged (LOGGING) or not logged (NOLOGGING). CREATE TABLE logging_test ( testcol  VARCHAR2(20)) LOGGING; SELECT table_name, logging FROM user_tables; ALTER TABLE logging_test NOLOGGING; SELECT table_name, logging FROM user_tables; MAXTRANS Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header. The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS. MAXTRANS <integer> SELECT table_name, max_trans FROM user_tables; MINEXTENTS The total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available. In a tablespace that is specified as EXTENT MANAGEMENT LOCAL, MINEXTENTS is used only to compute the initial amount of space that is allocated. The initial amount of space that is allocated and is equal to INITIAL * MINEXTENTS. Thereafter it is set to 1 for these tablespaces. (as seen in the DBA_SEGMENTS view). MINEXTENTS <integer> SELECT table_name, min_extents FROM user_tables; MONITOR Deprecated in 10g. DBMS_STATS now does this by default. NEXT Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages extents. INITIAL <integer> <K | M | G> SELECT tablespace_name, next_extent FROM user_tablespaces SELECT table_name, tablespace_name, next_extent FROM user_tables; Create Table With NOT NULL Constraints CREATE TABLE <table_name> <column_name>  <column_data_type> NOT NULL, <column_name>  <column_data_type>); CREATE TABLE nn_test ( pid   NUMBER(5) NOT NULL, fname VARCHAR2(20), lname VARCHAR2(25)); desc nn_test SELECT column_id, nullable FROM user_tab_cols WHERE table_name = 'NN_TEST' ORDER BY column_id; ALTER TABLE nn_test MODIFY (pid NULL); desc nn_test SELECT column_id, nullable FROM user_tab_cols WHERE table_name = 'NN_TEST' ORDER BY column_id; ALTER TABLE nn_test MODIFY (pid NOT NULL); SELECT column_id, nullable FROM user_tab_cols WHERE table_name = 'NN_TEST' ORDER BY column_id; PARALLEL Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter. Specify NOPARALLEL, the default, for serial execution. <PARALLEL | NOPARALLEL> For this to be optimally effective the table should be distributed among multiple datafiles. CREATE TABLE parallel_test ( testcol VARCHAR2(20)); SELECT table_name, degree FROM user_tables; set autotrace traceonly explain SELECT * FROM parallel_test; set autotrace off ALTER TABLE parallel_test PARALLEL 2; SELECT table_name, degree FROM user_tables; set autotrace traceonly explain SELECT * FROM parallel_test; PCTFREE Determines when a used block is removed from the list of available blocks. When a block is removed from the list ... no more data is written to it so that when records are updated there is room for the data in the block ... thus no chained rows. Tables on which there are no updates should have PCTFREE set to 0. The default value of 10 leaves 90% of each block empty. PCTFREE <integer> SELECT table_name, pct_free FROM user_tables; PCTUSED Determines when a used block is re-added to the list of available blocks. When deletes take place and the room available in a block falls below this value ... the block is made available for new inserts to take place. Tables on which there are no updates should have PCTUSED set to 99. The default value is 40% which means that blocks are available for insertion when they are less than 40% full. PCTUSED <integer> SELECT table_name, pct_used FROM user_tables; READ ONLY Clause New in 11g READ ONLY CREATE TABLE readonly ( testcol VARCHAR2(20)) READ ONLY; -- why did this fail? CREATE TABLE readonly READ ONLY AS SELECT * FROM servers; -- why did this fail too? CREATE TABLE readonly ( testcol VARCHAR2(20)); INSERT INTO readonly (testcol) VALUES ('Morgan'); ALTER TABLE readonly READ ONLY; SELECT table_name, read_only FROM user_tables ORDER BY 2; INSERT INTO readonly (testcol) VALUES ('Morgan'); tab$ information from dcore.sql trigflag number, /* first two bytes for trigger flags, the rest for */ /* general use, check tflags_kqldtvc in kqld.h for detail */ /* 0x00000001 deferred RPC Queue */ /* 0x00000002 snapshot log */ /* 0x00000004 updatable snapshot log */ /* 0x00000008 = context trigger */ /* 0x00000010 = synchronous change table */ /* 0x00000020 = Streams trigger */ /* 0x00000040 = Content Size Trigger */ /* 0x00000080 = audit vault trigger */ /* 0x00010000 = server-held key encrypted columns exist */ /* 0x00020000 = user-held key encrypted columns exist */ /* 0x00200000 = table is read only */ /* 0x00400000 = lobs use shared segment */ /* 0x00800000 = queue table */ /* 0x10000000 = streams unsupported table */ /* enabled at some point in past */ /* 0x80000000 = Versioning enabled on this table */ ROW CHAINING AND MIGRATION Source: http://www.tlingua.com Row chaining occurs when a row can no longer fit into its original block. If the entire row can fit in a new block, the row is moved completely, leaving only a forwarding pointer - this is known as row migration. If the row has grown so large that it may not fit in a single block then the row is split into two or more blocks - row chaining. When Oracle is forced to split a row into pieces, it often splits individual columns into one or more pieces. See Analyze: See "List Chained Rows" ROWDEPENDENCIES This clause lets you specify whether table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created. <ROWDEPENDENCIES | NOROWDEPENDENCIES> CREATE TABLE rowdep_test ( testcol  VARCHAR2(20)) ROWDEPENDENCIES; SELECT table_name, dependencies FROM user_tables; -- Not alterable after table creation -- See ORA_ROWSCN under pseudocolumns for more information about this ROW MOVEMENT The row_movement_clause specifies whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data. The default is to disable row movement. <ENABLE | DISABLE> ROW MOVEMENT CREATE TABLE rowmove_test ( testcol  VARCHAR2(20)) ENABLE ROW MOVEMENT; SELECT table_name, row_movement FROM user_tables; ALTER TABLE rowmove_test DISABLE ROW MOVEMENT; SELECT table_name, row_movement FROM user_tables; Supplemental ID Key Logging Clause DATA (<ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY>) COLUMNS CREATE TABLE sup_log1 ( pid   NUMBER(5), fname VARCHAR2(20), lname VARCHAR2(25), SUPPLEMENTAL LOG DATA (ALL) COLUMNS); conn / as sysdba SELECT object_id FROM dba_objects WHERE owner = 'UWCLASS' AND object_name = 'SUP_LOG1'; SELECT type# FROM cdef$ WHERE obj# = 74097; cdef$ information from dcore.sql type# number not null, /* constraint type: */ /* 1 = table check, 2 = primary key, 3 = unique, */ /* 4 = referential, 5 = view with CHECK OPTION, */ /* 6 = view READ ONLY check */ /* 7 - table check constraint associated with column NOT NULL */ /* 8 - hash expressions for hash clusters */ /* 9 - Scoped REF column constraint */ /* 10 - REF column WITH ROWID constraint */ /* 11 - REF/ADT column with NOT NULL const */ /* 12 - Log Groups for supplemental logging */ /* 14 - Primary key supplemental logging */ /* 15 - Unique key supplemental logging */ /* 16 - Foreign key supplemental logging */ /* 17 - All column supplemental logging */ Supplemental PL/SQL Clause DATA FOR PROCEDURAL REPLICATION CREATE TABLE sup_log2 ( pid   NUMBER(5), fname VARCHAR2(20), lname VARCHAR2(25), SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION); -- Note: While valid this syntax may not be functional TABLE LOCK Prevent and Re-enable DDL On A Table ALTER TABLE <table_name> DISABLE TABLE LOCK; CREATE TABLE tl_test ( col1 VARCHAR2(20)); desc tl_test ALTER TABLE tl_test DISABLE TABLE LOCK; ALTER TABLE tl_test ADD (col2 VARCHAR2(20)); TRUNCATE TABLE tl_test; DROP TABLE tl_test PURGE; ALTER TABLE tl_test ENABLE TABLE LOCK; TRUNCATE TABLE tl_test; DROP TABLE tl_test PURGE; TABLESPACE the name of the tablespace where the table will be built. The table may exist in one or more the the datafiles mapped to the tablespace. TABLESPACE <tablespace_name> or TABLESPACE <tablespace_group_name> SELECT tablespace_name, max_bytes, max_blocks FROM user_ts_quotas; CREATE TABLE tbsp_test ( testcol VARCHAR2(20)) TABLESPACE users; SELECT table_name, tablespace_name FROM user_tables ORDER BY 1; ALTER TABLE tbsp_test MOVE TABLESPACE uwdata; SELECT table_name, tablespace_name FROM user_tables ORDER BY 1; CREATE TABLE ctas2 TABLESPACE uwdata AS SELECT * FROM all_tables; VIRTUAL COLUMN New in 11g <column_name> [<data_type>] [GENERATED ALWAYS] AS (column_expression)  VIRTUAL (inline_constraint) Note: "GENERATED ALWAYS" and "VIRTUAL" are optional keywords CREATE TABLE vcol ( salary     NUMBER(8), bonus      NUMBER(3), total_comp NUMBER(10) GENERATED ALWAYS AS (salary+bonus) VIRTUAL); desc vcol col data_type format a30 col data_default format a30 set long 100000 desc user_tab_columns  -- stop using this view ... here's why desc user_tab_cols SELECT column_name, data_type, virtual_column, data_default FROM user_tab_cols WHERE table_name = 'VCOL'; INSERT into vcol (salary, bonus, total_comp) VALUES (100, 10, 110); INSERT into vcol (salary, bonus) VALUES (100, 10); SELECT * FROM vcol; DROP TABLE vcol PURGE; CREATE TABLE vcol ( salary     NUMBER(8), bonus      NUMBER(3), total_comp NUMBER(10) GENERATED ALWAYS AS (salary+bonus)); DROP TABLE vcol PURGE; CREATE TABLE vcol ( salary     NUMBER(8), bonus      NUMBER(3), total_comp NUMBER(10) AS (salary+bonus)); ALTER TABLE vcol ADD CONSTRAINT cc_vcol_total_comp CHECK (total_comp < 50001); INSERT into vcol (salary, bonus) VALUES (100, 10); INSERT into vcol (salary, bonus) VALUES (200, 12); COMMIT; INSERT into vcol (salary, bonus) VALUES (50000, 1); SELECT * FROM vcol; EXPLAIN PLAN FOR SELECT * FROM vcol; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT /*+ RESULT_CACHE */ * FROM vcol; SELECT * FROM TABLE(dbms_xplan.display); ALTER TABLE vcol ADD CONSTRAINT pk_vcol PRIMARY KEY (salary) USING INDEX; CREATE INDEX ix_virtual_column ON vcol(total_comp); SELECT index_name, index_type FROM user_indexes WHERE table_name = 'VCOL'; col column_name format a30 SELECT column_name, column_position, column_length FROM user_ind_columns WHERE table_name = 'VCOL'; BEGIN   FOR i IN 1 .. 20000 LOOP     BEGIN       INSERT INTO vcol       (salary, bonus)       VALUES       (i, TRUNC(MOD(i, 5)));     EXCEPTION       WHEN OTHERS THEN         NULL;     END;   END LOOP;   COMMIT; END; / exec dbms_stats.gather_index_stats(USER, 'PK_VCOL'); exec dbms_stats.gather_index_stats(USER, 'IX_VIRTUAL_COLUMN'); desc user_ind_statistics SELECT index_name, object_type, blevel, leaf_blocks, distinct_keys, clustering_factor FROM user_ind_statistics WHERE table_name = 'VCOL';   Compressed Table COMPRESSED The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default. When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table. For an entire table, in the physical_properties clause of relational_table or object_table For a range partition, in the table_partition_description of the range_partitioning clause For a list partition, in the table_partition_description of the list_partitioning clause For the storage table of a nested table, in the nested_table_col_properties clause Table compression saves disk space and reduces memory use in the buffer cache, and is completely transparent to applications. Compression ratios as high as 3.5 : 1 can be achieved. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for DML. Compressed Tablespace CREATE TABLESPACE ... DEFAULT COMPRESS; conn / as sysdba CREATE TABLESPACE compressed DATAFILE 'compressed.dbf' SIZE 5M DEFAULT COMPRESS; SELECT tablespace_name, def_tab_compression FROM dba_tablespaces; ALTER USER uwclass QUOTA unlimited ON compressed; conn uwclass/uwclass CREATE TABLE regtab TABLESPACE uwdata AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; CREATE TABLE comptab TABLESPACE compressed AS SELECT * from all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT table_name, tablespace_name, compression FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); exec dbms_stats.gather_table_stats(USER, 'REGTAB'); exec dbms_stats.gather_table_stats(USER, 'COMPTAB'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); conn / as sysdba DROP TABLESPACE compressed INCLUDING CONTENTS AND DATAFILES; Compressed Table New in 11g CREATE TABLE ... COMPRESS; conn uwclass/uwclass CREATE TABLE regtab TABLESPACE uwdata AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; CREATE TABLE comptab TABLESPACE uwdata COMPRESS AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT table_name, tablespace_name, compression FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); exec dbms_stats.gather_table_stats(USER, 'REGTAB'); exec dbms_stats.gather_table_stats(USER, 'COMPTAB'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); Another example CREATE TABLE t1 ( testcol VARCHAR2(50)) TABLESPACE uwdata; CREATE TABLE t2 ( testcol VARCHAR2(50)) TABLESPACE uwdata COMPRESS; DECLARE  x t1.testcol%TYPE; BEGIN   FOR i IN 1 .. 100000 LOOP     SELECT dbms_crypto.randombytes(25)     INTO x     FROM dual;     INSERT INTO t1 VALUES (x);     INSERT INTO t2 VALUES (x);   END LOOP;   COMMIT; END; / -- some sample rows: 3605CAA721159CAC4E462B841419CCB7390F1AE3484FF14963 05B7AE0B6BB076EEAF3E8E7DBA1BE9D5C8F97737AA1FDF21A5 40756BCEBF00CCB80ACA5F4F6BF3AFE6BC19D19EA74F10212B 234812A15930421A208BCF19C943762B5FA11D0C0C7E811F5E 4177AFC94C248D6B6765B8CE45FE3E49E2E5456BA6BA48C147 exec dbms_stats.gather_table_stats(USER, 'T1'); exec dbms_stats.gather_table_stats(USER, 'T2'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('T1', 'T2'); Compressed Direct Load New in 11g CREATE TABLE ... COMPRESS FOR DIRECT_LOAD OPERATIONS; CREATE TABLE regtab TABLESPACE uwdata AS SELECT /*+ APPEND */ * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; CREATE TABLE comptab TABLESPACE uwdata COMPRESS FOR DIRECT_LOAD OPERATIONS AS SELECT /*+ APPEND */ * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT table_name, tablespace_name, compression FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB'); exec dbms_stats.gather_table_stats(USER, 'REGTAB'); exec dbms_stats.gather_table_stats(USER, 'COMPTAB'); SELECT table_name, blocks FROM user_tables WHERE table_name IN ('REGTAB', 'COMPTAB');   Commenting Comment a table COMMENT ON TABLE <table_name> IS '<comment>'; COMMENT ON TABLE zip_code IS 'US Postal Service Zip Codes'; SELECT table_name, comments FROM user_tab_comments; Comment a column COMMENT ON COLUMN <table_name.column_name> IS '<comment>'; COMMENT ON COLUMN zip_code.zip_code IS '5 Digit Zip Code'; SELECT table_name, column_name, comments FROM user_col_comments;   Alter Table Column Clauses Add a new column ALTER TABLE <table_name> MODIFY (<field_name data_type>); CREATE TABLE test ( first_col  VARCHAR2(20)); desc test ALTER TABLE test ADD (second_col NUMBER(20)); desc test Add More Than One New Column ALTER TABLE <table_name> MODIFY (<field_name data_type>, <field_name data type>); ALTER TABLE test ADD (third_col DATE, fourth_col VARCHAR2(3)); desc test Rename A Column ALTER TABLE <table_name> RENAME COLUMN <current_name> TO <new_name>; ALTER TABLE test RENAME COLUMN third_col TO date_col; desc test Drop A Column On A Small To Medium Sized Table ALTER TABLE <table_name> DROP COLUMN <column_name>; ALTER TABLE test DROP COLUMN fourth_col; desc test Drop A Column On A Very Large Table ALTER TABLE <table_name> DROP COLUMN <column_name> CHECKPOINT <integer>; ALTER TABLE test DROP COLUMN fourth_col CHECKPOINT 1000; Set A Column Unused ALTER TABLE <table_name> SET UNUSED COLUMN <column_name>; ALTER TABLE test SET UNUSED COLUMN second_col; desc test SELECT * FROM user_unused_col_tabs; Drop Unused Columns ALTER TABLE <table_name> DROP UNUSED COLUMNS; ALTER TABLE test DROP UNUSED COLUMNS; desc test Drop Unused Columns On A Very Large Table ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <integer>; ALTER TABLE test DROP UNUSED COLUMNS CHECKPOINT 250; desc test Alter Table Change Data Type ALTER TABLE <table_name> MODIFY (<column_name new_data_type); CREATE TABLE dt_ test ( test_col   VARCHAR2(20)); desc dt_test ALTER TABLE dt_ test MODIFY (test_col NUMBER(6)); desc dt_test NOTE: column must be empty to change data type Alter Table Change Data Type Multiple Fields ALTER TABLE <table_name> MODIFY (<column_name> <data_type>,         <column_name> <data_type>); CREATE TABLE mcdt_test ( col_one NUMBER(10), col_two VARCHAR2(10), dat_col DATE); DESC mcdt_test ALTER TABLE mcdt_test MODIFY (col_one NUMBER(12), col_two VARCHAR2(20)); desc mcdt_test   Alter Table Storage Clauses Force Extent Allocation ALTER TABLE <table_name> ALLOCATE EXTENT; ALLOCATE EXTENT [( { SIZE size_clause | DATAFILE 'filename' | INSTANCE integer }  [ SIZE size_clause | DATAFILE 'filename' | INSTANCE integer ]...)] CREATE TABLE allo_test ( testcol  VARCHAR2(20)); col segment_name format a30 SELECT segment_name, extents FROM user_segments; ALTER TABLE allo_test ALLOCATE EXTENT; SELECT segment_name, extents FROM user_segments; ALTER TABLE allo_test ALLOCATE EXTENT (SIZE 1M INSTANCE 1); SELECT segment_name, extents FROM user_segments; Deallocate Unused Space ALTER TABLE <table_name> DEALLOCATE UNUSED; SELECT segment_name, extents FROM user_segments; ALTER TABLE allo_test DEALLOCATE UNUSED; SELECT segment_name, extents FROM user_segments; Shrink Space Valid only for segments in tablespaces with automatic segment management. Row movement must be enabled. COMPACT defragments the segment space and compacts the table rows for subsequent release. COMPACT does not readjust the high water mark and does not release the space immediately. CASCADE performs the same operations on all dependent objects. ALTER TABLE <table_name> SHRINK SPACE [COMPACT] [CASCADE]; CREATE TABLE shrink_test ( rid      NUMBER(5), testcol  VARCHAR2(20)) ENABLE ROW MOVEMENT; SELECT bid, count(*) FROM (   SELECT dbms_rowid.rowid_block_number(rowid) BID   FROM shrink_test) GROUP BY bid; BEGIN   FOR i IN 1..40000   LOOP     INSERT INTO shrink_test (rid, testcol)     VALUES (i, 'ABCDEFGHIJKLMNOPQRST');   END LOOP;   COMMIT; END; / SELECT COUNT(*) FROM shrink_test; SELECT bid, count(*) FROM (   SELECT dbms_rowid.rowid_block_number(rowid) BID   FROM shrink_test) GROUP BY bid; DELETE FROM shrink_test WHERE mod(rid, 2) = 0; COMMIT; SELECT COUNT(*) FROM shrink_test; SELECT bid, count(*) FROM (   SELECT dbms_rowid.rowid_block_number(rowid) BID   FROM shrink_test) GROUP BY bid; ALTER TABLE shrink_test SHRINK SPACE COMPACT CASCADE; SELECT bid, count(*) FROM (   SELECT dbms_rowid.rowid_block_number(rowid) BID   FROM shrink_test) GROUP BY bid; Control the Number of Records per Block This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible. Can not be specified MINIMIZE or NOMINIMIZE if a bitmap index has already been defined on table. The bitmap index must be dropped. ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK; ALTER TABLE test MINIMIZE NUMBER_OF_RECORDS_PER_BLOCK; Release Control on the Number of Records Per Block ALTER TABLE <table_name> NOMINIMIZE RECORDS_PER_BLOCK; ALTER TABLE test NOMINIMIZE NUMBER_OF_RECORDS_PER_BLOCK; Move Table Containing An LOB Segment To A Different Tablespace ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name> LOB (<lob_column_name>) STORE AS <lob_segment_name> (TABLESPACE <tablespace_name>); CREATE TABLE lobtab ( recid  NUMBER(5), lobcol BLOB) LOB (lobcol) STORE AS (TABLESPACE uwdata STORAGE (INITIAL 1M) CHUNK 4000 NOCACHE NOLOGGING) TABLESPACE uwdata; desc lobtab col segment_name format a30 SELECT segment_name, segment_type, tablespace_name FROM user_segments; SELECT tablespace_name, bytes FROM user_ts_quotas; conn / as sysdba ALTER USER uwclass QUOTA 10M ON users; conn uwclass/uwclass -- does not refresh in 10.2 / this is an unfixed bug SELECT tablespace_name, bytes FROM user_ts_quotas; ALTER TABLE lobtab MOVE TABLESPACE uwdata LOB (lobcol) STORE AS lobseg (TABLESPACE example); SELECT segment_name, tablespace_name FROM user_segments WHERE segment_name IN ('LOBTAB', 'LOBSEG'); SELECT segment_name, segment_type, tablespace_name FROM user_segments;   Drop Table Drop Table Into Recycle Bin DROP TABLE <table_name>; DROP TABLE zip_code; 10g Drop Table Not Into Recycle Bin DROP TABLE <table_name> PURGE; DROP TABLE zip_code PURGE; Drop Table Cascade Constraints DROP TABLE <table_name> CASCADE CONSTRAINTS; DROP TABLE work_note_header CASCADE CONSTRAINTS;   Miscellaneous Slow table reads The init parameter db_file_multiblock_read_count is paramount Rename a table Thanks Chris Barr for the second syntax. RENAME <current_table_name> TO <new_name>; CREATE TABLE old_name ( test  VARCHAR2(20)); SELECT table_name FROM user_tables ORDER BY 1; RENAME old_name TO new_name; SELECT table_name FROM user_tables ORDER BY 1;   Table Related Queries How much space is a table taking in the tablespace? SELECT SUBSTR(s.segment_name,1,20) TABLE_NAME, SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME, ROUND(DECODE(s.extents, 1, s.initial_extent, (s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB, ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB FROM dba_segments s, dba_tables t WHERE s.owner = t.owner AND s.segment_name = t.table_name ORDER BY s.segment_name; Space again SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB FROM dba_tables ORDER BY owner, table_name;