Archive for the ‘Objects’ Category

Get DDL for a view or table

Posted: July 12, 2012 in Objects

How to generate the DLL for a table or a view from sqlplus, useful if you’ve not got a GUI tool.

So as the schema owner connect and run the following, in this example the table is called TEST_TABLE

set long 1000

select DBMS_METADATA.GET_DDL(‘TABLE’,’TEST_TABLE’) from dual;

output

 

view example

set long 1000

select DBMS_METADATA.GET_DDL(‘VIEW’,’TEST_VIEW’)from dual;

output

Shrink Database Objects

Posted: June 14, 2012 in Objects

In Oracle10g and onwards, we have the option to shrink a segment that helps to manage space more efficiently.  Shrink operations can be performed only on segments in locally managed tablespaces with ASSM.

The segment advisor will highlight any segments that can be shrunk and then you can do the following steps to shrink the object to free up space in the tablespace.

Example

Enable row movement for the table.

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL ENABLE ROW MOVEMENT;

Table altered.

Shrink table but don’t want to shrink HWM

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL SHRINK SPACE COMPACT;

Table altered.

Shrink table and HWM too

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL SHRINK SPACE;

Table altered.

Disable row movement for the table

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL DISABLE ROW MOVEMENT;

Table altered.

See oracle note: 242090.1 for more options and additional detail

Got this error after session was killed whilst doing a index rebuild and then trying to rebuild the index again, its seems the cause is – A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not – see metalink note: 375856.1 for more details

Error
TEST SQL> alter index TEST.TEST_DETAIL_1 rebuild online;
alter index TEST_TEST.AUDIT_DETAIL_1 rebuild online
*
ERROR at line 1:
ORA-08104: this index object 75192 is being online built or rebuilt

 

Fix the Issue

Run DBMS_REPAIR.ONLINE_INDEX_CLEAN to fix the issue so the index can be rebuilt

 

TEST SQL> declare
2 isclean boolean;
3 begin
4 isclean :=false;
5 while isclean=false
6 loop
7 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
8 dbms_lock.sleep(10);
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

 

Now Rebuild the Index
TEST SQL> alter index BOAUD_OWNER.AUDIT_DETAIL_1 rebuild online;

Index altered.

 

Finding table name for lob segment

Posted: April 12, 2012 in Objects

So i have a lob segment taking up lots of space so i need to find out the corresposding table for the lob.

SYSTEM@TEST> select count(*) from dba_lobs where SEGMENT_NAME=’SYS_LOB0000068405C00008$$’;

COUNT(*)
———-
1

Just need to query dba_lobs with the corresponding lob segment name.

SYSTEM@TEST> select OWNER,TABLE_NAME from dba_lobs where SEGMENT_NAME=’SYS_LOB0000068405C00008$$’;

OWNER                                   TABLE_NAME
—————————— ——————————
TEST                                         AUDIT_LOG