Assorted Oracle Tips – MichaelHinds.com

Contents

Security Alerts

Check these regularly!

SQL*Loader

Create a control file tablename.ctl:

LOAD DATA APPEND INTO TABLE tablename FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (columns... )

Call this with:

sqlldr username/password control=tablename.ctl log=tablename.log data=inputfile.csv bad=tablename.bad

Import / Export

To export, make a file called epars.txt:

file=wp.dmp log=wp.log tables=(WP_ATTACHMENTS, WP_CHECKPOINTS, WP_HISTORICAL_SORT, WP_PRESENTATIONS, WP_SLIDES, WP_STYLES, WP_STYLE_IMAGES, WP_USER_ACCESS_TICKET)

then use this command to do it:

exp user/password parfile=epars.txt
To import, make a file called ipars.txt:
file=wp.dmp ignore=y fromuser=acs touser=acsquest log=imp.mjh tables=(WP_ATTACHMENTS, WP_CHECKPOINTS, WP_HISTORICAL_SORT, WP_PRESENTATIONS, WP_SLIDES, WP_STYLES, WP_STYLE_IMAGES, WP_USER_ACCESS_TICKET) show=y

then use this command to test it:

imp user/password parfile=ipars.txt

Take the show=y line out of the parameters file to execute

Jobs

select * from dba_jobs; select * from user_jobs; select * from dba_jobs_running;

SQL*Plus scripts

Note for me: There's other stuff in here - use FTP

Killing sessions

You can find what sessions are attached to a schema with:

select program,sid,serial# from v$session where schemaname = 'QUEST' AND AUDSID <> USERENV('sessionid');

The last line stops you selecting your current session. This returns something similar to: 

PROGRAM                                                 SID    SERIAL#
################################################ ########-- ########--
   ?  @qfs16 (TNS V1-V3)                                 13      20052

Kill the session with:

ALTER SYSTEM KILL SESSION '13,20052';

Also the following can help find locks:

rem ****************************** rem locks.sql rem Created by: Debra Dudek rem This program is used to show rem both 1) users waiting for rem locked resources rem and 2) the users who are rem locking those who are waiting rem ****************************** set linesize 80 set verify off column username format a10 column id1 format a10 column sql format a70 word_wrap column object_owner format a15 column object format a20 break on sid skip 2 rem This script shows all users rem who are waiting for locked rem resources SELECT b.username, b.serial#, c.sid, c.owner object_owner, c.object, d.id1, a.sql_text SQL FROM v$sqltext a,v$session b, v$access c, v$lock d WHERE a.address = b.sql_address AND a.hash_value = b.sql_hash_value AND b.sid = c.sid AND b.lockwait = d.kaddr AND c.owner NOT IN ('SYS','SYSTEM'); rem This script shows the users rem who are locking the above rem waiting resources. rem This script will also show the rem SQL being executed by the rem user. NOTE: If the locking rem user is no longer executing rem any SQL, the rows returned rem will be 0. The user, however, rem is still locking the above rem users since he/she has not rem yet committed his/her rem transaction -- maybe they are rem away from their desk?? rem You should call them and ask rem them to either commit or rem rollback. SELECT x.serial#, x.sid, x.username, y.id1, z.sql_text SQL FROM v$session x, v$lock y, v$sqltext z WHERE y.id1 IN (SELECT distinct b.id1 FROM v$session a, v$lock b WHERE b.kaddr = a.lockwait) AND x.sid = y.sid AND z.hash_value = x.sql_hash_value AND y.request = 0; rem This script is identical to rem the above script EXCEPT that rem it does not return the SQL rem text being executed by the rem locking user. You can use it rem to quickly identify the rem locking user's serial#, sid rem and username if you get to a rem point where you will need to rem kill the user's session. SELECT x.serial#, x.sid, x.username, y.id1 FROM v$session x, v$lock y WHERE y.id1 IN (SELECT distinct b.id1 FROM v$session a, v$lock b WHERE b.kaddr = a.lockwait) AND x.sid = y.sid AND y.request = 0;

PL/SQL

Use sql%rowcount after DML to see how many rows were affected.

Tablespace reports

To list all tablespaces in a system, their datafiles and their memory status...

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE FROM sys.dba_free_space fs, sys.dba_data_files dd WHERE dd.tablespace_name = fs.tablespace_name GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024 ORDER BY dd.tablespace_name, dd.file_name;

...and to get the percentage free...

SELECT df.tablespace_name, SUM(df.bytes) TOTAL_SPACE, SUM(fs.bytes) FREE_SPACE, ROUND(((NVL(SUM(fs.bytes),0)/SUM(df.bytes))*100),2) PCT_FREE FROM dba_free_space fs, dba_data_files df WHERE df.tablespace_name = fs.tablespace_name (+) GROUP BY df.tablespace_name ORDER BY df.tablespace_name;

Extending a tablespace

Either increase the maximum size of it's datafiles:

alter database datafile '/data1/oracle/dev9r2/oradata/dev9r2/questaims.dbf' maxsize 2000M autoextend on;

Or add another datafile:

alter tablespace add datafile '/data1/oracle/dev9r2/oradata/dev9r2/questaims2.dbf' size 100M maxsize 2000M autoextend on;

Or increase the size of an existing datafile:

alter database datafile '/data1/oracle/dev9r2/oradata/dev9r2/questaims2.dbf' resize 50M;;

Autonomous transactions

Use this to do an autonomous transaction. Lets you commit a nested transaction without commiting the outer one. Useful for logging & debugging! For example:

create or replace procedure add_tmp(p_msg in varchar2) is pragma autonomous_transaction; begin insert into nb_tmp (x) values(p_msg); commit; end;

PL/SQL last compiled

select to_char(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS') from user_objects where object_name = upper('yourprocorwhatever');