Monday, October 11, 2010

ALTER, DROP, SET UNUSED, RENAME, TRUNCATE, BLOCK

ALTER:
------
1. alter table t300 add(age number);

2. alter table t300 modify(name varchar(200));
If table contains data and u want to change size to 2,it will fail since existing data is more than 20 characters.
If can play around by changing column size if that column is empty.

3. alter table t300 drop(age); // when u drop a column performace issue. it will use more resources.

4. SET UNUSED is just like DROP command. But one you sET UNUSED, no one can view that column. and you cannot bring back that coulmn.

alter table t300 SET UNUSED COLUMN salary;
alter table t300 DROP UNUSED columns;

DROP:
When you drop a table, automatically associated indexes also will be dropped.

RENAME:
RENAME dept TO detail_dept;

SQL> rename t300 to t301;

Table renamed.

TRUNCATE:

Difference between Delete and Truncate.
delete will not release BLOCK, but truncate will release BLOCK.

select table_name, blocks from dba_tables where table_name='T500' AND OWNER='USER8';
TABLE_NAME BLOCKS
__________________________
T500

exec dbms_stats.gather_table_stats('user8','T500');
select table_name, blocks from dba_tables where table_name='T500' and owner='user8';

No comments:

Post a Comment

 

©2010 Software Testing powered by Free Blogger Templates | Author : Anand Satish