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...