Thursday, February 14, 2013

AWR Analysis

›
http://www.toadworld.com/Experts/GuyHarrisonsImprovingOraclePerformance/ResolvingOracleContention/tabid/257/Default.aspx http://www.dba-or...

Trace Connect Hang at startup

›
 http://www.oracleangels.com/2011/05/useful-tracing-commands-oradebug-oracle.html -- There is an option that is helpful when the databse i...

Migration using database link Performance

›
alter database noarchivelog; NOLOGGING APPEND noarchive log mode no redo LOGGING no append noarchive log mode redo generated NOLOGGING ...

Public synonym performance

›
How to reference a different schema without hard coding it Oracle resolves all names while parsing, and the query execution plan genera...

Top Buffer Gets

›
set serverout on size 1000000 declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select buffer_gets,sub...

Update on Large Table

›
http://jonathanlewis.wordpress.com/2008/07/28/big-update/ UPDATE is the heaviest DML operation and it will take a long time, regardless of...

CPU taking SQL

›
If you noticed that a session is using too much CPU, you can identify the actions performed by that session using top and Explain Plan. So...

synonym - after dba privileges user is not able to access the option. only schema owner can access

›
create or replace trigger logon_trg    after logon on database  begin     if user in ('A','B') then         execute imm...

Who is using database link

›
Select /*+ ORDERED */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN", substr(g.K2GTITID_ORA,1,35)...

partition in a partitioned table truncated by mistake

›
Import partition data A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump. How...

temporary tablespace usage

›
– Listing of temp segments.– SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, ...

remove two char from string

›
select substr(oowncode,1,length(oowncode)-2) from ldfibs where oowncode='25TAV014M';

Top 30 I/O Intensive SQL Statements Identification

›
-- Top 30 I/O Intensive SQL Statements Identification : top30iosql.sql set linesize 80 set pagesize 58 set heading on set serveroutput ...

Who called me

›
http://asktom.oracle.com/tkyte/who_called_me How Can I find out who called me or what my name is Many times we are asked "in a proc...

Find string into Database

›
create or replace procedure sp_ldsearchengine (val varchar2) is   v_old_table user_tab_columns.table_name%type;   v_where     Varchar2(...

UTL_FILE_DIR Checking

›
I found some sample code provided by Oracle for testing utl_file_dir Metalink Note 45327.1 gives some good procedures to test whether pl/...

Purge Queue

›
BEGIN   DBMS_SCHEDULER.create_job (     job_name        => 'purge_boqueue',     job_type        => 'PLSQL_BLOCK', ...

Job Analyze Temp tables

›
CREATE OR REPLACE PROCEDURE Analyzetemp AS BEGIN  FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME                   FROM   DBA_TABLES w...

Analyze Full

›
CREATE OR REPLACE PROCEDURE AnalyzeFull AS BEGIN  FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME                   FROM   DBA_TABLES) ...

User Creation With job

›
host mkdir c:\yep1314 spool c:\yep1314\yep1314.log --------------User Creation CREATE USER LDBO PROFILE "DEFAULT" IDENTIFIE...