Tables-Index-View

1-Rename Table
2-create Table on a special tablespace
3-Search Table with TableName
4-Show Index in each table
5-Drop Table in Recyclebin
6-Esstimate Table Size before create
7-Esstimate Index Size before create
8-Show table structure
9-create index in fast way
10-show last_analyzed in your object
11-run analyzed on your table
12-check index fragmentation
13-create index on a specific tablespace
14-create table then create index and create table-index then insert data
15-remove data from table , does it affect index size?
16-Shrink table and index space

---

1-rename Table

alter table
   customer
rename to
old_customer;

2-create Table on a special tablespace

create table temp002

tablespace TS_TRAINING

as

(select * from DSS_Report)

3-Search Table with TableName
select * from dba_tables
where Table_name like '%XX%';

 4-Show Index in each table
select index_name from user_indexes where table_name like 'CallDetail

5-Drop Table in Recyclebin
purge table "BIN$Lax+Rj3AD63gRAASeZ5J1Q==$0"

6-Esstimate Table Size before create

7-Esstimate Index Size before create

8-Show table structure
SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

9-create index in fast way

SQL> CREATE INDEX rname_idx

2  ON hr.regions (region_name)

3  PARALLEL 4; 10-show last_analyzed in your object select last_analyzed from dba _tables where table_name='xx' 11-run analyzed on your table analyze table test compute statistic  12-check index fragmentation --step 1 select last_analyzed from dba_tables where table_name='MSCMTCDR' select last_analyzed from dba_indexes where index_name='MSCMTCDR_ID1' --step 2 analyze table MSCMTCDR compute statistics analyze index MSCMTCDR_ID1 validate structure --step 3 select last_analyzed from dba_tables where table_name='MSCMTCDR' select last_analyzed from dba_indexes where index_name='MSCMTCDR_ID1' --step 4 select del_lf_rows,lf_rows, (del_lf_rows/lf_rows)*100 fragmentation,height from index_stats where name='MSCMTCDR_ID1' --step 5 alter index MSCMTCDR_ID1 rebuild; 13-create index on a specific tablespace create index index_01 on tableName(colName) tablespace tablespaceName 14-create table then create index and create table-index than insert data create table and then create index the process is fast, but if you create table and create index after insert data into index , it become very slow 15-remove data from table , does it affect index size? when you delete data from your table , the table size and index size is not affect, it remain the same as it before delete

16-Shrink table and index space

Alter table tab01 enable row movement;

Alter table tab01 shrink space;

Alter Index ind01 shrink space;