Oracle 11gR2 NTILE Analytic Function Usage Syntax

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.

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.

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.

dba topics. Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google