Home  >  Oracle Database Administration (DBA)  >  Maintenance  >  Managing Database Tables and Indexes

Create a database table

                CREATE TABLE EMP

                                   ( EMPNO NUMBER(4),

                                     ENAME VARCHAR2(10 BYTE),

                                     JOB VARCHAR2(9 BYTE),

                                     MGR NUMBER(4),

                                     HIREDATE DATE,

                                     SAL NUMBER(7,2),

                                     COMM NUMBER(7,2),

                                     DEPTNO NUMBER(2) )

                TABLESPACE USERS

                PCTUSED 0

                PCTFREE 10

                INITRANS 1

                MAXTRANS 255

                STORAGE (

                           INITIAL 64K

                           MINEXTENTS 1

                           MAXEXTENTS 2147483645

                           PCTINCREASE 0

                           BUFFER_POOL DEFAULT )

                LOGGING

                NOCOMPRESS

                NOCACHE;

                NOTE: The Automatic Segment Space Tablespace Management (ASSM) is new in Oracle9i and is implemented by adding the

                SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and

                remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

Add a column to an existing table

ALTER TABLE EMP add (new_col varchar2(23));

Drop a column from an existing table

ALTER TABLE EMP drop column new_col2;    (drop one column)

ALTER TABLE EMP drop (new_col1, new_col2);   (drop many columns)

When you drop a table column the column space remains used inside the data blocks and you may want to reorganize the table (using the imp/exp utility) to reclaim the free spaced from the dropped table column.

Drop a table

            DROP TABLE EMP;

            DROP TABLE EMP cascade constraints;    (Deletes all foreign keys that reference the table to be dropped, then drops the table.)

            DROP TABLE EMP purge;   (10g: Normally, a table is moved into the recycle bin, if it is dropped. Using "purge" option the table is not

                                                        moved in recycle bin.)

Add an index to a table

                CREATE UNIQUE INDEX PK_EMP ON EMP  (EMPNO)

                TABLESPACE USERS

                PCTFREE 10

                INITRANS 2

                MAXTRANS 255

                STORAGE (

                           INITIAL 64K

                           MINEXTENTS 1

                           MAXEXTENTS 2147483645

                           PCTINCREASE 0

                           BUFFER_POOL DEFAULT);

            This command creates an unique index on "empno" column on the EMP table and the index data will be stored in USERS tablespace. 

More information about  this subject ( Managing Database Tables and Indexes ) you can get from  www.in-oracle.com