Skip to content
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.

Advertisements
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'));
March 17, 2017 / doganay

BIND DATATYPE MISMATCHED SQLS

  • !!! These sqls do not return all bind datatype mismatches. Just return right and wrong datatype binded at the same time ones.

RECENT:

SELECT COUNT (*),
       sql_id,
       child_number,
       name
  FROM (SELECT DISTINCT sql_id,
                        child_number,
                        name,
                        SUBSTR (datatype_string, 1, 4) dtype
          FROM gv$sql_bind_capture
        GROUP BY sql_id,
                 child_number,
                 name,
                 SUBSTR (datatype_string, 1, 4))
GROUP BY sql_id, child_number, name
HAVING COUNT (*) > 1;


HISTORICAL:

SELECT COUNT (*), sql_id, name
  FROM (SELECT DISTINCT sql_id, name, SUBSTR (datatype_string, 1, 4) dtype
          FROM dba_hist_sqlbind
        GROUP BY sql_id, name, SUBSTR (datatype_string, 1, 4))
GROUP BY sql_id, name
HAVING COUNT (*) > 1;
March 17, 2017 / doganay

AUTOMATE DROPPING OLD PARTITIONS

--Print drop partition script older then 3 months for table owner1.table1
DECLARE
 V_OWNER varchar2(30) := 'OWNER1';
 V_TABLE_NAME varchar2(30) := 'TABLE1';
 V_MONTHS_OLDER number := 3;
BEGIN
    FOR c
        IN (SELECT table_owner, table_name, partition_name, high_value
              FROM dba_tab_partitions
             WHERE     table_owner = V_OWNER
                   AND table_name  = V_TABLE_NAME)
    LOOP
        EXECUTE IMMEDIATE
               'BEGIN
             IF sysdate >= ADD_MONTHS('
            || c.high_value
            || ', V_MONTHS_OLDER) THEN
                dbms_output.put_line(''ALTER TABLE '
            || c.table_owner
            || '.'
            || c.table_name
            || ' DROP PARTITION '
            || c.partition_name
            || ';'');
                  END IF;
          END;';
    END LOOP;
END;
/
March 17, 2017 / doganay

expdp FLASHBACK_TIME

$ expdp FLASHBACK_TIME=TO_TIMESTAMP('2017/03/01 14:41:01', 'YYYY/MM/DD HH24:MI:SS') schemas=doganay directory=tmpdir dumpfile=doganay.dmp

-bash: syntax error near unexpected token `('

$ expdp FLASHBACK_TIME="TO_TIMESTAMP('2017/03/01 14:41:01', 'YYYY/MM/DD HH24:MI:SS')" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

LRM-00116: syntax error at ')' following 'YYYY/MM/DD HH24:'

$ expdp FLASHBACK_TIME=\"TO_TIMESTAMP\('2017/03/0114:41:01', 'YYYY/MM/DDHH24:MI:SS'\)\" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-00907: missing right parenthesis

$ expdp FLASHBACK_TIME="2017/03/17 15:34:01" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

LRM-00112: multiple values not allowed for parameter 'flashback_time'


Solution:

$ expdp FLASHBACK_TIME="2017/03/1715:34:01" schemas=doganay directory=tmpdir dumpfile=doganay.dmp

Export: Release 11.2.0.4.0 - Production on Fri Mar 17 15:35:22 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA FLASHBACK_TIME=2017/03/1715:34:01 schemas=doganay directory=tmpdir dumpfile=doganay.dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
...