DBMS_METADATA_DDL and Interval partitions

I am writing this post after a OTN question on DBMS_METADATA not showing the DDL after interval partitions are created and Jonathan lewis reason for this behavior.

create table test3
(
id number,
created_dt date
)
partition by range (created_dt)
interval (numtoyminterval(1, ‘month’))
(
partition p_feb2017 values less than (to_date(‘2017-03-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’, ‘nls_calendar=gregorian’)) ,
partition p_mar2017 values less than (to_date(‘2017-04-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’, ‘nls_calendar=gregorian’))
) ;
SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name =’TEST3′ order by partition_position;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE
——————– —————— ——————————————————————————–
P_FEB2017 1 TO_DATE(‘ 2017-03-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
P_MAR2017 2 TO_DATE(‘ 2017-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

Check the DBMS_METADATA output for the table.

SQL> select dbms_metadata.get_ddl(‘TABLE’,’TEST3′,’RAKESH’) from dual;
DBMS_METADATA.GET_DDL(‘TABLE’,’TEST3′,’RAKESH’)
————————————————————————–

CREATE TABLE “RAKESH”.”TEST3″
( “ID” NUMBER,
“CREATED_DT” DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”
PARTITION BY RANGE (“CREATED_DT”) INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH’))
(PARTITION “P_FEB2017” VALUES LESS THAN (TO_DATE(‘ 2017-03-01 00:00:00’, ‘SYYY
Y-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ,
PARTITION “P_MAR2017” VALUES LESS THAN (TO_DATE(‘ 2017-04-01 00:00:00’, ‘SYYYY
-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” )

Insert new data into the table.

SQL> insert into test3 values (1,to_date(‘8-MAY-2017’, ‘DD-MON-YYYY’));

1 row created.

SQL> commit;

Commit complete.

Interval partition created.

SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name =’TEST3′ order by partition_position;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE
——————– —————— ——————————————————————————–
P_FEB2017 1 TO_DATE(‘ 2017-03-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
N’)

P_MAR2017 2 TO_DATE(‘ 2017-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
N’)

SYS_P911 3 TO_DATE(‘ 2017-06-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
N’)

But the DDL from DBMS_METADATA still shows the partitions that were created when the table first created, doesn’t include the new partition created for MAY.

CREATE TABLE “RAKESH”.”TEST3″
( “ID” NUMBER,
“CREATED_DT” DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”
PARTITION BY RANGE (“CREATED_DT”) INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH’))
(PARTITION “P_FEB2017” VALUES LESS THAN (TO_DATE(‘ 2017-03-01 00:00:00’, ‘SYYY
Y-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ,
PARTITION “P_MAR2017” VALUES LESS THAN (TO_DATE(‘ 2017-04-01 00:00:00’, ‘SYYYY
-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCT FREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” )

Thanks to Jonathan Lewis , who put a comment on rationale behind DBMS_METADATA not showing the interval partition. I am quoting his words.

“There is a simple rationale for this behaviour.

Imagine the only out-of-range data you’ve added is for December – Oracle will add the december partition but will not add the partitions for april through to november.
If dbms_metadata.get_ddl now created the DDL to create partitions with the three boundaries that existed the code would create three RANGE partitions, which means the december partition would cover april to december and act as the lower bound for the interval partitions, and you wouldn’t get (e.g.) a dedicated june partition created if you then inserted some data for June”

 

 

Oracle 12c Internal Users

DBA_USERS in oracle 12c included a new column ORACLE_MAINTAINED which will show if the user is oracle maintained or No.

SQL> desc dba_users
Name Null? Type
———————————————– ——–
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)

SQL> select username,oracle_maintained,account_status from dba_users where oracle_maintained=’Y’ ;

USERNAME O ACCOUNT_STATUS
——————– – ——————————–
GSMCATUSER Y EXPIRED & LOCKED
SYSBACKUP Y EXPIRED & LOCKED
OUTLN Y OPEN
SYSDG Y EXPIRED & LOCKED
DBSNMP Y OPEN
ORACLE_OCM Y EXPIRED & LOCKED
SYSKM Y EXPIRED & LOCKED
XS$NULL Y EXPIRED & LOCKED
GSMUSER Y EXPIRED & LOCKED
AUDSYS Y EXPIRED & LOCKED
SYS Y OPEN
SYSTEM Y OPEN
DIP Y EXPIRED & LOCKED
ANONYMOUS Y LOCKED
XDB Y EXPIRED & LOCKED
APPQOSSYS Y EXPIRED & LOCKED
GSMADMIN_INTERNAL Y EXPIRED & LOCKED

17 rows selected.

Convert Admin managed to Policy Managed Database in RAC

I haven’t come across Policy Managed database in RAC till now, so testing out the coversion of Admin managed to PMD and viceversa.

Convert Admin Managed to Policy Managed Database:

Current Config Status of database

$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name: TESTCDB
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
Password file: +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO_GRP_2,DATA_GRP_1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: TESTCDB1,TESTCDB2
Configured nodes: VM1,VM2
Database is administrator managed

Server pool List:

$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 2

Add new Server pool TEST

$ srvctl add srvpool -serverpool TEST -min 0 -max 2
$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 2
Server pool name: TEST
Active servers count: 0

Modify TESTCDB to server pool TEST.

$ srvctl modify database -d TESTCDB -g TEST -force

$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name: TESTCDB
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
Password file: +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Disk Groups: REDO_GRP_2,DATA_GRP_1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances:
Configured nodes:
Database is policy managed

Convert Policy Managed Database to Admin Managed Database:

To convert the policy managed to admin managed, you have to remove it from cluster registry. To gracefully remove,stop the database and then remove. As it is test instance I am using “-Force” option.

$ srvctl remove database -d TESTCDB
PRKO-3141 : Database TESTCDB could not be removed because it was running
$ srvctl remove database -d TESTCDB -force

$ srvctl status database -d TESTCDB
PRCD-1120 : The resource for database TESTCDB could not be found.
PRCR-1001 : Resource ora.testcdb.db does not exist

Add database and instances to cluster again.

$ srvctl add database -d TESTCDB -o /u01/app/oracle/product/12.1.0.2/db_1 -y AUTOMATIC
$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances:
Configured nodes:
Database is administrator managed

Add Instances , spfile and password file to the registry

$ srvctl add instance -d TESTCDB -i TESTCDB1 -n VM1
$ srvctl add instance -d TESTCDB -i TESTCDB2 -n VM2
$ srvctl modify database -d TESTCDB -p +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
$ srvctl modify database -d TESTCDB -pwfile +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
$ srvctl config database -d TESTCDB
Database unique name: TESTCDB
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA_GRP_1/TESTCDB/PARAMETERFILE/spfile.315.928144379
Password file: +DATA_GRP_1/TESTCDB/PASSWORD/pwdtestcdb.300.928143681
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: TESTCDB1,TESTCDB2
Configured nodes: VM1,VM2
Database is administrator managed

Ref: https://docs.oracle.com/database/121/RACAD/admcon.htm#RACAD7282

AMM in Oracle 11g

Below to post is to verify my understanding on how shared memory is managed in AMM. Thanks to Tanel poder Post on AMM internals.

Current setting for MEMORY_TARGET parameters in DB, not all 50GB allocated

SQL> sho parameter memory

NAME TYPE VALUE
———————————— ——————————– —–
hi_shared_memory_address integer 0
memory_max_target big integer 50G
memory_target big integer 50G
shared_memory_address integer 0

SGA parameters set in DB

SQL> sho parameter sga

NAME TYPE VALUE
————————-
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 50G
sga_target big integer 20G

In AMM , oracle manages shared memory as files in shared memory filesystem (/dev/shm)

cd /dev/shm

$ ls -ltr *TEST* |more
-rw-r—– 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131235851_0
-rw-r—– 1 oracle dba 134217728 Apr 26 18:42 ora_TEST_131203082_369
Each files is of size 128M

ls -ltr *TEST* | awk ‘$5==134217728’ |wc -l
170

170*128M=21.25 GB

21.25GB shared memory currently allocated to database TEST, these are memory files mapped to server process (size 0 bytes), but its not used, if space needed for pga , these files are freed and memory allocated to pga.

$ ls -ltr *TEST* | awk ‘$5==0’ |more
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_230
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_229
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_228
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_227
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_226
-rw-r—– 1 oracle dba 0 Apr 26 18:42 ora_TEST_131203082_225

No of empty files which can freed whenever memory needs to be allocated to pga.

$ ls -ltr *TEST* | awk ‘$5==0’ |wc -l
231

231*128M=28.8GB  ( MEMORY_TARGET-Current SGA Size)

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
——————– ———— ———- ———- ——————- ———- ————- ——— ——— ————
SGA Target 2.2683E+10 2.2012E+10 2.2683E+10 2.1475E+10 5 GROW DEFERRED 02-MAY-16 134217728
SQL> select to_char(2.2683E+10)/1024/1024/1024 from dual;

TO_CHAR(22683000000)/1024/1024/1024
———————————–
21.12519

sga_target current size is 21Gb which matches to the size of all shared memory files created for the DB in /dev/shm.
If /dev/shm not defined or properly configured, then setting AMM at instance will throw error message ORA-00845: MEMORY_TARGET not supported on this system. ASMM can be configured without /dev/shm.

Ref: http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/

 

RMAN Block Recovery ,Skip Corrupted Block

 

Create table under tablespace test2 and check the block# for the first few rows.

SQL> create table oratab.objects2 tablespace test2 as select * from dba_objects;

Table created.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from oratab.objects2) where rownum < 7;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
131
132
133
134
135

SQL> select count(*) from oratab.objects2;

COUNT(*)
———-
18520

No corrupted blocks in the tablespace.

SQL> select * from v$database_block_corruption;

no rows selected

Manually corrupt one of the block containing data.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=133 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.0526e-05 s, 162 MB/s

Flush buffer cache as it may have all blocks currently in memory.

SQL> alter system flush buffer_cache;

System altered.

Query the table, it will through error message with the block# of corrupted block.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 133)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

The corrupted block details will be reflected in v$database_block_corruption

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 133 1 0 ALL ZERO

Recover block using RMAN

RMAN> blockrecover datafile 7 block 133;

Starting recover at 21-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Corrupt multiple blocks now.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=134 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.6443e-05 s, 145 MB/s
$ dd of=/u02/oradata/test201.dbf bs=8192 seek=135 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.2078e-05 s, 157 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 134)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

Use RMAN validate to check the corrupted block list.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE TEST2;

Starting validate at 21-APR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u02/oradata/test201.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 FAILED 0 12410 12800 30691648
File Name: /u02/oradata/test201.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 247
Index 0 0
Other 2 143

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 134 2 0 ALL ZERO
When there are multiple corrupted blocks, use blockrecover corruption list to recover them.

RMAN> blockrecover corruption list;

Starting recover at 21-APR-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=26 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.6 (2014102419)
using channel ORA_DISK_1

channel ORA_SBT_TAPE_1: restoring block(s)
channel ORA_SBT_TAPE_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_SBT_TAPE_1: reading from backup piece /apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1
channel ORA_SBT_TAPE_1: piece handle=/apps/oracle/product/11.2.0.4/dbs/jpr3ij34_1_1 tag=TAG20160421T051932
channel ORA_SBT_TAPE_1: restored block(s) from backup piece 1
channel ORA_SBT_TAPE_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 21-APR-16

SQL> select * from v$database_block_corruption;

no rows selected

Skipping Corrupted Block using event ‘10231’  or DBMS_REPAIR if it can’t be recovered.

$ dd of=/u02/oradata/test201.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.9682e-05 s, 165 MB/s

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from oratab.objects2;
select count(*) from oratab.objects2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: ‘/u02/oradata/test201.dbf’

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
7 131 1 0 ALL ZERO

SQL> ALTER SESSION SET EVENTS
‘10231 TRACE NAME CONTEXT FOREVER, LEVEL 10’; 2

Session altered.

SQL> create table oratab.objects2_recover tablespace test2 as select * from oratab.objects;

Table created.

SQL> SQL> select count(*) from oratab.objects2_recover;

COUNT(*)
———-
18518 — 2 rows decreased.

Corrupted block can be skipped using DBMS_REPAIR package as well

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′);

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME SKIP_CORRUPT
—————————— ——–
OBJECTS2 ENABLED

Skip corrupt flag can be cleared after exporting the table or using CTAS

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘ORATAB’,’OBJECTS2′,flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select table_name,SKIP_CORRUPT from dba_tables where table_name=’OBJECTS2′ and owner=’ORATAB’;

TABLE_NAME                               SKIP_CORRUPT
—————————— ——–
OBJECTS2                                             DISABLED

 

ref:Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 (Doc ID 33405.1)