How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes the default attributes require to be modified for future partitions, or sub-partitions, specially in the situation when tables and indexes are refreshed from different environment.

Modifying Default Attributes of a Table

SQL>alter table OWNER.TABLE_NAME modify default attributes 
    tablespace TABLESPACE_NAME;

Modifying Default Attributes of a Index

SQL>alter index OWNER.INDEX_NAME modify default attributes 
    tablespace TABLESPACE_NAME;

Modifying Default Attributes of Table Partitions

SQL>alter table OWNER.TABLE_NAME modify default attributes 
    for partition PARTITION_NAME tablespace TABLESPACE_NAME;

Modifying Default Attributes of Index Partitions

SQL>alter index OWNER.INDEX_NAME modify default attributes 
 for partition PARTITION_NAME tablespace TABLESPACE_NAME;

Check Default Attributes of Tables

SQL> desc dba_part_tables;
 Name                        Null?     Type
 -----------------------     --------  ---------------------------------------------------------------
 OWNER                                        VARCHAR2(30)
 TABLE_NAME                                   VARCHAR2(30)
 PARTITIONING_TYPE                            VARCHAR2(9)
 SUBPARTITIONING_TYPE                         VARCHAR2(9)
 PARTITION_COUNT                              NUMBER
 DEF_SUBPARTITION_COUNT                       NUMBER
 PARTITIONING_KEY_COUNT                       NUMBER
 SUBPARTITIONING_KEY_COUNT                    NUMBER
 STATUS                                       VARCHAR2(8)
 DEF_TABLESPACE_NAME                          VARCHAR2(30)
 DEF_PCT_FREE                                 NUMBER
 DEF_PCT_USED                                 NUMBER
 DEF_INI_TRANS                                NUMBER
 DEF_MAX_TRANS                                NUMBER
 DEF_INITIAL_EXTENT                           VARCHAR2(40)
 DEF_NEXT_EXTENT                              VARCHAR2(40)
 DEF_MIN_EXTENTS                              VARCHAR2(40)
 DEF_MAX_EXTENTS                              VARCHAR2(40)
 DEF_MAX_SIZE                                 VARCHAR2(40)
 DEF_PCT_INCREASE                             VARCHAR2(40)
 DEF_FREELISTS                                NUMBER
 DEF_FREELIST_GROUPS                          NUMBER
 DEF_LOGGING                                  VARCHAR2(7)
 DEF_COMPRESSION                              VARCHAR2(8)
 DEF_COMPRESS_FOR                             VARCHAR2(12)
 DEF_BUFFER_POOL                              VARCHAR2(7)
 DEF_FLASH_CACHE                              VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                         VARCHAR2(7)
 REF_PTN_CONSTRAINT_NAME                      VARCHAR2(30)
 INTERVAL                                     VARCHAR2(1000)
 IS_NESTED                                    VARCHAR2(3)
 DEF_SEGMENT_CREATION                         VARCHAR2(4)

Check Default Attributes of Indexes

SQL> desc dba_part_indexes
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(30)
 INDEX_NAME                    NOT NULL VARCHAR2(30)
 TABLE_NAME                    NOT NULL VARCHAR2(30)
 PARTITIONING_TYPE                      VARCHAR2(9)
 SUBPARTITIONING_TYPE                   VARCHAR2(9)
 PARTITION_COUNT               NOT NULL NUMBER
 DEF_SUBPARTITION_COUNT                 NUMBER
 PARTITIONING_KEY_COUNT        NOT NULL NUMBER
 SUBPARTITIONING_KEY_COUNT              NUMBER
 LOCALITY                               VARCHAR2(6)
 ALIGNMENT                              VARCHAR2(12)
 DEF_TABLESPACE_NAME                    VARCHAR2(30)
 DEF_PCT_FREE                  NOT NULL NUMBER
 DEF_INI_TRANS                 NOT NULL NUMBER
 DEF_MAX_TRANS                 NOT NULL NUMBER
 DEF_INITIAL_EXTENT                     VARCHAR2(40)
 DEF_NEXT_EXTENT                        VARCHAR2(40)
 DEF_MIN_EXTENTS                        VARCHAR2(40)
 DEF_MAX_EXTENTS                        VARCHAR2(40)
 DEF_MAX_SIZE                           VARCHAR2(40)
 DEF_PCT_INCREASE                       VARCHAR2(40)
 DEF_FREELISTS                 NOT NULL NUMBER
 DEF_FREELIST_GROUPS           NOT NULL NUMBER
 DEF_LOGGING                            VARCHAR2(7)
 DEF_BUFFER_POOL                        VARCHAR2(7)
 DEF_FLASH_CACHE                        VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                   VARCHAR2(7)
 DEF_PARAMETERS                         VARCHAR2(1000)
 INTERVAL                               VARCHAR2(1000)
Advertisements
This entry was posted in Partitioning and tagged , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.