my notes...

NAME OF TABLE/COLUMS : Must begin with a letter.
SIZE : 1-30 characters
Allowed characters in a name : A-Z,a-z,0-9,_,$,#

Note : Name must not be duplicate another object owned by the owner. And it must not be an Oracle reserved word. Names are not case sensetive.

DATA TYPES

VARCHAR2(max_size) : Variable length Character data. (Min size:1; Max size:4000)

CHAR([size]) : Fixed length Character data, Oracle pads the values with blanks upto the declared length of the column. (default/Min size:1; Max size:2000)

• eg . : create table test1 (column1 char(10), column2 varchar2(10));
• insert into test1 values ('Ram', 'Ram');
• select vsize(column1), vsize(column2) from test1 ; -----> length(column1) -> 10, length(column2)->3
  

NUMBER([n,m]) : n: total number of digits(1 to 38, default 38), m: number of decimal points(upto 38).

• eg: xyx(15,2) // ---------> 1234567890123.45 ;
  
• inserting more than 13 digits to the left of decimal point will result in an error.
  
• inseting more than 2 decimal points will result in rounding off.
  

DATE : between Jan 1,4712 BC to Dec 31, 9999 AD. DATE columns are 7 bytes in length.

LONG : Variable length Character data, upto 2 gigabyte.

• Not copied when table created using a sub-query.

• Can't be used in a GROUP BY or ORDER BY clause.
• No constraints can be defines. Use CLOB instead.
• Data in LONG & LONG RAW stored inline with the rest of the table data. So only one column can be declared as LONG type in a table

• Note : For newer data types BLOB,CLOB, NCLOB data is stored in following ways:

• if less than 4k, the data can be stored inline with the rest of the data in the table.
  
• if more than 4K, the data is stored outside the table & a pointer to it is stored inline with the rest of the table.
  
• This allows Oracle to store more than one BLOB,CLOB, NCLOB column per table unlike LONG or LONG RAW.
  

CLOB : Character data upto 4 gigabyte.
RAW(MAX_size) : Raw binary data. Max size is 2000.
LONG RAW : Raw binary data of variable length upto 2 gigabyte.
BLOB : Binary data upto 4 gigabyte.
BFILE : Binary data stored in external file; upto 4 gigabyte.
ROWID : A 64 base number system representing the unique address of a row in its table. These could be physical ROWIDs or logical ROWIDs.

Single bytes or multibyte UNICODE character based data types :

NVARCHAR2(MAX_size), NCHAR ([size]), NCLOB : same as above but stores UNICODE data.

DDL COMMANDS

Summary of available DDL commands :

CREATE TABLE tablename
ALTER TABLE tablename

• ADD (columns) .....

• ADD CONSTRAINT .....

• MODIFY (columns) .....

• MODIFY CONSTRAINT...

• DROP (columns) .....

• DROP COLUMN column_name .....
• DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

• DISABLE CONSTRAINT constraint_name [CASCADE];

• ENABLE CONSTRAINT constraint_name;

• SET UNUSED

• DROP UNUSED COLUMNS

DROP TABLE tablename
TRUNCATE tablename
RENAME ... TO ...
COMMENT ON TABLE tablename IS '....' ;

Explainations :

CREATE TABLE [schema.]table
(column_name datatype [DEFAULT expression] [[CONSTRAINT constraint_name] constraint type]
[,column_name ....]
[,[CONSTRAINT constraint_name] constraint type (column,column...)]
[,CONSTRAINT ....] );

DEFAULT can be literal values, expressions, SQL functions. These can't be another column name or pseudocolumn.

CREATE TABLE tablename [(column [DEFAULT expression] [ [CONSTRAINT constraint_name] constraint type], [...])]
AS
SELECT [*|column list] FROM tablename2;

• Integrity rules are not copied. Only column type definitions are passed onto new table.

• Not Null constraint is passed. (no Default value setting passed?)
• Without column list, the name of the table columns are the same as the subquery.
• If column names are listed, then they can't show column_type. Only DEFAULT value may be configured.
• Use a column alias to change the column name, or where an expression is used.
• Use * to copy full table.
• If table is copied using subquery and constraints are also declared for the new table, any data not meet the constraints results in the failure of DDL and table is not created. For selected creation of table use the condition is the subquery part.

ALTER TABLE table_name
ADD (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

Note : The new columns is initially null for all the rows.

ALTER TABLE table_name
MODIFY (column [datatype] [DEFAULT expr] [CONSTRAINT constraint_name constraint_type] | [NOT NULL]);

• Modify a columns data_type, size, default values. Modification only effects subsequent values.

• Data type change possible only if the column has null values.
• Increase width/precision : decrease possible only if table is empty (or if actual data permits?)
• Change of DEFAULT value effects only subsequent insertions.
• CHAR<-> Varchar2 only if null values or if u don't change width (or if actual data permits?).

ALTER TABLE table_name
DROP (column, column,....) [CASCADE CONSTRAINTS];

ALTER TABLE table_name
DROP COLUMN column_name [CASCADE CONSTRAINTS];

• Dropped column may or may not contain data. The dropped column can't be recovered.

• With DROP COLUMN command only one column can be dropped at a time.

• The table must have one remaining column after the alteration.

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

ALTER TABLE table_name
SET UNUSED (column_name, column_name,.....);

• Once a column is marked UNUSED a user has no access to that column.

• Helps to mark the unused columns, to be dropped during low resource demand period.

• The table must have one remaining column after the alteration.

ALTER TABLE tablename
DROP UNUSED COLUMNS;

• Use drop to clean up marked unused columns when demand on system resources is low. This command returns without error even if the table has no unused columns.

• Can have multiple add/modify/drop constraints in a single ALTER TABLE command except drop column/set unused commands. eg:

alter table my_emp2
drop Primary Key
drop Unique (Lname)
modify (id number(6))
add (id2 number(8,4));

DROP TABLE tablename;

• Removes the defination of an Oracle table. The database loose all data in the table and all the indexes associated with it. This command is irreversable, the table is removed without question. Remember that all DDL statements are committed automatically!

RENAME tablename1 TO tablename2;

• Only the owner can rename the table. Rename a table, sequence or synonym.

TRUNCATE TABLE tablename;

• Only the owner or have DELETE TABLE System privileges to truncate table. If the table is the parent of a referencial entity, it can't be truncated. Such constraints must be disabled prior to TRUNCATE.

• TRUNCATE removes all rows of the table & release the storage space used by them. Oracle uses a characteristic called 'high-water mark' to keep track of the largest size the table has ever grown to. TRUNCATE rests this high-water mark to zero thereby improving performance.

• Rows removed using TRUNCATE can't be rolled back. Alternatively use "DELETE [FROM] tablename" which doesnot release storage space and can be rollbacked.

Note : TRUNCATE is faster than DELETE TABLE for following reasons:

• DDL statement, so generates no rollback information.

• Truncating doesnot fire delete triggers of the table.

COMMENT ON TABLE tablename IS 'this is test table';

COMMENT ON COLUMN tablename.column IS ' testing column comment';

• Drop a comment using empty string (''). Can't be rolled back.

ALTER TABLE tablename
ADD [CONSTRAINT constraint_name] constraint type (column);

ALTER TABLE tablename
MODIFY column [CONSTRAINT constraint_name] NOT NULL;

ALTER TABLE tablename
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name [CASCADE];

ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;

NOTE : DISABLE/ENABLE can be used in both CREATE TABLE/ALTER TABLE statements. CASCADE option causes any depended constraints also to be dropped or disabled.

DISABLEing a Primary Key or Unique Key constraint removes the unique index.

ENABLING creates the UNIQUE KEY or PRIMARY KEY indexes.If you enable a constraint, then all data in the table must fit the constraint. Enabling a primary key constraint disabled using CASCADE doesnot enable any foreign keys that are dependent upon the primary key.

Steps to take in case existing data violates a disabled Constraint:

• Create the special table called EXCEPTIONS ( created by running ORACLE_HOME/rdbms/admin/utlexcpt.sql).

• Describe exceptions;

  ROW_ID ROWID
  OWNER VARCHAR2(30)
  TABLE_NAME VARCHAR2(30)
  CONSTRAINT VARCHAR2(30)

• alter table enable constraint PK_.... -> can't enable, Primary Key Violated.

• select e.row_id, a.column from exceptions e, tablename a where e.row_id=a.row_id;

• either modify the identified offending records or drop them and then re-enable constraints!!

CONSTRAINTS

Syntax :

Column Constraint Level : column [CONSTRAINT constraint_name] REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Table Constraint Level :
column,... [CONSTRAINT constraint_name] FOREIGN KEY (column,...) REFERENCES tablename(column_name) [ON DELETE CASCADE|ON DELETE SET NULL]

Note : check constraints declared at table level can't reference other columns. All constraints can be defined at Table Level except NOT NULL. Composite unique/primary key/Foreign key contraints are created at table level declaration.

NOT NULL : can only be defined at column level. A NOT NULL constraint can only be defined if the table is empty or if the column has values for all the rows.

UNIQUE : Ensures uniqueness of column or column combination. Unless NOT NULL is also defined of the column, unique constraint allows any number of null values as NULL are not considered equal to anything. If the Unique constraint comprises more than one column eg. Unique(col1,c2) that group of column is called a Composite Unique Key. Can't have identical values in the non-null columns of a partially null UNIQUE key constraint.

PRIMARY KEY : Ensures uniqueness of column or column combination. No columns which are part of a composite primary key can contain a NULL. Only one Primary key is allowed per table.

Oracle enforces Unique & Primary key constraints by implicitly creating UNIQUE indexes on these columns/column combinations.

FOREIGN KEY : Referential integrity constraint. Designates a column or column combination as foreign key and establishes a relationship between a primary key or unique key in the same table or different table. A foreign key must match an existing value in the parent table or be NULL. Foreign keys are based on data values and are purely logical pointers.The rows in the parent table can't be deleted if it is referenced in the child table, unless ON DELETE CASCADE or ON DELETE SET NULL is specified. This default behaviour is called the restrict rule which disallows update or deletion of referenced data.

Note: A FOREIGN KEY can't be created on the child table until the parent table is created and the PRIMARY KEY is defined on that parent table.

CHECK constraints : Defines a condition that each row must satisfy.

• Static criteria : CHECK constraints can only compare data in that column to a specific set of constant values or operations on those values.

• A column can have multiple check constraints which refer to the column in its defination. There is no limit on the number of these check constraints.

The CHECK condition may be the same as SQL conditions except following:

• references to CURRVAL, NEXTVAL, LEVEL, ROWNUM & ROWID pseudocolumns

• call to SYSDATE, UID, USER & USERENV

• reference to any other column or row in this or any other table

CASCADE CONSTRAINTS : Used along with DROP COLUMN clause. It drops all referencial integrity constraints that refer to the primary & unique keys defined on the dropped columns. It also drops all multicolumn constraints defined on the dropped columns.

eg.
CREATE TABLE test1 (column1 primary key,columns...,
CONSTRAINT constraint_name1 FOREIGN KEY (column1) references test1,
CONSTRAINT constraint_name2 CHECK (column1>0, column3>0);

Alter Table drop column1 ; -> error as column1 is a parent key
ALTER TABLE DROP column3 ; -> error because column3 is referenced by multi-column constraint_name2

Solution :
ALTER TABLE DROP (column1) CASCADE CONSTRAINTS; -> drops constraints primary key, foreign key & constraint_name2

ALTER TABLE DROP (column1,column2,column3); -> this is valid as all the columns referenced by the constraints defined on the dropped columns are also dropped.

DateTime Data Types

TIMESTAMP [(fractional_second_precision)][WITH TIME ZONE | WITH LOCAL TIME ZONE]

• fractional_second precision : range : 0 to 9; default : 6

WITH TIME ZONE : timestamp + time zone displacement between local time & UTC(GMT). Can't be used for index/primary keys/unique keys.

WITH LOCAL TIME ZONE : Data stored in database is normalised to the database time zone.
Oracles returns the data in the user's local session time zone.

INTERVAL YEAR [(year precisiom)] TO MONTH

• To get the interval in years & months

• Default year precision is 2

• Default month precision is 3

example:
INTERVAL '123-2' YEAR(3) TO MONTH : +123-02 (month value can be 0-11)
INTERVAL '123' YEAR(3) : +123-00
INTERVAL '300' MONTH(3) : +025-00

INVERVAL DAY[day precision] TO SECOND[fractional_second_precision]

• To get the interval to fraction of seconds

• Day Precision : 0-9, Default 2.

example:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3) : +04 05:12:10.222
INTERVAL '7' DAY : +07 00:00:00
INTERVAL '180' DAY(3) : +180 00:00:00
INTERVAL '4 5:12' DAY TO MINUTE : +04 05:12:00
INTERVAL '4 5' DAY TO HOUR : +04 05:00:00
INTERVAL '5:12:10.1234567' HOUR TO SECOND(7) : +00 05:12:10.1234567

Data Dictionary Views :
All data dictionary tables are owned by SYS user.

Data dictionary views :

Prefixes:

• USER_ : Information about objects owned by user

• ALL_ : Information about all objects accessible to the user
• DBA_ : Restricted views accessed only by a DBA role user
• V$ : dynamic views of database server performance, memory, locking

Default data dictionary views available in Oracle 9i :

• USER_TABLES or TABS : (tables)

• USER_OBJECTS : (tables,index,sequences)
• USER_CATALOG or CAT
• TAB

• USER_UNUSED_COL_TABS : shows table_name with the count of unused columns.

• ALL_COL_COMMENTS

• USER_COL_COMMENTS
• ALL_TAB_COMMENTS
• USER_TAB_COMMENTS

• USER_CONSTRAINTS

• USER_CONS_COLUMNS

Note : C: check P: Primary key, R : Referential Integrity, U : Unique key. Not NULL is a check constraint

A SQL statement to get a useful table constraints view :

SELECT ucc.table_name, ucc.column_name, uc.constraint_type, ucc.constraint_name,
uc.r_constraint_name,uc.delete_rule,uc.status,uc.search_condition, uc.index_name
FROM user_constraints uc
JOIN user_cons_columns ucc
ON (ucc.constraint_name=uc.constraint_name)
ORDER BY ucc.table_name;