How to Check and Modify Default Attributes of Tables and Indexes Partitions (def_tablespace_name) in Oracle Database

Hi,

I will explain How to Check and Modify Default Attributes of Tables and Indexes Partitions in Oracle Database in this post.

 

ORA-02327: cannot create index on expression with datatype LOB | Move Lob Objects to New Tablespace using dba_lobs and dba_lob_partitions views

 

ORA-14257: cannot move a partition which is a composite partition | Move Table, Partition, Subpartitions to New Tablespace

 

 

Check Default Attributes of Tables and Indexes Partitions

You need to modify default attributes for tables, tables partitions, index or index partitions  During the tables,tables partitions, index and index partitions moving to new tablespaces.

 

If you move the tables and indexes to new tablespace, then drop the old tablespace, you need to modify default attributes for tables, tables partitions, index or index partitions for the future partitions, or subpartitions, that are created.

You can check Default atributes of partitioned table as follows.

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_TABLES WHERE TABLE_NAME='TABLE_NAME';

 

You can check Default atributes of partitioned index as follows.

 

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES WHERE INDEX_NAME='INDEX_NAME';

 

You can check Default atributes of table as follows.

 

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

 

Modify Default Attributes of Tables and Indexes Partitions

then you can modify Default Attributes for a Table partitions as follows.

 

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

 

or you can Modify Default Attributes for a Index as follows.

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

 

or you can modify Default Attributes for a Index partitions :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE

 

 

You can generate script to modify all tables’ default attributes under old tablespace as follows.

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||'  MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;' FROM DBA_PART_TABLES WHERE DEF_TABLESPACE_NAME='OLD_TABLESPACE_NAME';

 

You can generate script to modify all indexes’ default attributes under old tablespace as follows.

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;' FROM DBA_PART_INDEXES WHERE DEF_TABLESPACE_NAME='OLD_TABLESPACE_NAME';

 

You can generate the modify script for indexes and other objects.

 

 

You can read the following post to learn more details about Tables and Indexes moving.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

How to Rebuild Unused Indexes or Move Another Tablespace in Oracle

 945 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!