常用的sql(oracle)

一、查看表空间

select a.tablespace_name,
round(a.bytes/(1024*1024*1024),2)  total,
round(b.bytes/(1024*1024*1024),2)||'G' used,
round(c.bytes/(1024*1024*1024),2)||'G' free,
round((b.bytes*100)/a.bytes,2)||'%' "%USED",
round((c.bytes*100)/a.bytes,2)||'%' "%free" 
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c
where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name

二、查看表空间占用率

SELECT dts.tablespace_name 表空间, NVL(ddf.bytes / 1024 / 1024, 0) 共计,
       NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 已用,
       NVL(dfs.bytes / 1024 / 1024, 0) 空闲,
       TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') 使用率
FROM 
dba_tablespaces dts, 
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf, 
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs 
WHERE   dts.tablespace_name = ddf.tablespace_name(+) 
AND     dts.tablespace_name = dfs.tablespace_name(+) 
AND     NOT (dts.extent_management like 'LOCAL' AND dts.contents like 'TEMPORARY')
order by 5;

三、结束锁死的进程

--1.查询哪些对象被锁：
select o.OWNER||'.'||object_name,machine,s.SID,s.SERIAL#,'alter system kill session '''||s.SID||','||s.SERIAL#||''' immediate;' kk, s.CLIENT_INFO ip ,s.LOGON_TIME
from v$locked_object l, dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID and l.SESSION_ID=s.SID;
--2.杀死进程：
alter system kill session '1012,16413' immediate;
--3.查询已经杀死的进程
select a.SPID,b.SID,b.SERIAL#,b.USERNAME from v$process a,v$session b where  a.ADDR=b.PADDR and b.STATUS='KILLED'

如果查看进程状态为killed但是资源没有被释放。spid为数据库服务器的进程号，在服务器上kill掉

四、查看哪些存储过程被锁

--1.查V$db_Object_Cache
select * from V$db_Object_Cache t where t.NAME='PK_TELLHOW_POWERCUT' AND T.LOCKS!='0' 
--2.查SID值
SELECT /*+rule*/ SID, a.OBJECT,a.OWNER,'alter system kill session '''||SID||','||(select b.SERIAL# from V$SESSION b where b.SID=a.sid)||''' immediate;' from V$ACCESS a where a.OBJECT='PK_TELLHOW_POWERCUT';
--3.查b.SID,b.SERIAL#
select b.SID,b.SERIAL# from V$SESSION b where b.SID='339';
--4.杀进程
alter system kill session '1479,23715' immediate;

五、查询kettle

select tr.name,
       tr.created_date,
       to_char(tr.description),
       to_char(tr.extended_description),
       to_char(a.value_str),
       a.code
  from KETTLE.R_TRANSFORMATION tr, KETTLE.R_STEP_ATTRIBUTE a
 where tr.id_transformation = a.id_transformation
      --and a.code='sql'
   and lower(a.value_str) like '%pbdb.th_dev_byqxx%'
 order by tr.created_date desc;

六、查询kettle的job

select * from kettle.r_job j,kettle.r_jobentry je,kettle.r_jobentry_attribute ja 
where j.id_job=je.id_job and j.id_job=ja.id_job and je.id_jobentry=ja.id_jobentry
and lower(ja.value_str) like '%ythxs_loss_sub%'

七、查询数据库job

select * from dba_jobs t where t.WHAT like '%pk_pbjc%'

八、添加表分区

alter table pbdb.th_ex_data_gbfz_detail  add partition P202112 values less than (TO_DATE('2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

九、数据库用户锁定解锁

ALTER USER PMCS_JOB ACCOUNT UNLOCK
grant create session to PMCS_JOB

十、查询数据库所有用户创建的sql

select t.username, to_char(dbms_metadata.get_ddl('USER',t.username)) from dba_users t;

十一、查询数据库所有表空间创建的sql

select t.TABLESPACE_NAME, to_char(dbms_metadata.get_ddl('TABLESPACE',t.TABLESPACE_NAME)) from dba_tablespaces t 

十二、查看索引是否生效

select   owner,table_name,index_name,status
   from     dba_indexes
    where    owner = 'PBDB'
   and      table_name = 'TH_EX_DATA_GBGDY_DETAIL';

• N/A ：说明这个是分区索引需要查user_ind_partitions或者user_ind_subpartitions来确定每个分区是否可用；
• VAILD ：说明这个索引可用；
• UNUSABLE：说明这个索引不可用；
• USABLE ：说明这个索引的分区是可用的。