ORACLE常用语句

1.修改用户密码
alter user 用户名 identified by 新密码;

2.表空间扩容
1.增加数据文件

alter tablespace AA add datafile ‘+DATA’ size 20G autoextend off;

2.修改数据文件大小

ALTER DATABASE DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\aa\aa.DBF’ RESIZE 400M;

3.临时表空间扩容
alter database tempfile ‘+DATA/ORCL/orcl_temp03’ resize 10G;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DATA/ORCL/ora_temp03’ SIZE 10G;

4.创建表空间
create tablespace AA datafile ‘+DATA’ size 30G autoextend off;

5.查询临时表空间对应的数据文件
select FILE_NAME,TABLESPACE_NAME,status,bytes/1024/1024/1024 from dba_temp_files;
查询用户对应的临时表空间
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;

6.开启日志补全
alter database add supplemental log data;
开启force logging模式
alter database force logging;
7.查询归档增长率
SELECT SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH:MI:SS’),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘00’,1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘01’,1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘02’,1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘03’,1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time,‘MM/DD/RR HH24:MI:SS’),10,2),‘04’,1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘05’,1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘06’,1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘07’,1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘08’,1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time,‘MM/DD/RR HH24:MI:SS’),10,2),‘09’,1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘10’,1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘11’,1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘12’,1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘13’,1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘14’,1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘15’,1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘16’,1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘17’,1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘18’,1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘19’,1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘20’,1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘21’,1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘22’,1,0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH24:MI:SS’),10,2),‘23’,1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-10)
GROUP BY SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH:MI:SS’),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, ‘MM/DD/RR HH:MI:SS’),1,5) DESC;

8.查询长事务
set lines 1000 pages 1000
col transaction_duration format a40
col username for a15
col machine for a30
col event for a40
col terminal for a15
col program for a35

with transaction_details as
( select inst_id
, ses_addr
, sysdate - start_date as diff
from gvKaTeX parse error: Expected 'EOF', got '#' at position 467: …id , s.serial#̲ , s.inst_id , …session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by s.inst_id,t.diff desc;

9.查询session数

set pagesize 200
set lin 200
select a.inst_id,a.INACTIVE,b.active,a.inactive+b.active as TOTAL, round((a.inactive+b.active)/c.value*100)||'%' "session usage"   from
(select inst_id,count(1) as "INACTIVE" from gv$session where status='INACTIVE' group by inst_id) a,
(select inst_id,count(1) as "ACTIVE" from gv$session where status='ACTIVE' group by inst_id) b,
(select value from v$parameter where name='sessions')  c
where a.inst_id=b.inst_id;

10.查询表空间使用率
select total.tablespace_name,round(total.GB, 2) as Total_GB,round(total.GB - free.GB, 2) as Used_GB,round((1-free.GB / total.GB)* 100, 2) || ‘%’ as Used_Pct
from (
select tablespace_name, sum(bytes) /1024/1024/1024 as GB
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 /1024 as GB
from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name
order by used_pct desc;