Contacts     ->      ICQ:    699 512 265,         E-Mail: CerebroSQL@gmail.com

  • YouTube Social  Icon

Список SQL запросов используемых в программе для формирования интерфейса, сбора информации о работе баз данных

 

<1> ... <2> ... <3>

LogminerUI

[List directory]

select value "folder" from v$parameter
  where name = 'db_recovery_file_dest'
     or Upper(name) like Upper('log_archive_dest_%')
    and Upper(name) not like Upper('log_archive_dest_state_%')
    and value is not null

[List files in directory]

DECLARE
pattern VARCHAR2(1024) := '
<Directory path>';
ns VARCHAR2(1024);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;

 

​[Logminer: show data]

//закрытие предыдущей сессии logminer

begin DBMS_LOGMNR.END_LOGMNR(); end;

//Для каждого выбранного файла

begin DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '<File full path>',OPTIONS => DBMS_LOGMNR.ADDFILE); end;

//Запуск сессии logminer

begin DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); end;

 

//Отображение информации

SELECT *
   FROM v$logmnr_contents l

[Oracle database outstanding]

select o.creation_time,
       o.OBJECT_NAME,
       o.OBJECT_TYPE,
       o.REASON,
       o.SUGGESTED_ACTION,
       o.MESSAGE_TYPE
  from dba_outstanding_alerts o

Active SQL

[List SQL activity]

select distinct( sl.serial#),
       'EXECUTE' AS STATUS,
       SL.TIME_REMAINING||' c.' as REMAIN,
       SL.ELAPSED_SECONDS||' c.' AS ELAPSED,
       SL.START_TIME "TIME",
       SL.SQL_ID,
       S.SQL_TEXT,
       se.sid,
       sl.opname,
       sl.SOFAR,
       sl.TOTALWORK,
       round((sl.SOFAR/sl.TOTALWORK)*100,2) as "%",
       sl.MESSAGE,
       se.USERNAME,
       se.OSUSER,
       se.MACHINE,
       se.PROGRAM,
       SE.EVENT,
       SE.SECONDS_IN_WAIT
  from V$SESSION SE,
       V$SESSION_LONGOPS SL,
       V$SQL S
 where SE.SID=SL.SID
   AND SE.SERIAL#=SL.SERIAL#
   AND SE.SQL_ID=S.SQL_ID
   AND SE.USERNAME IS NOT NULL
   AND SL.TIME_REMAINING<>'0'
group by sl.serial#,SL.TIME_REMAINING,SL.ELAPSED_SECONDS,SL.START_TIME,SL.SQL_ID, S.SQL_TEXT, se.sid,
            sl.opname, sl.SOFAR, sl.TOTALWORK, sl.MESSAGE, se.USERNAME, se.OSUSER, se.MACHINE, se.PROGRAM,SE.EVENT, SE.SECONDS_IN_WAIT
order by se.sid,sl.serial#

[List query Oracle SQL monitoring]

select *  from v$sql_monitor order by  sql_exec_start desc

[Report SQL analyze]

SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '
<sql_id>',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual

[Generate SQL Analyze report]

--Первый запрос

DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sql_id    => '
<SQL_ID>'
, task_name   => '
SYS_MY_TASK_<task unique name>'
);
END;

--Второй запрос

begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( 'SYS_MY_TASK_<task unique name>'); end;

--Третий запрос

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'SYS_MY_TASK_<task unique name>' ) "SQL" FROM dual