oracle 相关

找表

select * from all_tables where table_name like '%NGINX_LOG_REPORT%'

 

---

=========任务运行===============

---

 

---任务列表

select spend_time/60,t.* from SPEND_TIME t

--where lower(t_name)=lower('P_TADU_CHANNEL_NEW')

order by begin_time desc,end_time

• select t.*, t.rowid from report_time_message t

order by send_dt desc

select t.*, t.rowid from report_time t

where begin_time >=date'2013-11-26'

• --and lower(p_name) like lower('%P_TADU_BOOK%')

order by begin_time desc ,end_time desc

select * from report_time_message

where msg_content like '%snapshot%'

order by send_dt desc

 

---

 

select t.rowid,t.* from spend_jobs t

where parent_id='p_report_c'

where lower(job_name) like lower('%main%')

---任务运行时间

select t2.t_name,t2.begin_time,end_time,flag,round(spend_time/60/60,2) Hours,round(spend_time/60,2) Mins ,t2.*

from SPEND_TIME t2

left join SPEND_JOBS t1 on t2.t_name = t1.job_id

where 1=1

and flag=1

and report_dt >=date'2013-12-01'

and lower(t_name) like lower('%p_link_c_2_0%')

• --and levels=2 --and t2.parent_id='de'

  --and spend_time>=60*60 --order by t2.begin_time

--order by report_dt desc

order by t2.t_name, t2.begin_time desc

---任务情况查看列表

select t1.job_name,

• t1.job_id,levels,versions,parent_id,importance, count(0), max(spend_time)/60 maxx, avg(spend_time)/60 avgm,avg(spend_time)/3600 avgh

--select t2.*

• from SPEND_TIME t2 left join SPEND_JOBS t1 on t2.t_name = t1.job_id

where flag=1

• and t2.begin_time>=date'2013-11-03' --and t2.begin_time<=date'2013-10-22' and parent_id='p_report'

--and lower(t2.t_name) like lower('%sh%')

• group by t1.job_name, t1.job_id,levels,versions,parent_id,importance order by avgm desc

 

---

系统表-q

select * from user_jobs;

select t.* from user_scheduler_jobs t;

select * from user_scheduler_job_run_details

where 1=1

--and job_name !='P_TADU_BOOK_RANK1'

and job_name ='P_SALES_REPORT1'

--and req_start_date like '24-12%'

order by log_date desc ;

select * from dba_jobs_running /*+RULE*/;

select * from dba_scheduler_job_run_details

where log_date>=date'2013-11-01'

and job_name ='GATHER_STATS_JOB'

order by actual_start_date desc ;

select job_name,job_action,repeat_interval,enabled,auto_drop,state,run_count,failure_count,retry_count,last_start_date,last_run_duration,next_run_date, t.*

from user_scheduler_jobs t;

--系统Scheduler

select owner,job_name,program_name,t.* from dba_scheduler_jobs t;

select * from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG';

--确定执行窗口

--目标出现了，我们仅需要知道“MAINTENANCE_WINDOW_GROUP”是如何定义的，便可以知道统计信息自动采集的调度时间。

select * from dba_scheduler_wingroup_members where window_group_name = 'MAINTENANCE_WINDOW_GROUP';

select window_name, repeat_interval, duration from dba_scheduler_windows where window_name = 'WEEKNIGHT_WINDOW';

--查看当前运行的任务

select a.program, b.spid, c.sql_text,a.machine,a.osuser,a.*

• from v$session a, v$process b, v$sqlarea c

• where a.paddr = b.addr
  • and a.sql_hash_value = c.hash_value and a.username is not null;

--查看不同用户的连接数

select username,count(username)

--select *

from V$SESSION where username is not null group by username

--查看锁表进程SQL语句1：

select sess.sid,

• sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess

where ao.object_id = lo.object_id and lo.session_id = sess.sid;

 

---

 

alter system kill session '72,48';

alter system kill session '130,26428';

alter system kill session '14,33988';

14,33988

alter system kill session '62,21341';

 

---

表空间大小

SELECT 　　A.A1 表空间名称,

• C.C2 类型, C.C3 区管理, B.B2 / 1024 / 1024 表空间大小M, (B.B2 - A.A2) / 1024 / 1024 已使用M, SUBSTR((B.B2 - A.A2) / B.B2 * 100, 1, 5) 利用率 FROM (SELECT TABLESPACE_NAME A1, SUM(NVL(BYTES, 0)) A2
  • FROM DBA_FREE_SPACE
  • GROUP BY TABLESPACE_NAME) A,
  (SELECT TABLESPACE_NAME B1, SUM(BYTES) B2
  • FROM DBA_DATA_FILES
  • GROUP BY TABLESPACE_NAME) B,
  (SELECT TABLESPACE_NAME C1, CONTENTS C2, EXTENT_MANAGEMENT C3
  • FROM DBA_TABLESPACES) C 　　WHERE A.A1 = B.B1 AND C.C1 = B.B1;

--查看表空间及数据文件

select t1.name,t2.name,round(t2.bytes/1024/1024/1024,2) bytes_G,round(t2.CREATE_BYTES/1024/1024/1024,2) CREATE_BYTES_G,t2.*

• from v$tablespace t1,v$datafile t2
  • where t1.ts# = t2.ts#;

--查看表的分区的rownum

SELECT partition_name,num_rows from DBA_TAB_PARTITIONS

WHERE table_name =UPPER('log_tadu')

and num_rows>0

select count(0) from v$process --当前的连接数-95

select value from v$parameter where name = 'processes' --数据库允许的最大连接数

--修改最大连接数:

alter system set processes = 300 scope = spfile;

select owner, segment_name, segment_type, sum(bytes / 1024 / 1024)

• from dba_segments

• where tablespace_name = 'TYTDTJ_DATA' group by owner, segment_name, segment_type order by sum(bytes / 1024 / 1024) desc

--查看系统版本

• select * from v$version;

 

---

创建Schedule

begin

dbms_scheduler.create_job

(

job_name => 'ARC_MOVE',

schedule_name => 'EVERY_60_MINS',

job_type => 'EXECUTABLE',

job_action => '/home/dbtools/move_arcs.sh',

enabled => true,

comments => 'Move Archived Logs to a Different Directory'

);

end;