Skip to content
October 20, 2017 / doganay

EXTRACT SQLIDs FROM SQL TRACE FILE

export FILENAME="my_trace_file.trc"
AWK="nawk";which nawk 2>/dev/null||AWK="awk"
grep sqlid $FILENAME | \
$AWK -F"=" BEGIN'{print "select * from gv$sql where sql_id in ("}{printf $NF","}END{printf ");\n"}' | \
sed 's/,)/)/g' | sed -e $'s/,/,\\\n/g'

Output would be like this:

select * from gv$sql where sql_id in (
'4tk6t8tfsfqbf',
'7ng34ruy5awxq',
'5n1fs4m2n2y0r',
'83taa7kaw59c1',
'ga9j9xk5cy9s0',
'cvn54b7yz0s8u',
'c6awqs517jpj0',
'39m4sx9k63ba2',
'gqj5gaygdbfs5',
'26jgpwq8ph25m',
'7s71kfm7q5jn7',
'gqj5gaygdbfs5',
'26jgpwq8ph25m',
'grwydz59pu6mc',
'grwydz59pu6mc',
'grwydz59pu6mc',
'aam2chsgpj7mb');

 

Advertisements
October 20, 2017 / doganay

AGGREGATE XML AUDIT FILES BY SPID

$ cd ${AUDIT_DIR}
$ ls | awk -F"_" '{print $3}' | uniq -c | head

OR

$ cd ${AUDIT_DIR}
$ ls | awk -F"_" '{print $3}' | \
  awk '{array1[$1]++;}END {for (i in array1) {print array1[i], i;}}' | \
  sort -nk1 | tail

 

October 20, 2017 / doganay

LIST LONG RUNNING SESSIONS WITH PERCENTAGE GRAPH

long.sql
--------

SET PAGES 1000 LINES 1000 FEED OFF

COL OPNAME FORMAT a30
COL TARGET FORMAT a35
COL PERCENTAGE FORMAT 999.99
COL PSID FORMAT a15
COL MESSAGE FORMAT a100
COL E_SECONDS FORMAT 999999
COL PERC_GRAPH FORMAT a12

SELECT SQL_ADDRESS,
SQL_HASH_VALUE,
b.EXECUTIONS,
a.SID || ',' || a.SERIAL# || ',@' || a.inst_id
PSID,
TARGET,
TIME_REMAINING
T_REMAIN,
ELAPSED_SECONDS
E_SECONDS,
100 * sofar / totalwork
percentage,
'['
|| TRIM (LPAD (' ', FLOOR (sofar / totalwork * 10) + 1, '*'))
|| TRIM (LPAD (' ', 11 - FLOOR (sofar / totalwork * 10), '.'))
|| ']'
PERC_GRAPH,
OPNAME,
TARGET_DESC,
SOFAR,
TOTALWORK,
UNITS,
START_TIME,
LAST_UPDATE_TIME,
CONTEXT,
MESSAGE,
USERNAME,
QCSID
FROM GV$SESSION_LONGOPS a, gv$sql b
WHERE a.inst_id = b.inst_id
AND a.SQL_HASH_VALUE = b.HASH_VALUE
AND time_remaining > 0
ORDER BY executions DESC
/

prompt
SET FEEDBACK ON
September 8, 2017 / doganay

Status Pending (Target Addition in progress)


Problem:

Setup-> Add Target-> Add Targets Manually-> Add Targets Using Guided Process
Target Types: Oracle Cluster and High Availibility Service
Cluster database target stuck in this state:
Status Pending (Target Addition in progress)


Solution:

Do not discover your cluster target using 2nd node. Use 1st node.

August 25, 2017 / doganay

HOW TO PRINT TRACE FILE FROM DB

Thx Frank:

https://blog.dbi-services.com/get-trace-file-from-server-to-client/

set serveroutput on feedback off verify off termout off linesize 1024 trimspool on echo off
declare
-- Edit trace filename ####### !!!!!!! ------
l_tracename varchar2(30):='MYDB1_ora_22618.trc';
--
fd utl_file.file_type;
line varchar2(1024);
l_directory_path all_directories.directory_path%TYPE;
l_directory_name all_directories.directory_name%TYPE;
l_directory_created boolean;
procedure t (msg in varchar2) is begin dbms_output.put_line(msg); end;
begin
/* use old parameter _cached_open_cursors to close all open cursors */
for r in (select 1 from v$session_cursor_cache where count>0) loop
dbms_session.set_close_cached_open_cursors(true);
rollback;
commit;
dbms_session.set_close_cached_open_cursors(false);
end loop;
/* get trace directory and trace file name */
select value into l_directory_path from v$diag_info where name='Diag Trace';
/* get directory name for it, or try to create it */
l_directory_created:=false;
begin
select directory_name into l_directory_name from all_directories where directory_path = l_directory_path and rownum=1;
exception
when no_data_found then
begin
l_directory_name:='UDUMP';
execute immediate 'create directory '||l_directory_name||' as '''||l_directory_path||'''';
l_directory_created:=true;
exception when others then
raise_application_error(-20000,'You must CREATE DIRECTORY '||l_directory_name||' as ''' ||l_directory_path||'''; or be granted CREATE DIRECTORY.');
end;
end;
/* opens the trace file */
begin
fd:=utl_file.fopen(l_directory_name,l_tracename,'R');
exception when others then
raise_application_error(-20001,'Impossible to open file: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
/* read the trace file and prints it */
begin
loop
begin
utl_file.get_line(fd,line);
dbms_output.put_line(line);
exception
when no_data_found then exit;
when others then
dbms_output.put_line('!!! error while reading file '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
end loop;
/* close the trace file */
utl_file.fclose(fd);
end;
end;
/
August 25, 2017 / doganay

RENAME AUTO-GENERATED INTERVAL PARTITIONS

Thx Roberto:

http://www.oraclefindings.com/2017/07/23/switching-range-interval-partitioning/

declare
--Edit this:
v_table_name varchar2(30) := 'TABLE1';
--
v_hv_date date;

function str_to_date(p_str in varchar2) return date is
v_date date;
v_sql varchar2(4000);
begin
select 'select cast(' || p_str || ' as date) from dual'
into v_sql
from dual;

execute immediate v_sql into v_date;
return v_date;
end str_to_date;

begin
-- Rename table partitions
for cur_parts in (
with par_xml as (
select dbms_xmlgen.getXMLType('select table_name,
partition_name,
high_value
from user_tab_partitions
where table_name = '''||v_table_name||'''
and not(regexp_like(partition_name, ''^P[0-9]{8}$''))'
) as col_xml from dual
),
partitions as (select x.*
from par_xml p,
xmltable('/ROWSET/ROW' passing p.col_xml columns
table_name varchar2(30) path '/ROW/TABLE_NAME',
partition_name varchar2(30) path '/ROW/PARTITION_NAME',
high_value varchar2(500) path '/ROW/HIGH_VALUE') x
)
select table_name, partition_name, high_value
from partitions)
loop
v_hv_date := str_to_date(cur_parts.high_value) - 1;
execute immediate 'alter table '||v_table_name||' rename partition '||cur_parts.partition_name||' to P'||to_char(v_hv_date, 'YYYYMMDD');
end loop;

-- Rename index partitions
for cur_idx_parts in (
with par_xml as (
select dbms_xmlgen.getXMLType('select index_name,
partition_name,
high_value
from user_ind_partitions
where index_name in (select index_name from user_indexes where table_name = '''||v_table_name||''')
and not(regexp_like(partition_name, ''^P[0-9]{8}$''))'
) as col_xml from dual
),
partitions as (select x.*
from par_xml p,
xmltable('/ROWSET/ROW' passing p.col_xml columns
index_name varchar2(30) path '/ROW/INDEX_NAME',
partition_name varchar2(30) path '/ROW/PARTITION_NAME',
high_value varchar2(500) path '/ROW/HIGH_VALUE') x
)
select index_name, partition_name, high_value
from partitions)
loop
v_hv_date := str_to_date(cur_idx_parts.high_value) - 1;
execute immediate 'alter index '||cur_idx_parts.index_name||' rename partition '||cur_idx_parts.partition_name||' to P'||to_char(v_hv_date, 'YYYYMMDD');
end loop;
end;
/
August 25, 2017 / doganay

HISTORICAL TABLE SIZE

  SELECT o.CREATED,
         o.OWNER,
         o.OBJECT_NAME,
         o.SUBOBJECT_NAME,
         o.OBJECT_TYPE,
         t.NAME
             "Tablespace",
         s.growth / (1024 * 1024 * 1024)
             "Growth in GB",
         (SELECT SUM (bytes) / (1024 * 1024 * 1024)
            FROM dba_segments
           WHERE segment_name = o.object_name)
             "Total Size(GB)"
    FROM DBA_OBJECTS o,
         (  SELECT TS#, OBJ#, SUM (SPACE_USED_DELTA) growth
              FROM DBA_HIST_SEG_STAT
          GROUP BY TS#, OBJ#
            HAVING SUM (SPACE_USED_DELTA) > 0) s,
         v$tablespace t
   WHERE s.OBJ# = o.OBJECT_ID AND s.TS# = t.TS# AND
--
--Edit this:
o.object_name = 'TABLE1'
------------------------
ORDER BY o.CREATED DESC;
August 25, 2017 / doganay

NEW INDEX SIZE ESTIMATION

set serveroutput on verify off feed off

declare
--Change this:
 V_OWNER_TABLE_IX_COLS varchar2(100) := 'owner1.table1(col1,col2)';
--
 u_bytes number;
 a_bytes number;
begin
 dbms_space.create_index_cost (
  ddl => 'create index create_index_cost_tmp_ix on ' || V_OWNER_TABLE_IX_COLS,
  used_bytes => u_bytes,
  alloc_bytes => a_bytes
 );
 dbms_output.put_line ('USED GB     : '|| round(u_bytes/1024/1024/1024,3));
 dbms_output.put_line ('ALLOCATED GB: '|| round(a_bytes/1024/1024/1024,3));
end;
/

set feed on
August 25, 2017 / doganay

NEW TABLE SIZE ESTIMATION

set serveroutput on verify off feed off

DECLARE
--Change These:
 V_ROWCOUNT number:=1000000;
 V_TBS varchar2(30) := 'TABLESPACE01';
--
 V_USED_BYTES NUMBER(10);
 V_ALLOCATED_BYTES NUMBER(10);
 V_TYPE sys.create_table_cost_columns;
BEGIN
 V_TYPE := sys.create_table_cost_columns
 (
--Change these:
  sys.create_table_cost_colinfo('NUMBER',9),
  sys.create_table_cost_colinfo('VARCHAR2',50),
  sys.create_table_cost_colinfo('VARCHAR2',15),
  sys.create_table_cost_colinfo('DATE',NULL),
  sys.create_table_cost_colinfo('DATE',NULL)
--
 );
 dbms_space.create_table_cost(V_TBS, V_TYPE, V_ROWCOUNT, 7, V_USED_BYTES, V_ALLOCATED_BYTES);
 dbms_output.put_line(chr(10));
 dbms_output.put_line('USED GB      : '|| TO_CHAR(V_USED_BYTES/1024/1024/1024));
 dbms_output.put_line('ALLOCATED GB : '|| TO_CHAR(V_ALLOCATED_BYTES/1024/1024/1024));
 dbms_output.put_line(chr(10));
END;
/

set feed on
March 17, 2017 / doganay

EXTRACT SQL HINTS FROM A RUNNING SQL


1)

select CHR(9)||''''
||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
|| ''','
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval
from gv$sql_plan
where sql_id = '&V_SQL_ID'
and CHILD_NUMBER = 0
and other_xml is not null)) d;


2)
This also prints current sql hints:

select * from table(dbms_xplan.display_cursor('&V_SQL_ID',null,'OUTLINE'));