1. 查表空间的使用情况:
SELECT a.tablespace_name,
SUM(a.bytes)/1024/1024 TOTAL_SIZE,
(SELECT sum(b.bytes)/1024/1024
FROM dba_free_space b WHERE b.tablespace_name=a.tablespace_name) remain_size,
((SUM(a.bytes)/1024/1024)-((SELECT sum(b.bytes)/1024/1024
FROM dba_free_space b WHERE b.tablespace_name=a.tablespace_name))) used_size,
ROUND((((SUM(a.bytes)/1024/1024)-((SELECT sum(b.bytes)/1024/1024
FROM dba_free_space b WHERE b.tablespace_name=a.tablespace_name)))/(SUM(a.bytes)/1024/1024)),2) used_rate
FROM dba_data_files a
GROUP BY a.tablespace_name;
3. 归档设置
1 sql> archive log list; #查看是否是归档方式
2 sql> alter system set log_archive_start=false scope=spfile; #禁用自动归档
3 sql> shutdown immediate;
4 sql> startup mount; #打开控制文件,不打开数据文件
5 sql> alter database noarchivelog; #将数据库切换为非归档模式
6 sql> alter database open; #将数据文件打开
7 sql> archive log list; #查看此时便处于非归档模式
4. 查看执行计划
select * from table(dbms_xplan.display_cursor('c0q6wv27b51yh'));
select * from table(dbms_xplan.display_awr('c0q6wv27b51yh'));
5. 取消表空间的自动扩展
先通过dba_data_files找到表空间对应的数据文件
再取消数据文件的自动扩展:
SQL> alter database datafile '/opt/oracle/oradata/orcl/undotbs01.dbf' autoextend off;
6.timestamp格式转化
SELECT TO_CHAR(T.startup_time, 'yyyy-mm-dd hh24:mi:ss') FROM DBA_HIST_SNAPSHOT T;
7.查看跟踪文件位置
SELECT a.VALUE
|| b.symbol
|| c.instance_name
|| '_ora_'
|| d.spid
|| '.trc' trace_file
FROM (SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol
FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name
FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d;
8.
ALTER SESSION SET EVENTS ‘immediate trace name heapdump level n’;
1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA内容
2050 SGA内容
4100 UGA内容
8200 Current call内容
16400 User call内容
32800 Large call内容
9. 十六进制与字符间转换
SELECT to_char('3974744754','xxxxxxxxxxxx') FROM dual;
SELECT to_number('ece9cab2','xxxxxxxxxxxx') FROM dual;
10.查看oracle隐藏参数
select x.ksppinm, y.ksppstvl, x.ksppdesc
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
and y.inst_id = userenv('Instance')
and x.inst_id = userenv('Instance')
and x.ksppinm like '\_%' escape '\';