-- Add Primary Key 
-- VALIDATE is default
-- ENABLE VALIDATE - All data (existing/new) complies with the constraint
-- ENABLE NOVALIDATE - Only new data to comply with the constraint

ALTER TABLE table_name ADD 
 CONSTRAINT  PK_contraint_name PRIMARY KEY (column_name)
 USING INDEX PK_contraint_name
 ENABLE VALIDATE
;

-- Add Foreign Key

ALTER TABLE table_name ADD 
CONSTRAINT  FK_contraint_name FOREIGN KEY (column_name)
 REFERENCING parent_table (column_name)
;

-- Add Check constraint

ALTER TABLE table_name ADD
(CONSTRAINT  chk_constraint_name CHECK (column_name IN ('Y', 'N')) 
);

-- Add Column (if not null constraint is needed, default data "value" should be 
-- of the same datatype (date, number etc.) for the table enhancement to succeed

ALTER TABLE table_name ADD
(column1_name     data_type DEFAULT value NOT NULL,
 column2_name     data_type
);

-- Drop Identity (oracle12c and higher)

ALTER TABLE table_name MODIFY
(identity_column_id   DROP IDENTITY
);

-- Rename table Column

ALTER TABLE table_name RENAME COLUMN current_name TO new_name; 

-- Reassign tablespace (to new_tablespace)

ALTER TABLE table_name MOVE TABLESPACE new_tablespace; 

-- Drop table column script with check for column usage

SELECT  alter_tab_drop_column
FROM
(SELECT  atc.column_id,
 'ALTER TABLE '||LOWER(atc.table_name)||' DROP COLUMN '||LOWER(atc.column_name)||';' 
   alter_tab_drop_column
FROM  all_tab_columns atc
WHERE 0 = (SELECT COUNT(*) FROM all_source s
           WHERE  INSTR(UPPER(s.text),atc.column_name) >0)
and    atc.column_name = UPPER('&i_col')
ORDER BY atc.column_id);

-- Drop table column (by force)

ALTER TABLE table_name DROP COLUMN  column_name;

ALTER TABLE table_name ENABLE ALL TRIGGERS;

-- Disable Trigger (for enabling -> ENABLE)

ALTER TRIGGER trigger_name  DISABLE;

ALTER TRIGGER trigger_name  COMPILE;

-- Grant Privilege

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO db_user;

-- object_name = function_name, procedure_name, package_name

GRANT EXECUTE ON object_name TO db_user;

-- Revoke Privilege

REVOKE SELECT, INSERT, UPDATE, DELETE ON table_name FROM db_user;

-- Column Dropping In Compressed Tables

ALTER TABLE table_name SET UNUSED COLUMN column_name;

-- Tablespace Resize

ALTER TABLESPACE  tablespace_nm  RESIZE 10g;

ALTER TABLESPACE  tablespace_nm  SHRINK SPACE KEEP 10g;

-- Adding New Partition by exchange (Swapping)

ALTER TABLE table_name 
EXCHANGE PARTITION  partition_name 
WITH TABLE stg_table_name           --table with data specific to new partition 
;


Database Reference  19c  12c  11g

Get_DDL