Page 2 of 212

DDL create commands for Objects (DBMS_METADATA)

Posted by Sagar Patil

Metadata API (DBMS_METADATA)
Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited:
* The SQL approach is limited in that as versions change, so must your scripts.
* Using export with ROWS=N and an import with SHOW=Y will produce the text to allow you to recreate a schema, but it can require considerable editing.
* The OCIDescribeAny interface is limited in the objects it supports.
To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as XML or SQL DDL: Read more…

Shell or UNIX OS Commands From PL/SQL

Posted by Sagar Patil

http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php Read more…

Identifying a Poor performing SQL

Posted by Sagar Patil

Our preferred v$sqlarea query is to actually report on physical disk I/O per statement execution. Hit ratios are informative but sometimes misleading. Logical I/O is less relevant. If the statement executes 1,000,000 logical I/Os but still only takes less than one-tenth of a second, who cares? It is the total physical I/O that consumes nearly all the time and identifies the potentially incorrect SQL. For example:

Read more…

How to find out hidden and system Parameters?

Posted by Sagar Patil

System Parameters Purpose: Reports all system parameters, including the hidden ones

Read more…

ORA 600 Corrupt UNDO/INDEX/TABLE Segment

Posted by Sagar Patil

I have seen this issue on number of SAP systems. I will compile a procedure I followed here.

Read more…

Estimate Undo Retention , Undo Optimization

Posted by Sagar Patil

undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level

Read more…

Complete (recursive) object dependency

Posted by Sagar Patil

The easiest approach is to use an Oracle utldtree.sql script. This will be located in the %ORACLE_HOME%\rdbms\admin\ directory.

Read more…

Find execution time for given SQL from Statpack tables

Posted by Sagar Patil

Please chnage “like” string for filtering results for a specific SQL statement.

set linesize 150;
set pagesize 1000;
BREAK ON hash_value SKIP 1;
spool 1_sql.lst;
select hash_value,to_char(snap_time,’DD-MON-YY HH24:MI:SS’),snap_id,piece, sql_text from STATS$SQLTEXT a, stats$snapshot b where hash_value in (
select hash_value
from STATS$SQLTEXT where sql_text like ‘SELECT%VBELN%KUNNR%ZZCURRENT_OWNER%VBKA%’)
and b.snap_id=a.last_snap_id
order by snap_time,hash_value,piece;
spool off;

Read more…

Resumable Space Allocation / SUSPEND RESUME transactions

Posted by Sagar Patil

Long running operations such as imports and batch processes sometimes fail because the server is unable to allocate more extents for an object. This may be because the object has reached max_extents or there isn’t sufficient room in the tablespace for the object to expand. In previous releases the operation would have to be rerun, possible with some manual cleanup necessary. In Oracle9i operations that would fail due to space allocation problems can be suspended and restarted once the problem is fixed.
* Resumable Mode
* Timeout Period
* AFTER SUSPEND Trigger
* Views
* DBMS_RESUMABLE Package
* Restrictions

Read more…

Running SHELL script from Oracle PL/SQL

Posted by Sagar Patil
exec dbms_java.grant_permission ('DBA_ADMIN', 'java.io.FilePermission','/usr/bin/ps', 'execute');
exec dbms_java.grant_permission ('DBA_ADMIN','java.lang.RuntimePermission','*','writeFileDescriptor' );

Read more…

What is a database EVENT and how does one set events?

Posted by Sagar Patil

Oracle trace events are useful for debugging the Oracle database server.

The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.

Read more…

Convert Oracle Enterprise Edition to Standard (DownGrade)

Posted by Sagar Patil

Oracle support referred me to Metalink Note 139642.1 to downgrade Enterprise Edition (EE) to the
Standard Edition (SE). They said we can not just downgrade the software, this will not get rid of EE.  I MUST export / import, in order to go from EE to SE.

Read more…

Speeding-Up Oracle Export/Import Migration

Posted by Sagar Patil

Step 1 – export
Step 2 – import data
Step 3 – import everything else

1.Assuming a full export, use direct=y if you’re not using any predicates in the export.
Set your buffer to be big (10MB at least)
2.Alter all your constraints novalidate prior to export if you can bring the app down, and if you can take a consistent export. This helps in reinstating the constraints instantly without forcing Oracle to validate constraints on massive tables
3. set workarea_size_policy=manual
4. Set sort_area_size=6GB (yes, 6 GB). Combined with 4, it let Oracle build indexes with more space in memory for all the sorts without the need to spill to disk.
5. Set massive online redo logs at 2GB each, 2 members each, 6 groups. After the import, reset everything back down to “normal”.

Read more…

Top of Page

Top menu