DB Oracle_Table Schema

 Table

• Create table

(
<COLUMN_NAME> NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
<COLUMN_NAME_1>        NUMBER,
<COLUMN_NAME_2>         NUMBER (15,4),
<COLUMN_NAME_3>        INTEGER,
<COLUMN_NAME_4>        CHAR(1 BYTE),
<COLUMN_NAME_5>        VARCHAR2(50 BYTE) NOT NULL,
<COLUMN_NAME_6>        VARCHAR2(50 CHAR),
<COLUMN_NAME_7>        TIMESTAMP(6),
<COLUMN_NAME_8>        TIMESTAMP(6) WITH TIME ZONE,
<COLUMN_NAME_9>        TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
<COLUMN_NAME_10>       DATE 
)

• Create table using constraints

<COLUMN_NAME_1>  NUMBER,
<COLUMN_NAME_2> VARCHAR2(100) NOT NULL,
<COLUMN_NAME_3> VARCHAR2(100) NOT NULL,
<COLUMN_NAME_4>  NUMBER NOT NULL,

CONSTRAINT SO_DETAIL_PK PRIMARY KEY (COLUMN_NAME_1),
CONSTRAINT SO_DETAIL_UK UNIQUE (COLUMN_NAME_2, COLUMN_NAME_4)
)

• Get All table name

SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

Column

• Add new column

ALTER TABLE <TABLE_NAME> ADD <COLUMN_NAME> <DATA_TYPE>;

• Update/Rename column name

ALTER TABLE <TABLE_NAME> RENAME COLUMN <OLD_COLUMN_NAME> TO <NEW_COLUMN_NAME>;

• Update Data type / Constraints

                 ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> <DATA_TYPE>;
            

ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> <DATA_TYPE> NOT NULL;

• Drop Column

         ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;

Sequence

• Show all sequence

                    --  https://www.codegrepper.com/code-examples/sql/oracle+reset+sequence

• Create new Sequence

                 CREATE SEQUENCE Test_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 99 NOCYCLE CACHE 20;

• Update Sequence

ALTER SEQUENCE TEST_SEQ 

     INCREMENT BY 1

     MINVALUE 1

     MAXVALUE 100

     NOCYCLE;

• Drop Sequence

         DROP SEQUENCE TEST_SEQ;

• Sequence

         SELECT TEST_SEQ.NEXTVAL FROM DUAL;

 

Column Constraints

• Create Unique constraints

                 ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <UNIQUE_CONSTRAINT_NAME> UNIQUE (<COLUMN_NAME_1> , <COLUMN_NAME_2>, <COLUMN_NAME_3>);

• Drop constraints

                ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <UNIQUE_CONSTRAINT_NAME>;