Purge BIPLATFORM Schema (S_NQ_ACCT) in OBIEE Database

Purge BIPLATFORM Schema in OBIEE:

Analysis:

In RCU Schemas of OBIEE, DEV_BIPLATFORM schema occupies decent amount of space if Usage Tracking is enabled. And out of which, LOBSEGMENT is the Object Type which would be consuming more space than the actual table itself (S_NQ_ACCT).

Action to be taken care:

There are two tables in BIPLATFORM Schema (S_NQ_ACCT and S_NQ_DB_ACCT), these two tables have a CLOB Datatype for QUERY_BLOB Column. And there will be 2 LOBSEGMENT Objects (with name SYS_LOB000xxxxxxx0000xx$$) in the Schema for the tables respectively (mentioned above).

To Shrink or Purge the Data, we need to purge the Data from S_NQ_ACCT and S_NQ_DB_ACCT tables to reclaim the space.

Reference Note id for purging the data from these 2 tables is “2000081.1”

==
begin
execute immediate ‘delete from dev_biplatform.s_nq_db_acct where start_dt < trunc(add_months(last_day(sysdate),-13)+1)’;
commit;

execute immediate ‘delete from dev_biplatform.s_nq_acct where start_dt < trunc(add_months(last_day(sysdate),-13)+1)’;
commit;

— Enable row_movement
execute immediate ‘alter table dev_biplatform.s_nq_db_acct enable row movement’;
execute immediate ‘alter table dev_biplatform.s_nq_acct enable row movement’;

— Shrink space tables and Indexes
execute immediate ‘alter table dev_biplatform.s_nq_db_acct shrink space cascade’;
execute immediate ‘alter table dev_biplatform.s_nq_acct shrink space cascade’;

— Shrink space LOB segments
execute immediate ‘alter table dev_biplatform.s_nq_db_acct modify lob (query_blob) (shrink space)’;
execute immediate ‘alter table dev_biplatform.s_nq_acct modify lob (query_blob) (shrink space)’;

end;
==

Reference on, how to get the link between LOBSEGMENT and Original table, you could follow the below link,

Finding table name for lob segment

 

Advertisements
This entry was posted in OBIEE, Oracle Database. 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s