Feeds:
Posts
Comments

Posts Tagged ‘table’

Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.

SQL> create index tab1_I on tab1(a,b);
Index created.

SQL> alter index tab1_I rebuild online;
alter index tab1_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Let’s now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.

SQL> create index tab3_I on tab3(a,b);
Index created.

Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.

Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.

SQL> alter table tab3 modify b varchar2(200);
Table altered.

SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.

Expert are always welcome for their valuable comment or suggestion for the above post.

 

Read Full Post »

Recover dropped table in Oracle 10g using flashback feature

STEP 1 : WE ARE GOING TO DROP TABLE TABLE2.
SQL> desc table2;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER(3)

SQL> select * from table2;

ID
———-
2
2
3
4
4
5

6 rows selected.

STEP 2 : NOW DROP THE TABLE.

SQL> drop table table2;

Table dropped.

SQL> select * from table2;
select * from table2
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now We are confirmed that table has been dropped.

STEP 2 : CHECK WHEATHER THE TABLE IS AVAILABLE IN RECYCLEBIN.

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
—————————— ——————————–
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 TABLE2

STEP 2 : USE THIS FLASHBACK COMMAND TO RECOVER THE DROPPED TABLE.

SQL> flashback table table2 to before drop;

Flashback complete.

SQL> select * from table2;

ID
———-
2
2
3
4
4
5

6 rows selected.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> exit

Recover dropped table in Oracle 9i

We required incomplete recovery

1.take backup of ur current db
2. apply previous day backup (When the table was dropped)
3. do point in time recovery to get table back
4. export table
5. shutdown and apply latest backup (done 2day)
6. import table back

 Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Table fragmentation – when?

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables; — Create a table

Table created.

SQL> analyze table test compute statistics; — Analyze it

Table analyzed.

SQL> select blocks “Ever Used”, empty_blocks “Never Used”, num_rows “Total rows”
2 from user_tables where table_name=’TEST’; — The number of blocks used/free

Ever Used Never Used Total rows
———- ———- ———-
49 6 1680

SQL> delete from test where owner=’SYS’; — Im deleting almost half the number of rows.

764 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics; — Analyze it again

Table analyzed.

SQL> select blocks “Ever Used”, empty_blocks “Never Used”, num_rows “Total rows”

2 from user_tables where table_name=’TEST’; — No difference in blocks usage

Ever Used Never Used Total rows
———- ———- ———-
49 6 916

PL/SQL procedure successfully completed.

Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.

Reasons to reorganization

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to reorganize?

Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table
a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).

Oracle 10g provides us a new way of reorganizing the data.

Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;

Table altered.

There are 2 ways of using this command.

1. Break in two parts: In first part rearrange rows and in second part reset the HWM.

Part 1: Rearrange (All DML’s can happen during this time)

SQL> alter table test shrink space compact;

Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table sa shrink space;

Table altered.

2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)

Table altered.

Few advantages over the conventional methods

1. Unlike “alter table move ..”, indexes are not in UNUSABLE state. After shrink command, indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.

Conclusion

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

Read Full Post »