Oracle Normal Relational Heap Tables
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose Oracle defines multiple table types ... each one optimized for a specific use case. Use the guide, below to find information about table types with native support inside the database in addition to the default "heap" table.

Simply stated relational tables are the primary way permanently store data in a relational database. The complete syntax for create table is so complex that there is no rational way to explore it on a single Library page so this page is showing only a small fraction of the available technology for persisting data in table structures within the database.

Information about use of the In-Memory clause is Clause is in the Library on a separate page too: See link at page bottom.
Table Types Links
Cluster One or more tables in a single database block
External External files readable as tables
Global Temporary Two different types depending
Heap Default Oracle table type
Index Organized Table and primary key index stored as a single queryable B*Tree index
JSON Merger between a table and an index
Object Also known as Nested Tables. These are essentially table like objects nested within a column-row of an otherwise relational table
Partitioned Partition/Subpartitioned by hash, list, or range
Sharded Not added to the Library at this time
XML Optimized for efficient storage and retrieval of XML documents
Heap Table and Column Clauses Guide
Alter Table ALTER TABLE commands are used to modify table properties after the table has been created.
Attribute Clustering Specify how to cluster data in close physical proximity based on column contents.
Cache Specify how the database should store blocks in the buffer cache.
Cluster Identifies the cluster mapped from a tablespace in which the table's segment will be stored.
Comment Tables and Columns Commands write comments into the data dictionary.
Compression Tablespace, Basic, Advanced, Hybrid Columnar, and LOB Compression reduce storage space and can improve I/O performance.
Constraints Follow the links at page bottom for relational and object constraints.
Deferred Segment Creation The initial table segment is created upon INSERT of the first row.
Drop Table DROP TABLE commands are used to drop into the recyclebin or purge an existing table.
Encryption Available encryption algorithms, for single columns or all columns, include 3DES168, AES128, AES192, and AES256.
Evaluation Edition Use this clause to specify the edition that is searched during name resolution of the editioned PL/SQL function—the evaluation edition.
Flashback Archive Specify to enable tracking for the table. You can specify flashback_archive to designate a particular flashback data archive for this table.
The flashback data archive you specify much already exist.
Identity Columns Identity columns are columns, usually surrogate keys, that are autonumbered by an automatically generated sequence object.
ILM Policy Use this clause to define an Integrated Lifecycle Management policy for Automatic Data Optimization.
Indexing The indexing property determines whether table partitions and subpartitions are included in partial indexes on the table.
Invisible/Visible Column Specifies whether a column will be visible during a describe operation.
In-Memory Specifies a tables characteristics if used with In-Memory options.
Logging Specify if you want logging of all tables, indexes, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels. It is always prefereable to do FORCE LOGGING at the database level.
Null / Not Null Clause Allows columns to be specfied as nullable or not nullable.
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.
Partitioning Partitioning is the method of choice for improving performance of I/O and maintenance activities by breaking up large tables into multiple independently accessed segments.
Period Definition Use the PERIOD_DEFINITION clause to create a valid time dimension for table. This clause implements Temporal Validity support for table.
Physical Attributes Clauses Specifies those aspects of a table definition that affect the table's extents and blocks. Oracle considers compression, in-memory,ilm part of the Physical Attributes but for purposes of clarity in the Library we treat them as separate entities.
Read Only Tables and partitions can be made read only to minimize data corruption and reduce backup size.
Result Cache Defines whether the table uses the DEFAULT or FORCE mode with respect to participation in the Result Cache independent of the Result Cache Hint.
Row Archival Enables In-Database Archiving, which allows designation of table rows as active or archived. Queries can then only b performed on the active rows within the table.
Row Dependencies Enable or disable row-level dependency tracking: Primary for parallel propagation in replication environments.
Row Movement Specifies whether the database can move a table row.
Sharing The SHARING clause can be used with a table built in an Application Root to share the METADATA or DATA, by pointer, with the Application Root's PDB children.
Storage Specify a table physical storage characteristics.
Supplemental Logging The clause lets you instruct the database to put additional data into the log stream to support log-based replication tools.
Table Lock Prevent and re-enable DDL on a table.
Tablespace Identifies the tablespace logical mapping of one or more datafiles in which the table's segment will be stored.
Tablespace Set Only valid for sharded tables.
Tiering Specify this clause to migrate data to tablespace when the condition specified in the AFTER clause is met or when the PL/SQL function specified in the ON clause returns TRUE. If you specify READ ONLY, then tablespace is made read only after the data is migrated.
Unusable Editions This clause lets you specify that the virtual column expression is unusable for evaluating queries in one or more editions. The remaining editions form a range of editions in which it is safe for the optimizer to use the virtual column expression to evaluate queries.
Virtual Column A non-stored column resulting from an explicit calculation possibly using a deterministic function.
Dependencies
ALL_ALL_TABLES CDB_TAB_COL_STATISTICS DBA_TAB_PRIVS
ALL_COL_COMMENTS CDB_TAB_COMMENTS DBA_TAB_STATISTICS
ALL_PARTIAL_DROP_TABS CDB_TAB_HISTOGRAMS DBA_TAB_STATS_HISTORY
ALL_TABLES CDB_TAB_MODIFICATIONS DBA_UNUSED_COL_TABS
ALL_TAB_COLS CDB_TAB_PRIVS TAB$
ALL_TAB_COLUMNS CDB_TAB_STATISTICS USER_ALL_TABLES
ALL_TAB_COL_STATISTICS CDB_TAB_STATS_HISTORY USER_COL_COMMENTS
ALL_TAB_COMMENTS CDB_UNUSED_COL_TABS USER_PARTIAL_DROP_TABS
ALL_TAB_HISTOGRAMS COL$ USER_TABLES
ALL_TAB_MODIFICATIONS DBA_ALL_TABLES USER_TAB_COLS
ALL_TAB_PRIVS DBA_COL_COMMENTS USER_TAB_COLUMNS
ALL_TAB_STATISTICS DBA_PARTIAL_DROP_TABS USER_TAB_COL_STATISTICS
ALL_TAB_STATS_HISTORY DBA_TABLES USER_TAB_COMMENTS
ALL_UNUSED_COL_TABS DBA_TAB_COLS USER_TAB_HISTOGRAMS
CDB_ALL_TABLES DBA_TAB_COLUMNS USER_TAB_MODIFICATIONS
CDB_COL_COMMENTS DBA_TAB_COL_STATISTICS USER_TAB_PRIVS
CDB_PARTIAL_DROP_TABS DBA_TAB_COMMENTS USER_TAB_STATISTICS
CDB_TABLES DBA_TAB_HISTOGRAMS USER_TAB_STATS_HISTORY
CDB_TAB_COLS DBA_TAB_MODIFICATIONS USER_UNUSED_COL_TABS
CDB_TAB_COLUMNS    
Row Chaining and Migration 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 too. The more columns a table has, too, the greater the chance a row will not fit within a single block. All of these increase I/O and negatively impact performance.
Follow the ANALYZE link at page bottom and find the section titled "List Chained Rows"
Row Storage The format of a row is: row header, column length - value; column_length - value; column_length - value; ....

The length of a column 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.

If you want to optimize access and improve performance create the table with those columns in order of how often they will need to be accessed rather than in the order in which you might fill out a paper form.
System Privileges
ALTER ANY TABLE DELETE ANY TABLE LOCK ANY TABLE
BACKUP ANY TABLE DROP ANY TABLE SELECT ANY TABLE
COMMENT ANY TABLE FLASHBACK ANY TABLE UNDER ANY TABLE
CREATE ANY TABLE INSERT ANY TABLE UPDATE ANY TABLE
CREATE TABLE    
 
Create Relational Heap Tables
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 <schema_name>.<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 As the output of a Select statement CREATE TABLE <table_name>
TABLESPACE <tablespace_name> AS
<select statement>;
-- CTAS can be used to create an empty table
CREATE TABLE ctas1 AS
SELECT table_name, tablespace_name
FROM all_tables
WHERE 1=2;

SELECT * FROM ctas1;

-- CTAS can be used to clone an existing table with some or a filtered set of its data
CREATE TABLE ctas2 AS
SELECT
table_name, tablespace_name
FROM all_tables
WHERE table_name LIKE 'U%';

SELECT * FROM ctas2;

-- CTAS can be used to create a table as the result of any valid SQL statement
CREATE TABLE ctas3 AS
SELECT CAST(t.table_name, VARCHAR2(30)) AS TABLE_NAME, CAST(i.index_name, VARCHAR2(30)) AS INDEX_NAME
FROM user_tables t, user_indexes i
WHERE t.table_name = i.table_name;

desc ctas3
 
Column Properties Clauses
Column Defaults -- the value inserted into the column if the insert or update would leave the column value NULL. In 12c this can include a sequence generated surrogate key

<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;
Relational Properties: Hidden Column

Note: "set colinvisible" is a SQL*Plus command.
CREATE TABLE [schema_name.]table_name(
<column_name> <data_type> <visible | invisible> [<DEFAULT [ON NULL] <expression>]
<column_name> <data_type> <visible | invisible> [<identity_clause>].. identity clause and identity options
CREATE TABLE vis (
rid     NUMBER,
testcol VARCHAR2(20));

CREATE TABLE invis (
rid     NUMBER,
testcol VARCHAR2(20) INVISIBLE);

show colinvisible
colinvisible OFF

desc vis
desc invis

set colinvisible on

desc vis
desc invis

SELECT table_name, column_name, data_upgraded, hidden_column
FROM user_tab_cols
WHERE table_name like '%VIS';

ALTER TABLE vis MODIFY (rid INVISIBLE);

ALTER TABLE vis MODIFY (rid VISIBLE);

desc vis


-- note the logical column ordering makes the most recently invisible column last in the ordering
Nullable and Not Nullable Columns

Note: Do not make columns that are intended to be primary keys NOT NULL: Does so serves no useful purpose
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;
Relational Properties: Virtual Column <column_name> [<data_type>] [GENERATED ALWAYS] AS (column_expression)
VIRTUAL (inline_constraint)


Note: "GENERATED ALWAYS" and "VIRTUAL" are optional keywords. For an example of a virtual column based on a deterministic function see the Evaluate Edition Clause above.
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';
Evaluate Edition Clause -- to use this clause the schema owner must be editions enabled and the function used to specify the virtual column editioned.

CREATE TABLE [schema_name.]table_name(
<column_specification>
EVALUATE USING <CURRENT EDITION | EDITION <edition_name> | NULL EDITION>;
conn uwclass/uwclass@pdbdev

show edition

CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
  RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/

SELECT evalValue FROM dual;

CREATE TABLE eec (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
EVALUATE USING CURRENT EDITION);

col evaluation_edition format a20

SELECT table_name, column_name, evaluation_edition
FROM user_tab_cols
WHERE table_name = 'EEC';
COLLATION (new 12.2) CREATE TABLE <schema_name>.<table_name> DEFAULT COLLATION <collation_name>
SQL> sho con_id

CON_ID
------
     1

SQL> show user
USER is "SYS"

show parameter max_string_size

NAME             TYPE    VALUE
---------------- ------- ---------
max_string_size  string  STANDARD

ALTER SYSTEM SET max_string_size=extended SID='*' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
SHUTDOWN IMMEDIATE;
STARTUP;

-- work to do here because the published information is not accurate for a Container DB.
 
Table Properties Clauses
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';
FLASHBACK ARCHIVE [NO] FLASHBACK ARCHIVE [flashback_archive_name]
conn sys@pdbdev as sysdba

CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10M
RETENTION 30 DAY;

Flashback archive created.

CREATE TABLE uwclass.tflasharch (
testcol NUMBER(5))
FLASHBACK ARCHIVE uw_archive;

Table created.

INSERT INTO uwclass.tflasharch VALUES (99);
COMMIT;

UPDATE uwclass.tflasharch SET testcol = 42;
COMMIT;

SELECT table_name, owner_name, archive_table_name
FROM dba_flashback_archive_tables;

TABLE_NAME   OWNER_NAME  ARCHIVE_TABLE_NAME
----------- ------------ -------------------
TFLASHARCH  UWCLASS      SYS_FBA_HIST_96699

col rid format a19
col operation format a10

SELECT rid, startscn, endscn, xid, operation, testcol
FROM uwclass.sys_fba_hist_96699;

RID                 STARTSCN   ENDSCN     XID              OPERATION  TESTCOL
------------------- ---------- ---------- ---------------- ---------- -------
AAAXm7AAPAAACsRAAA  18739213   18739321   02001B006D360000 I               99
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;
READ ONLY 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 */
RESULT_CACHE RESULT_CACHE(MODE <DEFAULT | FORCE>)
SELECT result_cache, COUNT(*)
FROM dba_tables
GROUP BY result_cache;

CREATE TABLE rcache (
testcol NUMBER(5))
RESULT_CACHE (MODE FORCE);

SELECT result_cache, COUNT(*)
FROM dba_tables
GROUP BY result_cache;
ROW ARCHIVAL ROW ARCHIVAL
CREATE TABLE trowarch (
rid     NUMBER,
testcol DATE)
ROW ARCHIVAL;

-- look at the table's metadata and columns
SELECT dbms_metadata.get_ddl('TABLE','TROWARCH') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TROWARCH')
------------------------------------------------------------
CREATE TABLE "UWCLASS"."TROWARCH"
 ( "RID" NUMBER,
   "TESTCOL" DATE
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
 TABLESPACE "UWDATA"
 ILM ENABLE LIFECYCLE MANAGEMENT

SELECT column_id, column_name, data_type, data_length, hidden_column
FROM user_tab_cols
WHERE table_name = 'TROWARCH'
ORDER BY column_id;

 COLUMN_ID COLUMN_NAME        DATA_TYPE  DATA_LENGTH HIDDEN
---------- ------------------ ---------- ----------- ------
         1 RID                NUMBER              22 NO
         2 TESTCOL            DATE                 7 NO
           ORA_ARCHIVE_STATE  VARCHAR2          4000 YES

-- note the hidden column ORA_ARCHIVE_STATE above

-- disable, the re-enable in-database archiving
ALTER TABLE trowarch NO ROW ARCHIVAL;
ALTER TABLE trowarch ROW ARCHIVAL;

-- add a primary key constraint
ALTER TABLE trowarch
ADD CONSTRAINT pk_trowarch
PRIMARY KEY(rid);

-- insert some data
INSERT INTO trowarch
SELECT level, (TO_DATE('20-JUN-2017')-500) + level
FROM dual
CONNECT BY level < 1001;
COMMIT;

col ora_archive_state format a20

-- ora_archive_state is populated with the default value "0"
SELECT rid, testcol, ora_archive_state
FROM trowarch
WHERE rownum < 6;

 RID TESTCOL               ORA_ARCHIVE_STATE
---- --------------------- ------------------
   1 07-FEB-2016 16:00:28  0
   2 08-FEB-2016 16:00:28  0
   3 09-FEB-2016 16:00:28  0
   4 10-FEB-2016 16:00:28  0
   5 11-FEB-2016 16:00:28  0

-- Count the table's rows
SELECT COUNT(*)
FROM trowarch;

  COUNT(*)
----------
      1000

UPDATE trowarch
SET ora_archive_state = '1'
WHERE testcol <= TO_DATE('01-JAN-2017');
COMMIT;

-- Count the table's rows after 329 have had their state updated to indicate archival status
SELECT COUNT(*)
FROM trowarch;

  COUNT(*)
----------
       670

-- make archived rows visible and examine the hidden column
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT ora_archive_state, COUNT(*)
FROM trowarch
GROUP BY ora_archive_state;

ORA_ARCHIVE_STATE  COUNT(*)
------------------ ---------
1                       330
0                       670

-- manipulate archiving with the DBMS_ILM package
UPDATE trowarch
SET ora_archive_state = dbms_ilm.archivestatename('1')
WHERE testcol <= TO_DATE('01-FEB-2017')
AND rownum < 11;
COMMIT;

SELECT ora_archive_state, COUNT(*)
FROM trowarch
GROUP BY ora_archive_state;

ORA_ARCHIVE_STATE  COUNT(*)
------------------ ---------
1                       340
0                       660

-- return the session to the default state
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
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. Follow the ORA_ROWSCN link at page bottom for more information
 
Table Options
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 */
Unusable Editions Clause Syntax 1 CREATE TABLE [schema_name.]table_name(
<column_specification>
UNUSABLE BEFORE <CURRENT EDITION | EDITION <edition_name>;
conn uwclass/uwclass@pdbdev

show edition

CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
  RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/

SELECT evalValue FROM dual;

CREATE TABLE uec1 (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
UNUSABLE BEFORE EDITION ora$base);

col unusable_before format a20
col unusable_beginning format a20

SELECT table_name, column_name, unusable_before, unusable_beginning
FROM user_tab_cols
WHERE table_name LIKE 'UEC%';
Unusable Editions Clause Syntax 2 CREATE TABLE [schema_name.]table_name(
<column_specification>
UNUSABLE BEGINNING WITH <CURRENT EDITION | EDITION <edition_name> | NULL EDITION>;
conn uwclass/uwclass@pdbdev

show edition

CREATE OR REPLACE FUNCTION evalValue(rid IN NUMBER) RETURN PLS_INTEGER AUTHID DEFINER DETERMINISTIC IS
BEGIN
  RETURN TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE),rid,1));
END evalValue;
/

SELECT evalValue FROM dual;

CREATE TABLE uec2 (
rid NUMBER,
virtcol NUMBER AS (evalValue(rid))
UNUSABLE BEGINNING WITH CURRENT EDITION);

col unusable_before format a20
col unusable_beginning format a20

SELECT table_name, column_name, unusable_before, unusable_beginning
FROM user_tab_cols
WHERE table_name LIKE 'UEC%';
 
 
ALTER TABLE Statements
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';
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

SELECT table_name, table_lock
FROM user_all_tables
ORDER BY 2;

ALTER TABLE tl_test DISABLE TABLE LOCK;

SELECT table_name, table_lock
FROM user_all_tables
ORDER BY 2;

ALTER TABLE tl_test ADD (col2 VARCHAR2(20));

TRUNCATE TABLE tl_test;

DROP TABLE tl_test PURGE;

ALTER TABLE tl_test ENABLE TABLE LOCK;

SELECT table_name, table_lock
FROM user_all_tables
ORDER BY 2;

TRUNCATE TABLE tl_test;

DROP TABLE tl_test PURGE;
 
Commenting Tables and Columns
Comment a table COMMENT ON TABLE <table_name> IS '<comment_string>';
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
Rename a table 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;
Drop a column of 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 of a large table ALTER TABLE <table_name>
DROP COLUMN <column_name>
CHECKPOINT <integer>;
ALTER TABLE test DROP COLUMN fourth_col CHECKPOINT 1000;
Set a column as 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 an unused column ALTER TABLE <table_name> DROP UNUSED COLUMNS;
ALTER TABLE test DROP UNUSED COLUMNS;

desc test
Drop unused columns in 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


-- in most cases a column must be empty to change the data type
Alter table change data type of multiple columns 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 Space 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. It cannot be specify 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 the Recycle Bin DROP TABLE <table_name>;
DROP TABLE zip_code;
Drop Table and bypass the Recycle Bin DROP TABLE <table_name> PURGE;
DROP TABLE zip_code PURGE;
Drop Table Including Referential Constraints DROP TABLE <table_name> CASCADE CONSTRAINTS;
DROP TABLE work_note_header CASCADE CONSTRAINTS PURGE;
 
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
12c Maximum Sring Size Enhancement

Note: This is a one-way street. Once you perform this ALTER SYSTEM you can not undo it
show parameter max_string_size

CREATE TABLE new_feature(
vcol  VARCHAR2(32767),
nvcol NVARCHAR2(16383),
rcol  RAW(32767));

ALTER SYSTEM SET max_string_size = EXTENDED SCOPE=SPFILE;

shutdown immediate;

startup;

CREATE TABLE new_feature(
vcol  VARCHAR2(32767),
nvcol NVARCHAR2(16383),
rcol  RAW(32767));

desc new_feature
 
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;
Another space usage query SELECT owner, table_name, NVL(num_rows*avg_row_len,0)/1024000 MB
FROM dba_tables
ORDER BY owner, table_name;

Related Topics
Analyze
Buffer Pools
Clusters
Constraints
Data Types & Subtypes
DBMS_REDEFINITION
DBMS_RESULT_CACHE
DBMS_ROWID
DBMS_SHARED_POOL
DBMS_STATS
DDL Statements
External Tables
Flashback Archive
Global Temporary Tables
Identity Columns
Indexes
In-Memory Tables
IOT (Index Organized Tables)
LOBs
Nested Tables
Object Privileges
Object Table Constraints
Object Tables
ORA_ROWSCN
Partitioned Tables
Pseudocolumns
Recycle Bin
Result Cache
Supplemental Logging
Table Flashback
Temporal Validity
Transparent Data Encryption
Triggers
Truncate
XMLType Tables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved