2017-02-16

Long to clob

Use function sys.dbms_metadata_util.long2clob
For example

SELECT sys.dbms_metadata_util.long2clob(v.textlength,
                                        'SYS.VIEW$',
                                        'TEXT',
                                        v.rowid) 
FROM sys.view$ v;

LISTAGG - remove duplicates

My colleague Victor help me to find solution for task:
Aggregate string from query result without duplicates.
Almost all solutions, that I find in internet was like
SELECT LISTAGG(str, ',') WITHIN GROUP (ORDER BY 1)
FROM (SELECT DISTINCT str FROM tab);
But if you have scalar subquery with filter condition this solution doesn’t work because of 2-levels of nesting.
Below there are 2 solutions with regexps and xslt -transformations
SELECT (
   SELECT regexp_replace(LISTAGG(object_type, ',') WITHIN GROUP (ORDER BY object_type), '([^,]+)(,\1)+', '\1') 
   FROM user_objects
   ) solution1,
   (
   SELECT rtrim(xmltype('<r><n>' || LISTAGG(object_type, ',</n><n>') WITHIN GROUP (ORDER BY object_type) || ',</n></r>').extract('//n[not(preceding::n = .)]/text()').getstringval(), ',')
   FROM user_objects
   ) solution2
FROM dual;
UPD: Starting from Oracle 19c this scripts are not relevant: we can use native syntax:
SELECT listagg(distinct object_type, ', ') txt FROM all_objects;

2017-02-09

Sql profile content

Extract sql profiles info and hints:

SELECT CREATED, PROFILE_NAME, SQL_TEXT, 
  XMLtransform(XMLTYPE(h.comp_data), '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="*">
<xsl:for-each select="/outline_data/hint">
<xsl:value-of select="."/>
<xsl:text>&#xa;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>').getStringVal()
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p
WHERE p.name = h.profile_name;

This query puts all hints in one field. If multiply lines is good for you you can choose simplier implementation

SELECT CREATED, PROFILE_NAME, SQL_TEXT, extractvalue(VALUE(hint), '.') AS hint
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p, TABLE(xmlsequence(extract(xmltype(h.comp_data), '/outline_data/hint'))) hint
WHERE p.name = h.profile_name;

Also found solution on JL site, but this query valid for 10g only

select
       sp.sp_name, sa.attr#, sa.attr_val
from
       sqlprof$      sp,
       sqlprof$attr  sa
where
       sp.signature = sa.signature
and     sp.category  = sp.category
order by
       sp.sp_name,
       sa.attr#
;

The further investigations give me the link to Christian’s Antognini site where he recommends the following query for 11g:

SELECT so.name, extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name = 'opt_estimate'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

Test shows me, that this query is incorrect. Correct query is

SELECT so.name,extractvalue(VALUE(h), '.') AS hint
FROM sys.sqlobj$data od,
     sys.sqlobj$ so,
     TABLE(xmlsequence(extract(xmltype(od.comp_data), '/outline_data/hint'))) h
WHERE so.name IN (SELECT name FROM DBA_SQL_PROFILES)
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

But it should be rewritten to show sql-query and creation time

2017-02-08

Why put sys.aud$ to sysaux?

It’s not a secret, that if you leave sys.aud$ in system tablespace, you can catch high buffer busy waits because of freelist management of system tablespace.
But which tablespace choose for moving? SYSAUX or user tablespace.
One more point for SYSAUX from Data Pump:

When transporting a database over the network using full transportable
export, auditing cannot be enabled for tables stored in an
administrative tablespace (such as SYSTEM and SYSAUX) if the audit
trail information itself is stored in a user-defined tablespace

2017-02-01

Looking for object usage

WITH looking_for(l_owner, l_name) AS (
  SELECT UPPER('&owner'), UPPER('&object_name') FROM dual
  ),
  dep AS (
    SELECT 'DBA_DEPENDENCIES' info_from, d.owner, d.name, d.type, CAST(d.dependency_type AS VARCHAR2(4000)) note 
    FROM dba_dependencies d, looking_for 
    WHERE d.referenced_name = l_name AND d.owner = l_owner
    ),
  dba_source_with_owner AS (
    SELECT 'DBA_SOURCE with owner', d.owner, d.name, d.type, NULL
    FROM dba_source d, looking_for 
    WHERE regexp_like(text, '(^|[^A-Z0-9#$_]+)' || l_owner || '.' || l_name , 'i')
      AND NOT (d.name = l_name AND d.owner <> l_owner)
    ),
  dba_source_wo_owner AS (
    SELECT 'DBA_SOURCE without owner', d.owner, d.name, d.type, NULL
    FROM dba_source d, looking_for 
    WHERE regexp_like(text, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
      AND d.owner = l_owner
      AND d.name <> l_name
    ),
  jobs AS (
    SELECT 'DBMS_JOB', d.priv_user, to_char(d.job), NULL, d.what
    FROM dba_jobs d, looking_for 
    WHERE regexp_like(what, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  schedules AS (
    SELECT 'DBA_SCHEDULER_JOBS.JOB_ACTION', d.owner, d.job_name, job_type, d.job_action
    FROM dba_scheduler_jobs d, looking_for 
    WHERE regexp_like(job_action, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  schedules_programs AS (
    SELECT 'DBA_SCHEDULER_PROGRAMS.PROGRAM_ACTION', d.owner, d.program_name, program_type, program_action
    FROM dba_scheduler_programs d, looking_for 
    WHERE regexp_like(program_action, '(^|[^A-Z0-9#$_]+)' || l_name , 'i')
    ),
  privs AS (
    SELECT 'DBA_TAB_PRIVS' info_from, NULL, grantee, 'ROLE', PRIVILEGE note 
    FROM dba_tab_privs d, looking_for 
    WHERE d.table_name = l_name AND d.owner = l_owner
    ),
  policy AS (
    SELECT 'DBA_POLICIES' info_from, NULL, d.policy_name, 'POLICY FOR ' || d.object_owner || '.' || d.object_name, pf_owner || '.' || d.package || '.' || d.function note 
    FROM dba_policies d, looking_for 
    WHERE d.pf_owner = l_owner 
      AND (d.package = l_name OR d.function = l_name)
    ),
  sql_plan AS (
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'HIST_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_text, 1, 4000))
    FROM looking_for, dba_hist_sql_plan p, dba_hist_sqltext t
    WHERE p.object_owner = l_owner
      AND p.object_name = l_name
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'INDEX_HIST_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_text, 1, 4000))
    FROM dba_hist_sql_plan p, dba_hist_sqltext t
    WHERE (object_owner, object_name) IN (SELECT owner, index_name FROM dba_indexes, looking_for WHERE table_name = l_name AND owner = l_owner)
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_fulltext, 1, 4000))
    FROM looking_for, v$sql_plan p, v$sql t
    WHERE p.object_owner = l_owner
      AND p.object_name = l_name
      AND p.sql_id = t.sql_id(+)
    UNION
    SELECT 'SQLPLAN', NULL AS owner, NULL AS NAME, 'INDEX_SQL' AS TYPE, TO_CHAR(SUBSTR(t.sql_fulltext, 1, 4000))
    FROM looking_for, v$sql_plan p, v$sql t
    WHERE (object_owner, object_name) IN (SELECT owner, index_name FROM dba_indexes, looking_for WHERE table_name = l_name AND owner = l_owner)
      AND p.sql_id = t.sql_id(+)
  ),
  tab_modifications AS (
    SELECT 'DBA_TAB_MODIFICATIONS' info_from, NULL AS owner, NULL AS NAME, 'Was modified on: ' || TO_CHAR(TIMESTAMP, 'DD.MM.YYYY HH24:MI:SS'), 
        'Inserts: ' || inserts || '; Updates: ' || updates || '; Deletes: ' || deletes || '; Truncated ' || truncated   note 
    FROM dba_tab_modifications d, looking_for 
    WHERE d.table_owner = l_owner 
      AND d.table_name = l_name
    )
SELECT /*+ PARALLEL(4)*/* FROM dep
UNION ALL
SELECT * FROM dba_source_with_owner
UNION ALL
SELECT * FROM dba_source_wo_owner t WHERE NOT EXISTS (SELECT NULL FROM dba_source_with_owner i WHERE i.owner = t.owner AND i.name = t.name)
UNION ALL
SELECT * FROM jobs
UNION ALL
SELECT * FROM schedules
UNION ALL
SELECT * FROM schedules_programs
UNION ALL
SELECT * FROM privs
UNION ALL
SELECT * FROM policy
UNION ALL
SELECT * FROM sql_plan
UNION ALL
SELECT * FROM tab_modifications
;