Oracle OpenWorld Blog
Stay in touch of everything from this year OpenWorld….
How does one count all tables in a schema
set serveroutput on
declare
row_cnt number;
BEGIN
for i in (select table_name from user_tables) loop
execute immediate ‘select count(*) from ‘|| i.table_name into row_cnt;
dbms_output.put_line(‘Table sum for ‘||i.table_name||’ is ‘ ||row_cnt);
end loop;
END;
/
How does one find Oracle timestamp from current SCN
Filed under: Backup & Recovery, Datatypes, General DBA, RMAN
Here is a quick way to find the current timestamp from the current SCN.
SQL> select to_char(CURRENT_SCN) from v$database;
TO_CHAR(CURRENT_SCN)
—————————————-
48133107062
SQL> select scn_to_timestamp(48133107062) from dual;
SCN_TO_TIMESTAMP(48133107062)
—————————————————————————
17-MAY-10 08.41.47.000000000 AM
SQL>
How to check/find session information (running sql, wait, io, lock, time)?
Find SQL currently executing in a session:
http://oraclespin.wordpress.com/2010/05/09/how-to-find-sql-running-for-a-usersid/
Session running waiting on
SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247;
EVENT STATE
P1 P2 P3 SECONDS_IN_WAIT
—————————————————————- —————
—- ———- ———- ———- ——————-
db file scattered read WAITED KNOWN TIME
122 1188369 128 30
1 row selected.
SYS@TESTDB> SELECT * FROM v$event_name WHERE name = ‘db file scattered read’;
EVENT# EVENT_ID NAME
PARAMETER1 PARAMETE
R2 PARAMETER3
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
———- ———- ———————————————————-
—— —————————————————————- ——–
——————————————————– ———————–
—————————————– ————- ———– ————
—————————————————-
117 506183215 db file scattered read
file# block#
blocks
1740759767 8 User I/O
So Parameter #1: file#=122, Parameter #2: block#=1188369, Parameter #3: blocks=128
IO information consistents gets – reads from db buffers, physical reads – Physical reads, block changes – block changes
SQL> select * from V$sess_io where sid = 247;
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
———- ———- ————— ————– ————- ——————
247 6 1355 25 4 0
Detail user session information:
SQL> select vsn.name, vst.value
from v$sesstat vst, v$statname vsn
where vsn.statistic# = vst.statistic#
and vst.value 0
and vst.sid = 247
order by vst.value;
NAME VALUE
—————————————————————- ————-
logons cumulative 1
sorts (memory) 1
logons current 1
enqueue releases 1
enqueue requests 1
…
Find current locks by session:
– ID1 – is the object id the locks is on
– CTIME – Time elapsed since the object was locked in seconds
SQL> SELECT * FROM V$lock where SID = 247;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
000000006934A5B0 000000006934A5D8 247 TM 13828 0 3 0 72989 0
0000000069376700 0000000069376888 24 TX 65539 2262 6 0 72989 0
List session time for various operations
Using V$SESS_TIME_MODEL it displays the session time for various operations. The time reported is the total elapsed or CPU time (in microseconds).
Example:
SQL> select stat_name, value
from V$SESS_TIME_MODEL
where sid = 247
order by value desc;
STAT_NAME VALUE
—————————————————————- ———-
DB time 111174
DB CPU 107996
parse time elapsed 90664
hard parse elapsed time 64358
connection management call elapsed time 53179
sql execute elapsed time 32974
failed parse elapsed time 963
repeated bind elapsed time 148
background elapsed time 0
RMAN cpu time (backup/restore) 0
…
How to view and change STATS retention?
Way of changing retention
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
31
– value passed is in days
– If 0 is passed – old statistics are never saved. The automatic purge will delete all statistics history
– If 1 is passed – statistics history is never purged by automatic purge.
SQL> exec dbms_stats.alter_stats_history_retention(30);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
30
How to find oldest STATS information?
Using dbms_stats.get_stats_history_availability one can find date of oldest STATS information. STATS older than this timestamp cannot be restored.
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
—————————————————————————
09-FEB-10 10.13.18.954562000 PM -07:00
How to find space used by AWR?
Space used by AWR
SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name like ‘%AWR%’;
OCCUPANT_DESC SPACE_USAGE_KBYTES
—————————————————————- ——————
Server Manageability – Automatic Workload Repository 233152
How does one disable Automated Maintenance Tasks
BEGIN
dbms_auto_task_admin.disable(client_name => ‘auto optimizer stats collection’, operation => NULL, window_name => NULL);
dbms_auto_task_admin.disable(client_name => ‘auto space advisor’, operation => NULL, window_name => NULL);
dbms_auto_task_admin.disable(client_name => ‘sql tuning advisor’, operation => NULL, window_name => NULL);
END;
RECYLE BIN
In 10g oracle has introduced the concept of recycle bin one can recover tables that were dropped. When a table is dropped, the column “DROPPED” is marked “YES” in the view user_tables/all/dba_tables.
USER_RECYCLEBIN – This view has dropped objects in current user’s schema
Clean current user’s recycle bin
SQL> purge recyclebin;
Recyclebin purged.
DBA_RECYCLEBIN – Has dropped objects in all users
# purge all tables in the recycle bin
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
dba_scheduler_running_jobs
Using DBA_SCHEDULER_RUNNING_JOBS one can see jobs currently running. In the example below it shows the oracle session id and corresponding OS process id.
22:20:51 sys@TESTDB> select job_name, session_id from dba_scheduler_running_jobs;
JOB_NAME SESSION_ID
—————————— ———-
GATHER_STATS_JOB 364
1 row selected.
sys@TESTDB> select program from v$session where sid = 364;
PROGRAM
————————————————
oracle@hostname (J002)
1 row selected.
sys@TESTDB> select vs.program, spid from v$session vs, v$process vp where vs.sid = 364 and vs.paddr = vp.addr
PROGRAM SPID
———————————————— ————
oracle@hostname (J002) 1642698
1 row selected.
Oracle j000 shadow processes are DBMS_SCHEDULER sessions
sys@TESTDB> !ps -ef | grep 1642698
oracle 1642698 1 120 00:00:07 – 1071:14 ora_j002_TESTDB
oracle 13836458 11677872 2 22:23:06 pts/5 0:00 grep 1642698
