Australia photo & PC memo

Thousands of Oracle & DB2 & SQL people answering each others questions

SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;


select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

"select sid,serial# from v$session where username"

SQL Trace を取得する方法 - DBMS_MONITOR - SQL shutdown abort

select sid,serial# from v$session where username = 'xxxx';
exec dbms_monitor.session_trace_enable(148,46);

iT-folder IT関連情報を配信
Oracle/PLSQL: Oracle System Tables

DBA_USERSの参照:
select USERNAME,ACCOUNT_STATUS from DBA_USERS;

SYSDBA/SYSOPER権限ユーザの確認:
select * from v$pwfile_users;

Check privileges by table name:
select * from dba_tab_privs where table_name = 'xxxxxx';

Checking the owner of table:
select owner, table_name from dba_tables where table_name = 'xxxxxxxx';

Search the column
select owner, table_name, column_name from dba_col_comments where column_name like '%xxxxx%'; (CAPITAL)

Checking the value of timed_statistics:
select value from v$parameter where name = ‘timed_statistics’;

Check SQL values:
SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE
b.sql_id = a.sql_id

Talking Tuning: Tracing SQL in Oracle Database 10g

exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'xxxx');
Or
alter system set sql_trace=true;

The default log is in $ORACLE_BASE\instance_name\admin\udump

To analyse:
# trcsess output="*.trc" service="xxxxx"
# cat *.trc > udump.trc
# tkprof udump.trc output.txt sys=no ( explain=cpsuser/cpsuser sort=(execpu))

To disable:
exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'xxxx');

» Read More