Oracle tables

Home

General
Table Related Data Dictionary Objects	tab$ col$ user_tables all_tables dba_tables user_tab_columns all_tab_columns dba_tab_columns
Table Related System Privileges	create table create any table alter any table backup any table comment any table drop any table delete any table flashback any table insert any table lock any table select any table under any table update any table
Types Of Tables	Heap Global Temporary Clustered External Index Organized Partitions XML
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 within 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:
Definitions
BUFFERPOOL	Defines a default buffer pool (cache) for a schema object. Not valid for tablespaces or rollback segments.
DEFAULT	The value inserted into the column if the insert or update would leave the column value NULL
FREELIST	The number of lists maintained on a table that can be used to identify a block available for insert. Set this to 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 the process id. Using a prime number with such mechansims 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).
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.
MAXEXTENTS	The total number of extents, including the first, that can ever be allocated for the segment.
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.
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.
PARALLEL	Example to be developed
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.
	SELECT table_name, pct_free FROM user_tables;
PCTINCREASE	Obsolete parameter with locally managed tablespaces. The percentage by which each incremental extent grows over the last incremental extent allocated for a segment. If PCTINCREASE is 0, then all incremental extents are the same size. If PCTINCREASE is greater than zero, then each time NEXT is calculated, it grows by PCTINCREASE. PCTINCREASE cannot be negative. The new NEXT equals 1 + PCTINCREASE/100, multiplied by the size of the last incremental extent (the old NEXT) and rounded up to the next multiple of a block size. Should always be 0 (zero).
	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.
	SELECT table_name, pct_used FROM user_tables;
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.
	SELECT table_name, tablespace_name FROM user_tables;
Create HeapTable
Simple Create Table With One Column	CREATE TABLE organization ( organization_name   VARCHAR2(40)) PCTFREE   5 PCTUSED   90 INITRANS  1 MAXTRANS  254 TABLESPACE <tablespace_name>;
	Test with simple SQL Statements: DESC organization SELECT organization_name FROM organization; INSERT INTO organization (organization_name) VALUES ('AAAAA'); SELECT organization_name FROM organization; UPDATE organization SET organization_name = 'ZZZZZ'; SELECT organization_name FROM organization; DELETE FROM organization; SELECT organization_name FROM organization;
Create Table With Multiple Columns	CREATE TABLE campus_site ( site_id            NUMBER(4), organization_name  VARCHAR2(40), campus_name        VARCHAR2(30), address_id         NUMBER(10), last_change_by     VARCHAR2(30), last_change_dt     DATE) PCTFREE   5 PCTUSED   90 INITRANS  1 MAXTRANS  254 TABLESPACE <tablespace_name>;
Create Table With NOT NULL Constraints	CREATE TABLE campus_site (  site_id           NUMBER(4), organization_name  VARCHAR2(40) NOT NULL, campus_name        VARCHAR2(30) NOT NULL, address_id         NUMBER(10) NOT NULL, last_change_by     VARCHAR2(30), last_change_dt     DATE) PCTFREE   5 PCTUSED   90 INITRANS  1 MAXTRANS  254 TABLESPACE <tablespace_name>;
Define Column DEFAULT	CREATE TABLE <table_name> ( <column_name> <data_type>, <column_name> <data_type> DEFAULT <default_value>) TABLESPACE <tablespace_name>;
	CREATE TABLE campus_site ( site_id           NUMBER(4), organization_name VARCHAR2(40) NOT NULL, campus_name       VARCHAR2(30) NOT NULL, address_id        NUMBER(10) NOT NULL, last_change_by    VARCHAR2(30) DEFAULT USER, last_change_dt    DATE DEFAULT SYSDATE) TABLESPACE data_sml; DESC campus_site INSERT INTO campus_site (site_id, organization_name, campus_name, address_id) VALUES (1, 'University of Washington', 'Seattle Main', 1); COMMIT; SELECT * FROM campus_site;
Create Table Using Select Statement With No Data Using Tablespace Defaults	CREATE TABLE <table_name> TABLESPACE <tablespace_name> AS <select statement>;
	CREATE TABLE campus_site_archive TABLESPACE <tablespace_name> AS SELECT site_id, organization_name, campus_name, address_id FROM campus_site WHERE 1=2; SELECT * FROM campus_site_archive;
Create Table Using Select Statement With Data	CREATE TABLE <table_name> PCTFREE <integer> PCTUSED <integer> INITRANS <integer> MAXTRANS <integer> TABLESPACE <tablespace_name> AS <select statement>;
	CREATE TABLE campus_site_archive TABLESPACE <tablespace_name> AS SELECT site_id, organization_name, campus_name, address_id FROM campus_site; SELECT * FROM campus_site_archive;
Create Table Using Select Statement With Data And Full Table Definition	CREATE TABLE <table_name> PCTFREE <integer> PCTUSED <integer> INITRANS <integer> MAXTRANS <integer> TABLESPACE <tablespace_name> AS <select statement>;
	CREATE TABLE campus_site_archive PCTFREE   5 PCTUSED   90 INITRANS  1 MAXTRANS  254 TABLESPACE <tablespace_name> AS SELECT * FROM campus_site;
Create Table With MONITOR Clause	CREATE TABLE <table_name> ( <column_name> <data_type>, <column_name> <data_type>) MONITORING TABLESPACE <tablespace_name>;
	CREATE TABLE campus_site ( site_id           NUMBER(4), organization_name VARCHAR2(40), campus_name       VARCHAR2(30), address_id        NUMBER(10), last_change_by    VARCHAR2(30), last_change_dt    DATE) MONITORING TABLESPACE data_sml; INSERT INTO campus_site (site_number, organization_name) VALUES (1, 'UW'); COMMIT; SELECT table_name, inserts, updates, deletes, truncated FROM user_tab_modifications;
Create Global Temporary Table
Global temporary tables have two major benefits: 1. Non-interference between private sets of data. 2. Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do the same thing). 3. Decreased redo generation as, by definition, they are non-logging.
Create Global Temporary Table That Empties On Commit	You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace. CREATE GLOBAL TEMPORARY TABLE gtt_zip ( zip_code     VARCHAR2(5), by_user      VARCHAR2(30), entry_date   DATE); alternative ... explicitly specifying the ON COMMIT action ... CREATE GLOBAL TEMPORARY TABLE gtt_zip ( zip_code     VARCHAR2(5), by_user      VARCHAR2(30), entry_date   DATE) ON COMMIT DELETE ROWS;
	Test ON COMMIT DELETE ROWS: INSERT INTO gtt_zip (zip_code, by_user, entry_date) VALUES ('98000', USER, SYSDATE); SELECT * FROM gtt_zip; COMMIT; SELECT * FROM gtt_zip;
Create Global Temporary Table That Empties At End Of  Session	You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace. CREATE GLOBAL TEMPORARY TABLE gtt_zip ( zip_code     VARCHAR2(5), by_user      VARCHAR2(30), entry_date   DATE) ON COMMIT PRESERVE ROWS;
	Test ON COMMIT PRESERVE ROWS: INSERT INTO gtt_zip (zip_code, by_user, entry_date) VALUES ('98000', USER, SYSDATE); SELECT * FROM gtt_zip; COMMIT; SELECT * FROM gtt_zip; -- log on as a different user -- log back on as original user SELECT * FROM gtt_zip;
Parallel Clause
Parallel	CREATE TABLE zip_code ( zip_code        VARCHAR2(5), state_abbrev    VARCHAR2(2), city            VARCHAR2(40), last_change_by  VARCHAR2(30), last_change_dt  DATE) PCTFREE   5 PCTUSED   90 INITRANS  1 MAXTRANS  254 PARALLEL(DEGREE 8) TABLESPACE <tablespace_name>;
	SQL to identify a table with non-default parallelism SELECT table_name, degree FROM user_tables;
Commenting
Comment a table	COMMENT ON TABLE zip_code IS 'US Postal Service Zip Codes';
View table comments	SELECT table_name, comments FROM user_tab_comments;
Comment a column	COMMENT ON COLUMN zip_code.zip_code IS '5 Digit Zip Code';
View column  comments	SELECT table_name, column_name, comments FROM user_col_comments;
Alter Table
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
Create A Column Default	ALTER TABLE <table_name> MODIFY (field_name DEFAULT SYSDATE);
	ALTER TABLE test ADD (last_change_by VARCHAR2(30)); DESC test COL table_name FORMAT a20 COL column_name FORMAT a20 COL data_default FORMAT a20 SELECT table_name, column_name, data_default FROM user_tab_columns WHERE data_default IS NOT NULL; ALTER TABLE test MODIFY (last_change_by DEFAULT USER); SELECT table_name, column_name, data_default FROM user_tab_columns WHERE data_default IS NOT NULL; ALTER TABLE test ADD (last_change_date DATE DEFAULT SYSDATE); DESC test SELECT table_name, column_name, data_default FROM user_tab_columns WHERE data_default IS NOT NULL; SELECT * FROM test; INSERT INTO test (first_col, date_col) VALUES ('A', SYSDATE); SELECT * FROM test;
Change A Column Default	ALTER TABLE <table_name> MODIFY (<column_name DEFAULT <condition>);
	ALTER TABLE test MODIFY (last_change_date DEFAULT SYSDATE+30); INSERT INTO test (first_col, date_col) VALUES ('B', SYSDATE); SELECT * FROM test;
Remove A Column Default	ALTER TABLE <table_name> MODIFY (field_name DEFAULT NULL);
	ALTER TABLE test MODIFY (last_change_by DEFAULT NULL); INSERT INTO test (first_col, date_col) VALUES ('C', SYSDATE); SELECT * FROM test;
Make A Column NOT NULL	ALTER TABLE <table_name> MODIFY (field_name NOT NULL);
	ALTER TABLE test MODIFY (last_change_by NOT NULL); TRUNCATE TABLE test; ALTER TABLE test MODIFY (last_change_by NOT NULL); INSERT INTO test (first_col, date_col) VALUES ('D', SYSDATE); INSERT INTO test (first_col, date_col, last_change_by) VALUES ('E', SYSDATE, USER); SELECT * FROM test;
Make A NOT NULL Column NULLABLE	ALTER TABLE <table_name> MODIFY (field_name NULL);
	ALTER TABLE test MODIFY (last_change_by NULL); INSERT INTO test (first_col, date_col) VALUES ('F', SYSDATE); SELECT * FROM test;
Alter Table Change Data Type	ALTER TABLE <table_name> MODIFY (<column_name new_data_type);
	CREATE TABLE test ( test_col   VARCHAR2(20)); DESC test ALTER TABLE test MODIFY (test_col NUMBER(6)); DESC 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 test ( col_one NUMBER(10), col_two VARCHAR2(10), dat_col DATE); DESC test ALTER TABLE test MODIFY (col_one NUMBER(12), col_two VARCHAR2(20)); DESC test
Move Table To A Different Tablespace	ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;
	SELECT tablespace_name FROM user_tables WHERE table_name = 'TEST'; ALTER TABLE test MOVE TABLESPACE indx_sml; SELECT tablespace_name FROM use_tablespaces WHERE table_name = 'TEST';
Change Table Storage Parameters	ALTER TABLE <table_name> MOVE STORAGE (INITIAL <value>, NEXT <value>, MINEXTENTS <value>, MAXEXTENTS <value>, PCTINCREASE <value>);
Force  Extent Allocation	ALTER TABLE <table_name> ALLOCATE EXTENT;
	ALTER TABLE test ALLOCATE EXTENT;
Move Table Containing An LOB Segment To A Different Tablespace	ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name> LOB (<lob_column_name>) STORE AS <lob_segment_name> (TABLESPACE <tablespace_name>);
	Example to be developed
Deallocate Unused Space	ALTER TABLE <table_name> DEALLOCATE UNUSED;
	Example to be developed
Make A Logging Table No Logging	ALTER TABLE <table_name> NOLOGGING;
	Example to be developed
Make A No Logging Table Logging	ALTER TABLE <table_name> LOGGING;
	Example to be developed
Drop Table
Drop Table	DROP TABLE <table_name>;
	DROP TABLE zip_code;
Drop Table Cascade Constraints	DROP TABLE <table_name> CASCADE CONSTRAINTS;
	DROP TABLE work_note_header CASCADE CONSTRAINTS;
New With 10g
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;

Notes
Slow table reads	The init parameter db_file_multiblock_read_count is paramount
Rename a table	RENAME <current_table_name> TO <new_name>;
	CREATE TABLE old_name (    test     VARCHAR2(20)) TABLESPACE <tablespace_name>; DESC old_name RENAME TABLE old_name TO new_name; DESC new_name
Analyzing tables	In earlier versions of Oracle, Oracle provided a number of tools. For table analysis intended to help the optimizer use the DBMS_STATS built-in package. For table analysis to find corruption or gather other statistics use ANALYZE_TABLE.
Table Column String Datatypes	CHAR or CHARACTER(n)   (up to 2KB) CLOB                    (up to 4GB) LONG                    (up to 2GB) VARCHAR2(n)            (up to 4KB) Never use VARCHAR(#) as it defines a C language data type and is not guaranteed to work in the future.
	CREATE TABLE string_types ( char_field      CHAR(2000), varchar_field   VARCHAR2(4000), clob_field      CLOB); DESC string_types
Table Column Numeric Datatypes	DECIMAL                       (up to 38 bytes) FLOAT                         (up to 126) INTEGER or INT or SMALLINT   (size not specified) NUMBER                        (up to 38 bytes) NUMBERIC                      (up to 38 bytes)
	CREATE TABLE number_types ( decimal_col               DECIMAL, double_prec_col           DOUBLE PRECISION, float_col                 FLOAT(126), integer_col               INTEGER, int_field_col             INT, smallint_col              SMALLINT, number_col                NUMBER, number_decimal_col(p,s)   NUMBER(10,2), numeric_col               NUMERIC, real_col                  REAL);
Table Column Data Datatypes	DATE             (size not specified) NTERVAL YEAR (year_precision) TO MONTH INTERVAL DAY (day_precision)    TO SECOND (fractional_seconds_precision) TIMESTAMP(n)    (size not specified) TIMESTAMP (fractional_seconds_precision)    WITH LOCAL TIME ZONE
	CREATE TABLE date_data_types ( date_col           DATE, timestamp_col(9)   TIMESTAMP);
Table Column Binary Datatypes	BLOB        (binary data to 4GB) LONG RAW   (binary data to 2GB) RAW        (binary data to 2KB)
Other Data Types	NCHAR(n)       (up to 2KB bytes) NCLOB         (up to 4GB) NVARCHAR(n)   (up to 4KB)
	CREATE TABLE nat_char_set ( nchar_col      NCHAR(2000), nclob_col      NCLOB, nvarchar_col   NVARCHAR(4000);
External Data Type	BFILE
	Example to be developed
Delete vs. Truncate by Howard Rogers	Deletes do normal DML. That is, they take locks on rows, they generate redo (lots of it), and they need rollback segments. They clear records out of blocks carefully. You can roll the command back. No space is relinquished by the segment -it still possesses exactly the same number of blocks after the delete as before. Truncates cheat. They move the High Water Mark of the table back to ground zero, and that's pretty much all they do. No row-level locks, no redo, and no rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unilke with deletes, of course).
Delete all rows from a table	DELETE FROM <table_name>;
	INSERT INTO organization (organization_name) VALUES ('AAAAA'); INSERT INTO organization (organization_name) VALUES ('BBBBB'); INSERT INTO organization (organization_name) VALUES ('CCCCC'); DELETE FROM organization; COMMIT;
Delete specific rows from a table	DELETE FROM <table_name> WHERE <column_name> = <specific_value>
	INSERT INTO organization (organization_name) VALUES ('AAAAA'); INSERT INTO organization (organization_name) VALUES ('BBBBB'); INSERT INTO organization (organization_name) VALUES ('CCCCC'); DELETE FROM organization WHERE organization_name = 'BBBBB'; COMMIT;
ROWID	ROWID is what is referred to a pseudo-column. It is not data in the database or table so much as it is a mapping of the location, in a specific datafile of the physical location of a row of data. Since rows can migrate from location-to-location when they are updated ROWID should never be stored an never be counted on to be the same in any database.
	INSERT INTO organization (organization_name) VALUES ('AAAAA'); INSERT INTO organization (organization_name) VALUES ('BBBBB'); INSERT INTO organization (organization_name) VALUES ('CCCCC'); COMMIT; SELECT ROWID, organization_name FROM organization; TRUNCATE TABLE organization;
ROWNUM	ROWNUM is what is referred to as a pseudo-column. It is not data in the database or table and has absolutely no relationship to anything in the database, data file, tablespace, or table or to the order in which a row is inserted into a table. Rather it is the number of a row selected from a table and depending on the order in which rows are selected will change.
	INSERT INTO organization (organization_name) VALUES ('AAAAA'); INSERT INTO organization (organization_name) VALUES ('BBBBB'); INSERT INTO organization (organization_name) VALUES ('CCCCC'); COMMIT; SELECT ROWNUM, organization_name FROM organization SELECT ROWNUM, organization_name FROM organization WHERE organization_name <> 'AAAAA';
	Because the first row selected is ROWNUM 1, the second ROWNUM 2, trying to view row 2 with a simple use of ROWNUM will not work.
	SELECT ROWNUM, organization_name FROM organization; SELECT organization_name FROM organization WHERE ROWNUM = 1; SELECT organization_name FROM organization WHERE ROWNUM = 2; SELECT organization_name FROM organization WHERE ROWNUM <= 2; SELECT organization_name FROM organization WHERE ROWNUM < 3;
Simple truncate	TRUNCATE table <table_name> By default this drops storage even if not specified
	TRUNCATE TABLE organization; or more explicitly TRUNCATE TABLE organization DROP STORAGE;
Full truncate	TRUNCATE TABLE <table_name>;
	TRUNCATE TABLE organization REUSE STORAGE;

Table Related Queries
How much space is a query 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;