PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

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 Follow the link, below, to LOBs

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.

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.

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 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
WHERE segment_name = 'ALLO_TEST';

ALTER TABLE allo_test ALLOCATE EXTENT;

SELECT segment_name, extents, blocks
FROM user_segments
WHERE segment_name = 'ALLO_TEST';

ALTER TABLE allo_test ALLOCATE EXTENT
(SIZE 1M INSTANCE 1);

SELECT segment_name, extents, blocks
FROM user_segments
WHERE segment_name = 'ALLO_TEST';

Deallocate Unused Space		 ALTER TABLE <table_name> DEALLOCATE UNUSED [KEEP <intger>];
ALTER TABLE allo_test DEALLOCATE UNUSED;

SELECT segment_name, extents, blocks
FROM user_segments
WHERE 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;
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;
 
Related Topics
Analyze
Clusters
Table Constraints
Data Types & Subtypes
DBMS_REDEFINITION
DBMS_RESULT_CACHE
DBMS_ROWID
DBMS_SHARED_POOL
DBMS_STATS
External Tables
Global Temporary Tables
Indexes
IOT (Index Organized Tables)
LOBs
Nested Tables
Partitioned Tables
Pseudocolumns
Recycle Bin
Table Flashback
Transparent Data Encryption
Triggers
Truncate
XMLType Tables
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----