Favorite tabs for pl/sql developer

Locks

select l.*, o.owner object_owner, o.object_Name
, h.SID holder_sid
from sys.all_objects o, v$lock l, v$lock h
where l.sid = :sid
and o.object_id = l.id1
and l.ID1=h.ID1
and h.SID!=l.SID

Statistics

select names.name, stats.statistic#, stats.value
from v$sesstat stats, v$statname names
where stats.sid = :sid
and names.Statistic# = stats.Statistic#
and value>0
order by stats.statistic#

Events

select * from v$session_event
where SID=:SID

Waits

select * from v$session_wait
where sid=:SID

Longops

select * from v$session_longops
where sid=:SID and serial#=:SERIAL#

Transaction

select * from v$transaction
where ses_addr=:SADDR

Temp usage

select t.TABLESPACE, t.CONTENTS, t.SEGTYPE, sum(t.EXTENTS), sum(t.BLOCKS ) BLOCKS
from v$tempseg_usage t
where t.SESSION_ADDR =:SADDR
group by t.TABLESPACE, t.CONTENTS, t.SEGTYPE

I/O stats

select * from v$sess_io
where sid=:SID

Plan

select id,
level l,
lpad(' ', level * 2) || operation operation,
options,
name,
"input(MB)",
last_mem,
opt_mem,
onepass_mem,
"sort O/1/M",
temp_space "TEMP space (MB)",
COST,
CPU_COST,
CARDINALITY,
BYTES,
IO_COST,
ACCESS_PREDICATES,
FILTER_PREDICATES
from (select p.ID,
p.PARENT_ID,
p.OPERATION,
options,
object_name name,
trunc(bytes / 1024 / 1024) "input(MB)",
trunc(last_memory_used / 1024) last_mem,
trunc(estimated_optimal_size / 1024) opt_mem,
trunc(estimated_onepass_size / 1024) onepass_mem,
decode(optimal_executions,
null,
null,
optimal_executions || '/' || onepass_executions || '/' ||
MULTIPASSES_EXECUTIONS) "sort O/1/M",
p.TEMP_SPACE / 1024 / 1024 temp_space,
p.COST,
p.CPU_COST,
p.CARDINALITY,
p.BYTES,
p.IO_COST,
p.ACCESS_PREDICATES,
p.FILTER_PREDICATES
from v$sql_plan p, v$sql_workarea w
where p.address = w.address(+)
and p.hash_value = w.hash_value(+)
and p.id = w.operation_id(+)
and p.address = :SQL_ADDRESS) i
start with i.id = 0
connect by prior i.id = i.parent_id

Access

select owner, object, type from v$access
where sid=:SID

Sessmetrics

select * from v$sessmetric
where session_id=:SID

Simpliest Goldan Gate replication

Require two users (GGSOURCE and GGDEST) on one DB

-->

Initial load

On the source:

ADD EXTRACT EINI01, SOURCEISTABLE

EXTRACT EINI01

USERID GGSOURCE, PASSWORD GGSOURCE

RMTHOST localhost, PORT 7809

RMTTASK REPLICAT, GROUP RINI01

TABLE GGSOURCE.OBJT;

On target:

ADD REPLICAT RINI01, SPECIALRUN

REPLICAT RINI01

ASSUMETARGETDEFS

OVERRIDEDUPS

INSERTAPPEND

BATCHSQL

USERID GGDEST, PASSWORD GGDEST

DISCARDFILE ./dirrpt/RINI01.DSC, PURGE

MAP GGSOURCE.OBJT, TARGET GGDEST.OBJT;

START EINI01

Simplest replication

On the source:

DBLOGIN USERID GGSOURCE, PASSWORD GGSOURCE

ADD TRANDATA owner.table

ADD EXTRACT EORA01, TRANLOG, BEGIN NOW, THREADS 1

ADD RMTTRAIL ./dirdat/st, EXTRACT EORA01

EXTRACT EORA01

USERID ggsource, PASSWORD ggsource

RMTHOST localhost, MGRPORT 7809

RMTTRAIL ./dirdat/st

TABLE GGSOURCE.OBJT;

On target:

EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE GGDEST.ggschkpt

Выйти из консоли и войти

DBLOGIN USERID GGDEST, PASSWORD GGDEST

ADD CHECKPOINTTABLE

ADD REPLICAT RORA01, EXTTRAIL ./dirdat/st

REPLICAT RORA01

USERID GGDEST, PASSWORD GGDEST

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA01.DSC, PURGE

MAP GGSOURCE.OBJT, TARGET GGDEST.OBJT;