tables and indexes

Creating Temporary Tables

SQL> CREATE GLOBAL TEMPORARY TABLE
hr.employees_temp
AS SELECT * FROM hr.employees ;
Altering Table
“Changing Storage And Block Utilization Parameters”

Some of the storage parameters and any of the block utilization parameters can be modified by using
the ALTER TABLE command.
SQL> ALTER TABLE hr.employees
PCTFREE 30
PCTUSED 50
STORAGE ( NEXT 500K MINEXTENTS 2 MAXEXTENTES 100 ) ;

“Manually Allocating Extents”

Use the following command to allocate an extent to a table.
SQL> ALTER TABLE hr.employees
ALLOCATE EXTENTS ( SIZE 500K
DATAFILE ‘ C:\ORACLE\ORADATA\ORCL\USER.DBF ’ );

“Nonpartitioned Table Reorganization”

SQL> ALTER TABLE hr.employees MOVE TABLESPACE data01;
“Dropping a Column”
SQL> ALTER TABLE hr.employees DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000 ;

In the example, checkpoint occurs every 1000 rows. The table is marked invalid until operation
completed.

“Renaming Column”
SQL> ALTER TABLE hr.employees
RENAME COLUMN hire_date TO start_date ;

“Mark Column as Unused”

SQL> ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS ;

“Drop Unused Column”

SQL> ALTER TABLE hr.employees
DROP UNUSED COLUMNS ;

Creating B-Tree Indexes

SQL> CREATE INDEX hr.employees_indx
ON hr.employees ( last_name )
PCTFREE 30
STORAGE ( INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE indx ;
Creating Bitmap Indexes
SQL> CREATE BITMAP INDEX orders_id_indx
ON orders ( region_id )
PCTFREE 30
STORAGE ( INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE indx ;
Altering Indexes

“Changing Storage Parameters”

SQL> ALTER INDEX employees_name_indx
STORAGE ( NEXT 400K MAXEXTENTS 100 ) ;

“Allocating Index Space”

SQL> ALTER INDEX employees_name_indx
ALLOCATE EXTENTS ( SIZE 200K
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\indx.dbf ’) ;

“Deallocating Index Space”

SQL> ALTER INDEX employees_name_indx
DEALLOCATE UNUSED ;

“Rebuilding Indexes”

SQL> ALTER INDEX employees_name_indx REBUILT ;

“Rebuilding Indexes Online”

SQL> ALTER INDEX employees_name_indx REBUILT ONLINE ;

“Coalesing Indexes”

SQL> ALTER INDEX employees_name_indx COALESCE ;

“Checking Index Validity”

SQL> ALTER INDEX employees_name_indx VALIDATE STRUCTURE ;

“Start Usage Monitoring on Indexes”

SQL> ALTER INDEX employees_name_indx MONITORING USAGE ;
“Stop Usage Monitoring on Indexes”
SQL> ALTER INDEX employees_name_indx NOMONITORING USAGE ;
“Dropping Indexes”
SQL> DROP INDEX index_name ;