Oracle-常用语句

1.修改用户密码

alter user 用户名 identified by 新密码;

2.表空间扩容

1.增加数据文件

alter tablespace I_TBS_SAAS add datafile '+DATADG' size 20G autoextend off;

2.修改数据文件大小

ALTER DATABASE DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFIRST\DITS.DBF' RESIZE 400M;

3.临时表空间扩容

alter database tempfile ‘+DG_ORA/sdpdb/ora_temp03’ resize 10G;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DG_ORA/sdpdb/ora_temp03’ SIZE 10G;

4.创建表空间

create tablespace D_SERVICE_CMJSIASD datafile '+DATADG' size 30G autoextend off;

5.查询硬解析

set linesize 220 pagesize 10000

set long 999999999

col MODULE for a40

col sql_id for a30

col PARSING_SCHEMA_NAME for a20

select a.sql_id,

a.MODULE,

a.PARSING_SCHEMA_NAME,

a.last_active_time,

a.last_load_time,

a.sql_fulltext,

b.pool_mb,

b.cnt

from v$sqlarea a,

(select max(sql_id) sql_id,

FORCE_MATCHING_SIGNATURE,

round(sum(SHARABLE_MEM / 1024 / 1024)) pool_mb,

count(1) cnt

from v$sqlarea

where FORCE_MATCHING_SIGNATURE > 0

and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE

group by FORCE_MATCHING_SIGNATURE

having count(1) > 300

order by count(1) desc) b

where a.sql_id = b.sql_id

order by cnt desc;

6.查询那些表被锁

select s.inst_id,

s.sql_id,

s.username,

object_name,

machine,

s.type,

'alter system kill session '''||s.sid||','||s.serial#||''';' ,

l.locked_mode

from v$locked_object l,dba_objects o ,gv$session s

where l.object_id=o.object_id and l.session_id=s.sid

and s.type=’user’

and object_name='T_OB_U_2018050401022494_2202'

and s.username='WS_AMCCC_QDWH'

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.查询长事务

with transaction_details as

( select inst_id

, ses_addr

, sysdate - start_date as diff

from gv$transaction

)

select s.username

, to_char(trunc(t.diff))

|| ' days, '

|| to_char(trunc(mod(t.diff * 24,24)))

|| ' hours, '

|| to_char(trunc(mod(t.diff * 24 * 60,24)))

|| ' minutes, '

|| to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))

|| ' seconds' as transaction_duration

, s.osuser

, s.program

, s.terminal

, s.status

, s.sid

, s.serial#

from gv$session s

, transaction_details t

where s.inst_id = t.inst_id

and s.saddr = t.ses_addr and username='&CRMAPP'

order by t.diff desc

9.查询大于5000的子游标

select inst_id,sql_id,is_obsolete,count() from gv$sqlgroup by inst_id,sql_id,is_obsolete having count()>5000;

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;