| General Information |
| Table Types |
|
| 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 |
|
|
| 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 |
CREATE TABLE ...
<CACHE | NOCACHE> |
CREATE TABLE yc (
testcol VARCHAR2(20));
NOCACHE;
CREATE TABLE nc (
testcol VARCHAR2(20))
CACHE;
SELECT table_name, cache
FROM user_tables; |
| CACHE READS |
Follow the link, below, to LOBs |
| Column Defaults |
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 <1 | 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 <1 | 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. |
| MOVE |
ALTER TABLE <table_name> MOVE TABLESPACE <new_tablespace_location>; |
SELECT segment_type, tablespace_name, blocks
FROM dba_extents
WHERE segment_name = 'MGMT_JOB_STATE_CHANGES';
ALTER TABLE sysman.mgmt_job_state_changes MOVE TABLESPACE sysaux;
SELECT segment_type, tablespace_name, blocks
FROM dba_extents
WHERE segment_name = 'MGMT_JOB_STATE_CHANGES'; |
| NEXT |
Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages extents.
NEXT <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))
PARALLEL 2;
SELECT table_name, degree
FROM user_tables;
set autotrace traceonly explain
SELECT * FROM parallel_test;
set autotrace off
ALTER TABLE parallel_test PARALLEL 4;
SELECT table_name, degree
FROM user_tables;
set autotrace traceonly explain
SELECT * FROM parallel_test;
CREATE TABLE parallel_test (
testcol VARCHAR2(20))
PARALLEL;
SELECT table_name, degree
FROM user_tables; |
| 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; |
| PCTINCREASE |
The default is zero and it should be left that way. Using alternate values back in Oracle 8.0 and before was always a bad idea.
PCTFREE <0 | integer> |
SELECT table_name, pct_increase
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 |
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. This setting cannot be changed after table creation.
<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 |
| 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; |
SEGMENT CREATION DEFERRED
aka: Segment Creation On Demand |
Deferred creation defers creating the segment until an insert statement is executed.
SEGMENT CREATION <IMMEDIATE | DEFERRED> |
CREATE TABLE uwclass.now (
col1 NUMBER,
col2 VARCHAR2(20))
SEGMENT CREATION IMMEDIATE;
CREATE TABLE uwclass.later (
col1 NUMBER,
col2 VARCHAR2(20))
SEGMENT CREATION DEFERRED;
SELECT table_name, tablespace_name, segment_created
FROM user_tables
WHERE table_name IN ('LATER', 'NOW');
SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LATER', 'NOW');
INSERT INTO now (col1, col2) VALUES (1, 'A');
INSERT INTO later (col1, col2) VALUES (1, 'B');
SELECT segment_name, tablespace_name
FROM user_segments
WHERE segment_name IN ('LATER', 'NOW'); |
| 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_ae
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 */ |
|
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; |
| TABLESPACE LOGGING |
TABLESPACE <tablespace_name> <LOGGING | NOLOGGING> |
CREATE TABLE force_logging (
rid NUMBER,
lname VARCHAR2(30))
TABLESPACE uwdata LOGGING; |
| VIRTUAL COLUMN |
<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 ...
<NOCOMPRESS | 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;
CREATE TABLE comptab
TABLESPACE compressed AS
SELECT * from all_objects;
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 |
CREATE TABLE ...
COMPRESS; |
conn uwclass/uwclass
CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects;
CREATE TABLE comptab
TABLESPACE uwdata COMPRESS AS
SELECT * FROM all_objects;
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 |
CREATE TABLE ...
COMPRESS FOR DIRECT_LOAD OPERATIONS; |
CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT /*+ APPEND */ *
FROM all_objects;
CREATE TABLE comptab
TABLESPACE uwdata COMPRESS FOR DIRECT_LOAD OPERATIONS AS
SELECT /*+ APPEND */ *
FROM all_objects;
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, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
ALTER TABLE allo_test ALLOCATE EXTENT;
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST';
ALTER TABLE allo_test ALLOCATE EXTENT
(SIZE 1M INSTANCE 1);
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST'; |
| Deallocate Unused Space |
ALTER TABLE <table_name> DEALLOCATE UNUSED [KEEP <integer>]; |
ALTER TABLE allo_test DEALLOCATE UNUSED;
SELECT segment_name, extents, blocks
FROM user_segments
HERE segment_name = 'ALLO_TEST'; |
| 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 for bitmap indexes |
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
or if the table is empty.
ALTER TABLE <table_name> MINIMIZE RECORDS_PER_BLOCK; |
| ALTER TABLE test MINIMIZE RECORDS_PER_BLOCK; |
| Release Control on the Number of Records Per Block |
ALTER TABLE <table_name> NOMINIMIZE RECORDS_PER_BLOCK; |
| ALTER TABLE test NOMINIMIZE RECORDS_PER_BLOCK; |
| |
| Drop Table |
| Drop Table Into Recycle Bin |
DROP TABLE <table_name>; |
| DROP TABLE zip_code; |
| Drop Table Not Into The 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 |
| Maximum number of columns |
Oracle documentation states that a table can contain as many as 1000 columns. No sane person should ever build one 1/10th that size and certainly never with more than 255 columns. The truth is that no table really can contain 1000 columns. Oracle does a bit
of sleight of hand behind the scenes to make it appear that a single table contains the large number of columns and you and your application will pay a horrendous price in performance.
Here is another reason to severely limit the number of columns in any table.
* Oracle stores columns in variable length format
* Each row is parsed in order to retrieve one or more columns
* Each subsequently parsed column introduces a cost of 20 CPU cycles whether or not it is used |
| 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; |
