Month: March 2019

How to Find the Interval String of an Interval Partition Table

SQL> select TABLE_NAME, PARTITIONING_TYPE, INTERVAL 
     from dba_part_tables 
     where owner='TESTUSER' ;

TABLE_NAME    PARTITION_TYPE    INTERVAL
------------- ----------------- -----------------------------
TABLE1        RANGE             NUMTODSINTERVAL(1, 'DAY' )
TABLE2        RANGE             NUMTODSINTERVAL(1, 'MONTH' )
TABLE3        RANGE             NUMTODSINTERVAL(15, 'MINUTE' )
...
..
.
Advertisement

ORA-14758: Last partition in the range section cannot be dropped

Try to drop the last interval partition and get error “ORA-14758: Last partition in the range section cannot be dropped”.

SQL>alter table PART_TAB drop partition SYS_P3726162 update indexes;
alter table PART_TAB drop partition SYS_P3726162 update indexes
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

WORKAROUND

Disable interval partition definition, then drop the last partition and re-enable the interval partition again.

SQL> alter table PART_TAB set interval();

Table altered.


SQL> alter table PART_TAB drop partition SYS_P3726162 update indexes;

Table altered.


SQL>alter table PART_TAB set INTERVAL (NUMTODSINTERVAL(1,'DAY'));

Table altered.

How to Get Partition Column Name of a Partitioned Table in Oracle

SQL> desc dba_part_key_columns

Name             Null?    Type
---------------- -------- ----------------------
OWNER                     VARCHAR2(30)
NAME                      VARCHAR2(30)
OBJECT_TYPE               CHAR(5)
COLUMN_NAME               VARCHAR2(4000)
COLUMN_POSITION           NUMBER
SQL> select OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION 
     from   DBA_PART_KEY_COLUMNS 
     where  OWNER ='TESTUSER' 
     and    OBJECT_TYPE='TABLE';

How to Open ASM Trace File Starting With ‘+’

Oracle ASM trace file always starts with ‘+’, it is not working for below vi command to open and edit those kind of files:

$vi  +ASM1_ora_20007.trc

or

$vi  \+ASM1_ora_20007.trc

or

$vi  *ASM1_ora_20007.trc

or

$vi "+ASM1_ora_20007.trc"

What we do is to open those kind of files by :

1)  vi —

$ vi -- +ASM1_ora_20007.trc

OR

2) vi ./+ASM1_ora_20007.trc

$ vi ./+ASM1_ora_20007.trc

How to Exclude Tablespaces from RMAN Backup

You can use “CONFIGURE EXCLUDE FOR TABLESPACE” to exclude tablespaces from RMAN  backup command “BACKUP DATABASE”.

To exclude tablespace TEST_TBS from RMAN backup:

$ rman target / catalog rman/passwd@rman

RMAN>CONFIGURE EXCLUDE FOR TABLESPACE TEST_TBS;

Tablespace TEST_TBS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete


RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name RACTEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'TEST_TBS';

RMAN>

To exclude PDB tablespace TEST_TBS  from RMAN backup:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE RACTESTPDB:TEST_TBS;

Tablespace RACTESTPDB:TEST_TBS will be excluded from future whole 
database backups. 
new RMAN configuration parameters are successfully stored. 
starting full resync of recovery catalog
full resync complete

RMAN>show exclude ;
RMAN configuration parameters for database with db_unique_name RACTEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'TEST_TBS';
CONFIGURE EXCLUDE FOR TABLESPACE 'RACTESTPDB:TEST_TBS';

The following rman backup will excluded the above mentioned tablespaces:

RMAN> BACKUP DATABASE;

The excluded tablespace can be backed up by explicitly specifying them in a BACKUP command or by specifying the NOEXCLUDE option:

RMAN> BACKUP DATABASE NOEXCLUDE;

RMAN> BACKUP TABLESPACE TEST_TBS;

To disable the exclusion for RMAN backups:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE TEST_TBS CLEAR;

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE RACTESTPDB:TEST_TBS CLEAR;

RMAN> show exclude;