Oracle DBA Blog

Modify oracle table structure with low impact

2011-07-13T12:31:00.002-04:00

--------------------------------------------------------------------------------
-- Goal: To modify a table structure with low impact
-- from DATE to TIMESTAMP assuming T1 table is a multi million row table
-- and T1 table is an -- highly available table.
--------------------------------------------------------------------------------

RMATTE@hhp3 SQL> drop table t1 purge;
Table dropped.

RMATTE@hhp3 SQL> create table t1 as select * from dba_objects;
Table created.

RMATTE@hhp3 SQL> create index t1_idx_01 on t1(object_id);
Index created.

RMATTE@hhp3 SQL> delete from t1 where object_id is NULL;
22 rows deleted.

RMATTE@hhp3 SQL> commit;
Commit complete.

RMATTE@hhp3 SQL> alter table t1 add constraint pk_t1_01 primary key(object_id);
Table altered.

RMATTE@hhp3 SQL> desc t1;
Name                                      Null?    Type
- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

--------------------------------------------------------------------------------
-- Create a interim table that matches your target table requirements.
-- You can specify partition option or different storage parameters.
--------------------------------------------------------------------------------
CREATE TABLE RMATTE.T1_INTERIM
(
OWNER           VARCHAR2(30 BYTE),
OBJECT_NAME     VARCHAR2(128 BYTE),
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID       NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE     VARCHAR2(19 BYTE),
CREATED         TIMESTAMP(6),
LAST_DDL_TIME   TIMESTAMP(6),
TIMESTAMP       VARCHAR2(19 BYTE),
STATUS          VARCHAR2(7 BYTE),
TEMPORARY       VARCHAR2(1 BYTE),
GENERATED       VARCHAR2(1 BYTE),
SECONDARY       VARCHAR2(1 BYTE)
)
/

--------------------------------------------------------------------------------
-- Check whether you can redefine your table or not?
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('RMATTE', 'T1', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
-- This function is required because to_timestamp parameter is not compatible with dbms_redifinition package.
--------------------------------------------------------------------------------
create or replace function my_to_timestamp(p_date date) return timestamp is
begin
return to_timestamp(p_date);
end;
/

Function created.

--------------------------------------------------------------------------------
-- Start the redefinition process. It will move all records from source table to interim table(target table).
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> BEGIN
2 dbms_redefinition.start_redef_table('RMATTE', 'T1','T1_INTERIM',
3   'OWNER,
4   OBJECT_NAME,
5   SUBOBJECT_NAME,
6   OBJECT_ID,
7   DATA_OBJECT_ID,
8 OBJECT_TYPE,
9   my_to_timestamp(CREATED) CREATED,
10   11   my_to_timestamp(LAST_DDL_TIME) LAST_DDL_TIME,
12   13   TIMESTAMP,
14   STATUS,
15   TEMPORARY,
16   GENERATED,
17   SECONDARY',DBMS_REDEFINITION.CONS_USE_PK);
18 END;
19 /

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
-- Move all dependent objects from source table to interim table (Target table).
-- It will create the depended objects with TMP names.
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> DECLARE
2   RETVAL NUMBER(5);
3 BEGIN
4   dbms_redefinition.copy_table_dependents('RMATTE', 'T1', 'T1_INTERIM', copy_indexes=>dbms_redefinition.cons_orig_params, copy_triggers=>TRUE, copy_constraints=>TRUE,copy_privileges=>TRUE, num_errors=>RETVAL);
5   dbms_output.put_line(RETVAL);
6 END;
7 /

0
PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
-- Complete the redefinition process.
-- It flips the sources table name/dependent objects with interim tablename/dependent objects (target table).
--------------------------------------------------------------------------------
RMATTE@hhp3 SQL> EXEC dbms_redefinition.finish_redef_table('RMATTE', 'T1', 'T1_INTERIM');

PL/SQL procedure successfully completed.

RMATTE@hhp3 SQL> DESC T1;
Name                                      Null?    Type
- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            TIMESTAMP(6)
LAST_DDL_TIME                                      TIMESTAMP(6)
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

RMATTE@hhp3 SQL> DESC T1_INTERIM;
Name                                      Null?    Type
- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

RMATTE@hhp3 SQL> DROP TABLE T1_INTERIM;
Table dropped.

SQL loader using EXTERNAL_TABLE option (ravinder_matte.postit@blogger.com)

2011-03-22T12:08:00.001-04:00

SQL loader testing

PERFORMANCE BENEFITS USING EXTERNAL_TABLE=EXECUTE/GENERATE_ONLY OPTION IN SQL LOADER

-- PREPARATION FOR TEST START HERE --

spool Million_Rec_File.txt

begin

for i in 1..1000000

loop

dbms_output.put_line(round(dbms_random.value(1,1000))||chr(9)||dbms_random.string('U',10)||chr(9)||round(dbms_random.value(10000,12000))||chr(9)||dbms_random.string('X',20));

end loop;

end;

spool off;

create table t1 (no number,ename varchar2(20), sal number, address varchar2(30));

alter table t1 parallel 16;

oracle@bond[ORA10GR2] /export/home/oracle/>cat Million_Rec_File.ctl

load data

infile '/export/home/oracle//Million_Rec_File.txt'

into table t1

fields terminated by "|" optionally enclosed by '"'

( no, ename, sal, address)

-- PREPARATION FOR TEST ENDS HERE --

-- TESTING START HERE --

Case - 1:

RMATTE@ORA10GR2 SQL> truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>

time sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl silent=all

real 0m16.664s

user 0m2.297s

sys 0m1.208s

Case - 2:

RMATTE@ORA10GR2 SQL> truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>

time sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl silent=all rows=1000

real 0m9.349s

user 0m2.100s

sys 0m0.547s

RMATTE@ORA10GR2 SQL> select count(*) from t1;

COUNT(*)

----------

1000000

Case - 3:

RMATTE@ORA10GR2 SQL> truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>

time sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl silent=all parallel=true rows=1000

real 0m8.162s

user 0m2.116s

sys 0m0.603s

RMATTE@ORA10GR2 SQL> select count(*) from t1;

COUNT(*)

----------

1000000

Case - 4:

RMATTE@ORA10GR2 SQL> truncate table t1;

Table truncated.

RMATTE@ORA10GR2 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

oracle@bond[ORA10GR2] /export/home/oracle/>time

sqlldr rmatte/<password>@ORA10GR2 control=Million_Rec_File.ctl external_table=execute

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Mar 21 14:19:38 2011

real 0m2.499s

user 0m0.029s

sys 0m0.034s

RMATTE@ORA10GR2 SQL> select count(*) from t1;

COUNT(*)

----------

1000000

-- TESTING ENDS HERE --

OBSERVATIONS:

Convention SQL Loader

Conventional SQL Loader with Rows=1000

Conventional SQL Loader with rows=1000 and parallel=TRUE

SQL Loader with external table

Improvements using external_table=execute option.

real 0m16.664s

user 0m2.297s

sys 0m1.208s

real 0m9.349s

user 0m2.100s

sys 0m0.547s

real 0m8.162s

user 0m2.116s

sys 0m0.603s

real 0m2.499s

user 0m0.029s

sys 0m0.034s

~ >3.26 times faster.

Google Docs makes it easy to create, store and share online documents, spreadsheets and presentations.

Disable / Enable all constraints and trigger for a given schema.

2011-02-25T19:19:00.000-05:00

Following 2 procedures will allow you to enable/disable all constraints and triggers for a given schema.

Usage:
EXEC DISABLE_ALL_CONS_TRGS('RMATTE');
exec ENABLE_ALL_CONS_TRGS('RMATTE');

CREATE OR REPLACE PROCEDURE DISABLE_ALL_CONS_TRGS (V_OWNER in varchar2 )
IS
BEGIN
for i IN (select owner,table_name, constraint_name from dba_constraints where constraint_type ='R'and status = 'ENABLED' and owner=v_owner)
loop
dbms_output.put_line ('alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name||'.');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;

for i IN (select owner,table_name, constraint_name from dba_constraints where status = 'ENABLED' and owner=V_OWNER)
loop
dbms_output.put_line ('alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name||'.');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;

for i IN (select distinct owner,table_name from dba_triggers where status = 'ENABLED' and owner=V_OWNER)
loop
dbms_output.put_line('alter table ' ||i.owner||'.'||i.table_name|| ' disable ALL TRIGGERS ;');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' disable ALL TRIGGERS ';
end loop i;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

CREATE OR REPLACE PROCEDURE ENABLE_ALL_CONS_TRGS (V_OWNER in varchar2 )
IS
BEGIN

for i IN (select owner,table_name, constraint_name from dba_constraints where status = 'DISABLED' and owner=V_OWNER)
loop
DBMS_OUTPUT.PUT_LINE ('alter table ' ||i.owner||'.'||i.table_name|| ' enable novalidate constraint ' ||i.constraint_name||';');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' enable novalidate constraint ' ||i.constraint_name;
end loop i;

for i IN (select distinct owner,table_name from dba_triggers where status = 'DISABLED' and owner=V_OWNER)
loop
dbms_output.put_line ('alter table ' ||i.owner||'.'||i.table_name|| ' enable ALL TRIGGERS ;');
EXECUTE IMMEDIATE 'alter table ' ||i.owner||'.'||i.table_name|| ' enable ALL TRIGGERS ';
end loop i;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

Moving table statistics from one database to other database

2010-04-03T22:32:00.006-04:00

To avoid the load of gathering stats on production database, oracle providing a way to move the stats from one database to other database. Normally it is very useful when we want to gather stats on UAT/TEST database and move them to PROD database. See this following example to understand the procedure.

Gather stats on source(UAT/TEST) database:

SQL> exec dbms_stats.gather_table_stats(ownname=>'RMATTE', tabname=>'T1', method_opt => 'for all indexed columns size auto',cascade => TRUE);

Create a stats table:

SQL> exec dbms_stats.create_stat_table(ownname=>'RMATTE',stattab=>'STATS',tblspace=>'USERS');

Save the T1 tables stats into stats table:

SQL> exec dbms_stats.export_table_stats(ownname=>'RMATTE', tabname=>'T1', stattab=>'STATS', statown=>'RMATTE', statid=>'T1_NEWSTATS');

Move the STATS table from UAT/TEST database to PRODUCTION database using convenient way. (exp/imp, data pump, db link etc):

Save the old T1 stats to STATS table with T1_OLD_STATS id incase if you want to revert the stats to old stats:

SQL> exec dbms_stats.export_table_stats(ownname=>'RMATTE', tabname=>'T1', stattab=>'STATS', statown=>'RMATTE', statid=>'T1_OLDSTATS');

Apply the new stats on T1 table:

SQL> exec dbms_stats.import_table_stats(ownname=>'RMATTE', tabname=>'T1', statown=>'RMATTE', stattab=>'STATS', statid=>'T1_NEWSTATS', cascade=>TRUE);

If you want to rollback the stats to original stats just import the T1_OLDSTATS:

SQL> exec dbms_stats.import_table_stats(ownname=>'RMATTE', tabname=>'T1', statown=>'RMATTE', stattab=>'STATS', statid=>'T1_OLDSTATS', cascade=>TRUE);

Hope this simple example will helps you....

Copy all privileges from one role to other role

2009-10-22T15:10:00.009-04:00

Whenever you need to copy all the privileges (Roles, System privs, Object privs and Column prives) from one existing role to an new/existing role, this script is useful as an handful tool.

Don't forget to save the script in .sql format and run.

-- Script begin here --
-- CopyRole.sql
-- Takes 2 inputs (old role to copy from and new role name to copy to.
-- Version : 1.0
-- Tested on 10.2.0.3 Version.

-- Create the New Role
accept OldRoleName prompt'please enter old role:'
accept NewRoleNameprompt'please enter new role:'
declare
Role_Exist exception;
pragma_exception_init(Role_Exist, -1921);
begin
execute immediate 'create role &&NewRoleName ';
exception
when Role_Exist then
dbms_output.put_line ('&&NewRoleName is alredy exicted, so i am append the privileges now.. ');
end;
/

-- Append the privs to new role

declare
stmt varchar2(3000);
begin
-- Grant Roles...
for i in (select granted_role,admin_option from sys.dba_role_privs where grantee=upper('&&OldRoleName'))
loop
select decode(i.admin_option, 'YES', ' WITH ADMIN OPTION') into stmt from dual;
declare
invalid_priv exception;
pragma_exception_init(invalid_priv, -990);
begin
execute immediate 'grant '||i.granted_role||' to "'||upper('&&NewRoleName')||'"'|| stmt;
exception
when invalid_priv then
dbms_output.put_line (i.granted_role||' can not be granted because it might be deleted');
end;

end loop;

for i in (select privilege,admin_option from sys.dba_sys_privs where grantee=upper('&&OldRoleName'))
loop
select decode(i.admin_option, 'YES', ' WITH ADMIN OPTION') into stmt from dual;
declare
invalid_priv exception;
pragma_exception_init(invalid_priv, -990);
begin
execute immediate 'grant '||i.privilege||' to "'||upper('&&NewRoleName')||'"'|| stmt;
exception
when invalid_priv then
dbms_output.put_line (i.privilege||' can not be granted because it might be deleted');
end;
end loop;

for i in (select privilege,owner,table_name from sys.dba_tab_privs where grantee=upper('&&OldRoleName'))
loop
declare
invalid_priv exception;
pragma_exception_init(invalid_priv, -990);
begin
execute immediate 'grant '||i.privilege||' on '||i.owner||'.'||i.table_name||' to "'||upper('&&NewRoleName')||'"';
exception
when invalid_priv then
dbms_output.put_line (i.privilege||' can not be granted because it might be deleted');
end;
end loop;

for i in (select privilege,owner,table_name,column_name from sys.dba_col_privs where grantee=upper('&&OldRoleName'))
loop
declare
invalid_priv exception;
pragma_exception_init(invalid_priv, -990);
begin
execute immediate 'grant '||i.privilege||' on '||i.owner||'.'||i.table_name||'('||i.column_name||') to "'||upper('&&NewRoleName')||'"';
exception
when invalid_priv then
dbms_output.put_line (i.privilege||' can not be granted because it might be deleted');
end;
end loop;
end;
/

-- Script ends here --

Error while Transporting Automatic Workload Repository (AWR) Data

2008-11-28T16:29:00.009-05:00

. . imported "AWR_STAGE"."WRH$_WAITSTAT":"WRH$_WAITSTAT_MXDB_MXSN" 0 KB 0 rows

. . imported "AWR_STAGE"."WRH$_WAITSTAT":"WRH$_WAITST_312119078_435" 0 KB 0 rows

. . imported "AWR_STAGE"."WRM$_SNAP_ERROR" 0 KB 0 rowsProcessing object type

TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 15:08:23
Elapsed: 00:00:56.08
begin
*
ERROR at line 1:
ORA-20105: unable to move AWR data to SYS
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1760ORA-20107: not allowed to move AWR data for local dbid
ORA-06512: at line 3
Elapsed: 00:00:00.05...
Dropping AWR_STAGE userElapsed: 00:00:06.70
End of AWR Load

This is the error message when you are running $ORACLE_HOME/rdbms/admin/awrload.sql.
Purpose: To transfer the awr snapshots from one database to other database for various reasons.
Snashots are exported from source database successfully and this is the error message when trying to load the snapshots into an destination database.

If we open the awrload.sql file all the sql and pl/sql are executed without any error except this procedure.

begin
/* call PL/SQL routine to move the data into AWR */
dbms_swrf_internal.move_to_awr(schname => :schname);
end;
/

From the error message, it is definately an error while trying to move the snapshots from a temporary schema to same schema. (looks like it is a bug).

The "dbms_swrf_internal" package is wrapped package (Unable to see the contents in the package.). So It is a NO go for me.

But i will keep you posted if I find a solution. Please comment if you find a solution...

Thanks,
Ravi Matte

ORADEBUG - Excellent oracle tracing tool

2008-09-25T12:58:00.006-04:00

oradebug - oracle debugging/tracing tool

oracledebug is an un-documented debugging utility provided by oracle. You need to connect as sysdba before start using oradebug.

Please find some of the useful commands and examples below.

-- list of all command and its usages.
oradebug help

--find the process id you need to attach
select s.username,s.sid,p.spid
from v$session s, v$process p
where s.paddr=p.addr
and s.username='scott'
/
username sid spid
-------------------- ---------- ------------------------------------
scott 32 6580

--attach scott spid to oradebug utility

sys @ xe:> oradebug setospid 6580
oracle pid: 18, unix process pid: 6580, image: oracle@rmatte (tns v1-v3)

--hint
if you are doing system wide dumps rather than single process dumps then you can attach to a process something like this.
oradebug setmypid;

--get the list of dumps you can get.
oradebug dumplist

--where is my dumpfiles going to be?
oradebug tracefile_name

--how much could be the tracefile size?
the default maximum size will be 20m
you can override that restriction using
oradebug unlimit;

***********************************************************************************
example of system state dump.
***********************************************************************************

oradebug setmypid;

--dump systemstate
oradebug dump systemstate 10;
you will see a trace under udump directory.
--hints:
to flush any pending writes to tracefile use : oradebug flush;

--close tracing.
oradebug close_trace;

***********************************************************************************
example of tracing other sessions.
***********************************************************************************

select s.username,s.sid,p.spid
from v$session s, v$process p
where s.paddr=p.addr
and s.username='scott'
/

oradebug setospid ;

oradebug event 10053 trace name context forever, level 1;

.... start the commands from scott session.

sys @ xe:> oradebug tracefile_name;
/usr/lib/oracle/xe/app/oracle/admin/xe/udump/xe_ora_7567.trc

!vi /usr/lib/oracle/xe/app/oracle/admin/xe/udump/xe_ora_7567.trc --- see the trace file.

oradebug flush; --flush any pending writes to tracefile.
oradebug close_trace; --stop tracing.

***********************************************************************************

Got problem with 2rd party tools, needs to know what statements it is trying to execute internally... sometimes 3rd party tools will show the error messages leaving actual statements.

you can start tracing for 3rd party tools like:

oradebug event 942 trace name errorstock forever, level 3;
***********************************************************************************
how the events will be turned off;

oradebug event 10053 trace name context off;

oradebug event 842 trace name context off;

***********************************************************************************
session suspend and resume
***********************************************************************************
sys @ xe:> @pidbyuser
username (=all): scott

sid serial# spid osuser machine sql_addr
----- ------- ------ ------ ------ --
32 68 8352 oracle rmatte 00

sys @ xe:>
sys @ xe:>
sys @ xe:> oradebug setospid 8352
oracle pid: 21, unix process pid: 8352, image: oracle@rmatte (tns v1-v3)
sys @ xe:> oradebug suspend;
statement processed.
sys @ xe:>

from this time onwards, scott cannot execute any sql statement until his process resume like:
sys @ xe:> oradebug resume;
statement processed.

***********************************************************************************
hanganalyze -- metalink reference document 215858.1
***********************************************************************************
-- hanganalyze is new in oradebug. it is most powerfull tool which helps in a situations when the database instance/session hung.
hanganalyze is "not" a dump, it produces output in udump directory in human readable format.

Some guidelines to understand the output of hanganalyze:

chains: chains are formed by the processes competing for a resource. Cyclical or closed chains
are deadlocks and are considered true hangs. Open chains consist of processes waiting for a
resource held by a blocker. these are not true hangs and can be resolved by killing the holding
session and allowing the blocked processes to proceed. This can be useful when a process is
holding a latch, frequent example is the library latch, and other processes are waiting for the latch.
be careful, however, not to kill smon or pmon as that would usually bring down the entire
instance.

The format of each chain entry is the following:
chain 1 : :
these abbreviations have the following meanings:
cnode=cluster node, available as of 9i
sid = session id, the same as sid in v$session
sess_srno = serial number, the same as serial# in v$session.
ospid=operating system pid (process id)

nodes: nodes are sessions. the first line explains the session listing:
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
****
the meaning of the abbreviations is the same as for the chains.

state column in the nodes list is matters most of the time,
in_hang - sid is hanged.
leaf or leaf_new -this session is blocking others, probably this is on the tops of the open chain.
nleaf -this session is waiting on other sessions.
ign or ign_dump -session is idle.

***********************************************************************************

reference link : http://www.mladen-gogala.com/ oradebug.pdf
metalink note ids:29786.1, 118570.1, 1058210.6 ,105395.1 and 215858.1

Backup your oracle listener log

2008-08-27T17:53:00.014-04:00

For the production databases you will see the listener log grows rapidly. you may need to backup and delete the listener log. If you move the listener.log while the listener is up and running, listener will stop writing to log file. It won't create new log file like alert log file.

rmatte:(test):/ora/app/oracle> lsnrctl
lsnrctl> show log_directory
connecting to (description=(address=(protocol=tcp)(host=rmatte)(port=1521))) listener parameter "log_directory" set to /u01/app/oracle/product//network/log/
the command completed successfully
lsnrctl> show log_file
connecting to (description=(address=(protocol=tcp)(host=rmatte)(port=1521))) listener parameter "log_file" set to listener.log
the command completed successfully
lsnrctl> show log_status
connecting to (description=(address=(protocol=tcp)(host=rmatte)(port=1521))) listener parameter "log_status" set to on
the command completed successfully
lsnrctl> set log_status off
connecting to (description=(address=(protocol=tcp)(host=rmatte)(port=1521))) listener parameter "log_status" set to off
the command completed successfully

rmatte:(test):/u01/app/oracle> mv /u01/app/oracle/product//network/log/listener.log /u01/app/oracle/product//network/log/listener.log.backup

lsnrctl> set log_status on
connecting to (description=(address=(protocol=tcp)(host=rmatte)(port=1521))) listener parameter "log_status" set to on
the command completed successfully

Don't be panic if you move/rename the listener log without following the above procedure. You just need to set the log_status on.

NOTE: You will miss all the listener log entries from the time you set the log_status to off to the time you set to on.

Basic oracle scripts - 1

2008-08-22T17:55:00.018-04:00

Active sql:

set linesize 150

set pages 100

col sid_serial noprint

col firstload for a10 head 'firstload'

col username for a10 word_wrap

col sql_text for a35 wrap

col sid_serial# for a10

col process format a10

col sid_ser format a10
select s.sid','s.serial# sid_ser
,s.username
,s.sid'.'s.serial# sid_serial
,s.process
,a.rows_processed
,substr(a.first_load_time,12,5) firstload
,a.sql_text
from v$sql a,
v$session s
where a.address = s.sql_address
and s.username is not null
and a.users_executing > 0
order by users_executing,
first_load_time
/

Waiting sessions :

set heading on

set head on

set feedback on

set trimspool on

set trunc on

set linesize 220

column username format a20

column event format a23

column p1 format a20

column p2 format a15

column p3 format a15

column wait_time format 999 heading waittime

column state format a16

column sid format 999

column sec_in_wait format 999 heading secondsinwait

select sw.sid,
s.username,
sw.event, '('sw.p1')' as p1,

sw.p2text'('sw.p2')' as p2,

sw.p3text'('sw.p3')' as p3,

sw.wait_time, sw.state
from v$session s,
v$session_wait sw

where s.sid=sw.sid

and sw.event not like '%sql*net%'

and s.username is not null

order by sw.wait_time desc

Holding and Waitings sessions:

set truncate off

set linesize 200

select decode(request,0,'holder: ','waiter: ') sid sess,

id1,
id2,
lmode,
request,
type

from v$lock

where (id1, id2, type) in ( select id1, id2, type
from v$lock

where request>0)

order by id1,

request

Tables in your rollback/undo segments?

set echo off
set linesize 200
set pagesize 200
set wrap off
column os_login format a10 heading "oslogin"
column db_login format a10 heading "dblogin"
column sid_serial format a10 heading "sidserial"
column sql_hash_address format a10 heading "sqlhashaddress"
column schema format a10 heading "schemaname"
column object_name format a20 heading "objectname"
column object_type format a10 heading "objecttype"
#column rollback_segment format a5 heading "rbseg"
column rollback_segment format a10 heading "rbseg"
column sql_address format a5 heading "sqladdress"
column records_locked format 999,999,999 heading "recordslocked"
column command_type format a25 heading "commandtype"
column sid noprint

select distinct
a.os_user_name os_login
,a.oracle_username db_login
,e.sid
,e.sid','e.serial# sid_serial
,e.sql_hash_value sql_hash_value
,e.sql_address sql_address
,b.owner schema
,b.object_name object_name
,b.object_type object_type
,c.segment_name rollback_segment
,d.used_urec records_locked
,f.name command_type
from
v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
, audit_actions f
where
a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
and e.command = f.action

order by
records_locked,
e.sid
,c.segment_name;
set echo off

Top 10 resource intensive queires in oracle :

set lines 300
set pages 40
set heading on
clear columns
ttitle off
column hash_value heading "hash value"
column address heading "address"
column buffer_gets heading "buff gets" format 99,999,999,999
column disk_reads heading "disk reads" format 99,999,999,999
column executions heading "executions" format 999,999,999,999
column stext heading "sql text" format a70 wrapped
column pctbuf heading "buff readsper exec" format 99999.99
column pctdsk heading "disk readsper exec" format 99999.99
column parse_schema heading "parsing user" format a12 trunc
column sharable_mem heading "memory usage" format 999,999,999,999
column parse_user heading "parse user" format a12 trunc
column parse_calls heading "parse calls" format 999,999,999
column rows_processed heading "rows processed" format 9,999,999,999
column pct_exec heading "rows per execs"

spool top10sql.log
prompt top 10 by buffer gets

select * from
(select
c.username parse_user,
a.sql_text stext,
a.executions,
a.rows_processed,
a.buffer_gets,
(a.buffer_gets/a.executions) pctbuf
from v$sqlarea a,
dba_users b,
dba_users c
where (a.buffer_gets > 0 and a.executions > 100)
and c.username not in ('johnd','sdavis','sys','system')
and a.parsing_schema_id = b.user_id
and a.parsing_user_id = c.user_id
order by a.buffer_gets desc)
where rownum <= 10; prompt top 10 by disk reads select * from (select c.username parse_user, a.sql_text stext, a.executions, a.rows_processed, a.disk_reads, (a.disk_reads/a.executions) pctdsk from v$sqlarea a, dba_users b, dba_users c where a.disk_reads > 0 and a.executions > 1000
and a.parsing_schema_id = b.user_id
and c.username not in ('johnd','sdavis','sys','system')
and a.parsing_user_id = c.user_id
order by a.disk_reads desc)
where rownum <= 10; prompt top 10 by memory usage select * from (select c.username parse_user, a.sql_text stext, a.executions , a.rows_processed, a.sharable_mem from v$sqlarea a, dba_users b, dba_users c where a.sharable_mem > 0
and a.parsing_schema_id = b.user_id
and c.username not in ('johnd','sdavis','sys','system')
and a.parsing_user_id = c.user_id
order by a.sharable_mem desc)
where rownum <= 10; prompt top 10 by executions select * from (select c.username parse_user, a.sql_text stext, a.executions, a.rows_processed, a.rows_processed/a.executions pct_exec from v$sqlarea a, dba_users b, dba_users c where a.executions > 1000
and a.parsing_schema_id = b.user_id
and a.parsing_user_id = c.user_id
and c.username not in ('johnd','sdavis','sys','system')
order by a.executions desc)
where rownum <= 10; prompt top 10 by parse calls select * from (select c.username parse_user, a.sql_text stext, a.executions, a.rows_processed, a.parse_calls from v$sqlarea a, dba_users b, dba_users c where a.parse_calls > 100
and a.parsing_schema_id = b.user_id
and c.username not in ('johnd','sdavis','sys','system')
and a.parsing_user_id = c.user_id
order by a.parse_calls desc)
where rownum <= 10;

spool off

What is in the oracle buffer now?

set linesize 140

column c0

heading 'Owner' format a30

column c1 heading 'ObjectName' format a30

column c2 heading 'NumberofBuffers' format 999,999

column c3 heading 'Percentageof DataBuffer' format 999,999,999

select owner c0,

object_name c1,

count(1) c2,

(count(1)/(select count(*) from v$bh)) *100 c3

from dba_objects o,

v$bh bh

whereo.object_id = bh.objd

ando.owner not in ('SYS','SYSTEM')

group by owner,

object_name

order by count(1) desc

Create a user same as other user in oracle:

set echo off
set pages 0 feed off veri off lines 500
set timing off
accept oldname prompt "enter user to model new user to: "
accept newname prompt "enter new user name: "

-- create user...
select 'create user "&&newname" identified 'decode(password,'external',' externally',' by welcome2fxfn')
' default tablespace 'default_tablespace
' temporary tablespace 'temporary_tablespace' profile '
profile';'
from sys.dba_users
where username = upper('&&oldname');

-- grant roles...
select 'grant 'granted_role' to "&&newname"'
decode(admin_option, 'yes', ' with admin option')';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- grant system privs...
select 'grant 'privilege' to "&&newname"'
decode(admin_option, 'yes', ' with admin option')';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- grant table privs...
select 'grant 'privilege' on 'owner'.'table_name' to "&&newname;"'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- grant column privs...
select 'grant 'privilege' on 'owner'.'table_name
'('column_name') to "&&newname";'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- tablespace quotas...
select 'alter user "&&newname" quota '
decode(max_bytes, -1, 'unlimited', max_bytes)
' on 'tablespace_name';'
from sys.dba_ts_quotas
where username = upper('&&oldname');

-- set default role...
set serveroutput on
declare
defroles varchar2(4000);
atleast number;
begin
atleast := 0;
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'yes'
) loop
atleast := 1;
if length(defroles) > 0 then
defroles := defroles','c1.granted_role;
else
defroles := defrolesc1.granted_role;
end if;
end loop;
if(atleast = 1) then
dbms_output.put_line('alter user "&&newname" default role 'defroles';');
end if;
end;
/
select 'alter user "&&newname" password expire;'
from dba_users where username = upper('&&oldname')
and password not in ('external');
select ' ' from dba_users where rownum<5;>