Oracle DBA Daily Experiences

Last updated: 17-Nov-2008

When exporting/importing one can minimize risk of losing data during import/export by setting NLS_LANG.

- Before starting export set NLS_LANG to be the same character set of the database being exported which means no conversion takes place, all the data will be stored in the export file as it was stored in the database.
- Before starting import set NLS_LANG to be the same value as the it was set during export which means no conversion will take place in the import session, but if the character set of the target database is different the data will automatically be converted when import inserts the data in the database.

OR

- Before starting export set NLS_LANG to be the same character set of the database being imported to which means conversion takes place at this step it will automatically convert during export.
- Before starting import set NLS_LANG to be the same value as the it was set during import which means no conversion will take place as it was already converted during export.

To find the NLS_LANG click on the following link

The following example shows how to find lock on a store procedure, the following store procedure has infinite loop.  A store-procedure is locked when it’s running that it can’t be modified/recompiled when it’s executing.

Window 1)
SQL> create or replace procedure p as
begin
while true
loop
null;
end loop;
end;
/

SQL> exec p;

Window 2)
– run after starting execution of store procedure p
– owner – owner of the lock
– session_id – session id of the lock
– name – object name
– type – object type
– mode_held – None (value when the store procedure is executing), Exclusive (value when store procedure is being compiled)
SQL> select * FROM dba_ddl_locks where name = ‘P’;

SESSION_ID OWNER NAME
———- —————————— ——————————
TYPE MODE_HELD MODE_REQU
—————————————- ——— ———
145 USER P
Table/Procedure/Type Null None

– note this will work when not logged in as SYS, as the session id is the same for all background processes
SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.audsid = userenv(‘sessionid’);

Or for SYS you can use the following
SQL> SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = (select sid from v$mystat where rownum = 1);

ORACLE_DEDIC CLIENTPID
———— ————
2301958 839726

1 row selected.

SQL> !ps -ef | grep 2301958
oracle 2301958 839726 0 15:18:04 – 0:00 oracleTEST (DESCRIPTION=(LOCAL=YES(ADDRESS=(PROTOCOL=beq)))
oracle 2441306 839726 0 15:18:13 pts/4 0:00 grep 2301958

SQL> !ps -ef | grep 839726
oracle 839726 2928804 0 15:18:02 pts/4 0:00 sqlplus
oracle 2441308 839726 0 15:18:19 pts/4 0:00 grep 839726

To make trace files visible to all users, set the parameter _TRACE_FILES_PUBLIC=TRUE, this is undocumented parameter. This parameter requires a restart of instance to take into effect.

View the following link to find if parameter can be modified without restart or requires a restart.

http://oraclespin.wordpress.com/2008/05/14/internal-parameters/

Using the following SQL you can monitor the progress of RMAN process, as channels completes creating the backpiece new sessions are created so overall progress can be monitored by looking at where context = 3 as in the case below it shows the overall progress is 38.05% complete.

– works in 8i/9i/10g.

SQL> SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) “% Complete”
FROM v$session_longops
WHERE totalwork 0
and upper(opname) like ‘%RMAN%’
and sofar totalwork;

The following 2 links explains transaction isolation.

http://www.acs.ilstu.edu/docs/Oracle/server.101/b10743/consist.htm

http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html

– Turn off display of execution plan and statistics
SET AUTOTRACE OFF

– show user query output, execution plan
SET AUTOTRACE ON EXPLAIN

– display output from query and statistics
SET AUTOTRACE ON STATISTICS

– show user query output, execution plan and statistics
SET AUTOTRACE ON

– set trace on but suppress output from query, display execution plan and statistics
SET AUTOTRACE TRACEONLY

To be able view the trace using the above option the user needs PLUSTRACE role assigned, the role is created by running the script which is ORACLE_HOME @?/sqlplus/admin/plustrce.sql to be run as SYS. If the PLUSTRACE role is not granted you will get the following error “SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled”

Each schema may require a PLAN_TABLE to debug performance so instead of creating the PLAN_TABLE within each schema, one can do the following:

– ? is the ORACLE_HOME
SQL> connect system
Password:
SQL> @?/rdbms/admin/utlxplan.sql
– create public synonym
SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
– Grant everyone access to PLAN_TABLE
SQL> GRANT ALL ON PLAN_TABLE TO PUBLIC;

To change default number of bytes to be displayed when selecting LONG and CLOB columns.
SQL> SET LONG 2000
SQL> SELECT long_column, clob_column FROM table_name;

If you need to test you monitoring solution, this can be handy to directly write to the archive log.

execute sys.dbms_system.ksdwrt(2,to_char(sysdate)|| ‘ 99 ‘);
execute sys.dbms_system.ksdwrt(2,(‘ORA-07445: Alexs OEM Monitoring Test’));