NTILE is an analytic function. This function can be used to group the rows into different buckets depends on the requirement. Suppose you have huge number of rows or records and you wanted to segregate the records into 3 or 4 groups depends on the salary criteria, then you can use this function. NTILE(4) means the entire group of records will be divided into 4 and each record will assign the corresponding bucket number. Example is shown below.
Index Rebuild Failed With ORA-14456: cannot rebuild index on a temporary table
Problem Description:
Index rebuild failed with following error.
SQL> ALTER INDEX IDX_TEMP_EMP REBUILD ONLINE;
ALTER INDEX IDX_TEMP_EMP REBUILD ONLINE
*
ERROR at line 1:
ORA-14456: cannot rebuild index on a temporary table
Oracle 11gR2 NTH_VALUE Function Usage Syntax
Oracle 11gR2 has a new analytical function to find out the nth value in a window. Using this function you can find out the nth largest value from a set of values or by grouping some set of values.
{RESPECT | IGNORE} NULLS determine whether null values of measure_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.
Rename Command fails with ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations
Error Description
Table rename command fails with following error.
SQL> alter table scott.test rename to scott.tst;
alter table scott.test rename to scott.tst
*
ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations
Solution Description
Script to Find out SQL Text from Oracle 11g, 10g RAC Database Using SID, SPID
Many times DBAs asked to check the queries running from a particular session. If we have TOAD or SQL Developer kind of software, it is easy to pull it. Otherwise you can use the following script find out the sql text. SID (marked in red) number needs to be modified as per your requirement.
For RAC
set lines 120
set pages 200
Advantage of Caching / Pinning/ Keeping an Oracle Table in Buffer Pool
Oracle provides a feature to keep objects in the Buffer pool. This feature allows you to keep the frequently accessed table in memory itself which will provide the faster access of the table. The object will be kept under the KEEP pool and the purpose of the keep pool is to accommodate / cache the small objects like look up tables.
