Natalka's Oracle DBA Toolkit

Home

Toolkit:

My OraFAQ Blog

Contact me

Quick Tips

How to close a database link:

Alter Session Close Database Link  or 
DBMS_SESSION.CLOSE_DATABASE_LINK (dblink VARCHAR2);

JDBC Connect String (Thin):

"jdbc:oracle:thin:@host:port:sid"

JDBC Connect String (Thin) with Load Balancing / Failover:
To enable load balancing or failover, stuff the entire (DESCRIPTION= ) from the tnsnames.ora into the "host" field of the JDBC connect string, like this:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=barberry.sphinx.edu)
(PORT=9987)) (ADDRESS=(PROTOCOL=TCP)(HOST=burberry.sphinx.edu)(PORT=9091))
(LOAD_BALANCE = YES)(FAILOVER=ON) (CONNECT_DATA=(SERVER=SHARED)(SERVICE_NAME=
WINKLE.WORLD)))
Warning: WebLogic says that (LOAD_BALANCE=YES) can cause problems. (FAILOVER=YES) should be fine.

Enable SQL statement tracing, including binds/waits:

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 
12';
or
dbms_support.start_trace(waits=>true, binds=>true);
/* code to be traced goes here */
dbms_support.stop_trace();

Sample MTS setup in init.ora (8i version):

mts_dispatchers="(address=(protocol=tcp)(host=141.247.114.245)(dispatchers=1)(conn=512)(port=2399))(serv=adm_trn3.world)"
mts_dispatchers="(address=(protocol=tcp)(host=141.247.114.245)(dispatchers=1)(conn=512)(port=2400))(serv=adm_trn3.world)"
mts_max_servers=20
mts_servers=3
mts_max_dispatchers=2
service_names=adm_trn3.world
instance_name=TRN3
local_listener="(address=(protocol=tcp)(host=141.247.114.245)(port=1521))"

Create PFILE from SPFILE, or vice versa (9i and up):

CREATE PFILE [= 'pfile_name'] FROM SPFILE [= 'spfile_name'];
CREATE SPFILE [= 'pfile_name'] FROM PFILE [= 'spfile_name'];

Move a LOB segment or its associated index:

ALTER TABLE REPCAT$_INSTANTIATION_DDL 
MOVE LOB (DDL_TEXT) STORE AS (TABLESPACE TOOLS);

Move a table partition:

ALTER TABLE LOGSTDBY$APPLY_PROGRESS 
MOVE PARTITION P0 TABLESPACE TOOLS;

Sample sql*plus COPY statement (Used to copy tables containing LONG fields):

copy from scott/tiger@mydb to scott/tiger@mydb - 
> append mytab2 - 
> using select * from mytab;

Simple Oracle Text (conText, interMedia Text) index:

CREATE INDEX foo_ind on foo ( bar ) INDEXTYPE IS CTXSYS.CONTEXT; 

Simple Oracle Text (conText, interMedia Text) query:

SELECT bar from foo WHERE CONTAINS ( bar, 'thing i want' ) > 0; 

Extract basic Oracle Text index ddl:

select 'create index ' || idx_owner || '.' || idx_name 
|| ' on ' || idx_table_owner || '.' || idx_table
|| ' ( ' || idx_text_name || ' ) ' || ' indextype is ctxsys.context;' 
from ctxsys.ctx_indexes;

Cursor variable:

declare
  type std_cur_t is ref cursor return my_table%rowtype;
  std_cur std_cur_t ;
  std_rec my_table%rowtype;
begin
  open std_cur for
   select * from my_table;
  ....
  close std_cur;
end;

Statspack report:

SQL> connect perfstat/******
SQL> @?\rdbms\admin\spreport

Purge old statspack snapshots:

 SQL> @?\rdbms\admin\sppurge.sql


Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...