Prepare POI for Sygic

Sygic doesn’t support POI and routes in standard KML format.
To load POI to Sygic from google mymaps you have to
1. Export layers to KML format (or KMZ format – its just archive with KML plus icons for places). KML is just XML format
2. Process KML file with XSLT transfomation

<!-- longitude | latitude | name | address | phone | fax | web | email | short description | long description -->
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:df="http://www.opengis.net/kml/2.2">
  <xsl:output method="text" encoding="utf-8" />

  <xsl:param name="delim" select="'|'" />
  <xsl:param name="quote" select="'&quot;'" />
  <xsl:param name="break" select="'&#xA;'" />

  <xsl:template match="/">
    <xsl:apply-templates select="df:kml/df:Document/df:Folder/df:Placemark" />
  </xsl:template>

  <xsl:template match="df:Placemark">
    <xsl:value-of select="substring-before(substring-after(normalize-space(df:Point/df:coordinates), ','), ',')"/>|<xsl:value-of select="substring-before(normalize-space(df:Point/df:coordinates), ',')"/>|<xsl:value-of select="normalize-space(df:name)"/>|||||||<xsl:value-of select="normalize-space(df:description)"/>
    <xsl:if test="following-sibling::*">
      <xsl:value-of select="$break" />
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>

I did it with Notepad++ text editor with XML Tools plugin

Next steps was taken from Sygic site
3. Download CSV to RUPI converter
4. Process file from step 2 with converter
5. Copy created upi/rupi files to Sygic/maps/import folder (create import folder if you don’t have such)

Unfortunately Sygic support told me, that they have no plans to support KML/KMZ format like free MapsMe do.

How to read FILTER operation

Из книги Expert Oracle SQL: Optimization, Deployment, and Statistics

if you see a FILTER operation with more than one operand then the
second and subsequent operands (the subquery or subqueries) are
evaluated for each row returned by the first operand (the main query)

Merge and unnest

Опять из книги Expert Oracle SQL: Optimization, Deployment, and Statistics разница между MERGE и UNNEST

• View merging applies to inline views, factored subqueries, and data
dictionary views that appear as row sources in the FROM clause of an
enclosing query block. View merging is controlled by the MERGE and
NO_MERGE hints.

• Subquery unnesting relates to subqueries in the
SELECT list, WHERE clause, or anywhere else that Oracle may in the
future support. Subquery unnesting is controlled by the UNNEST and
NO_UNNEST hints

UNNEST – это про WHERE, SELECT и управляется при помощи UNNEST – NO_UNNEST hints

VIEW MERGING – это про FROM, управляется при помощи MERGE – NO_MERGE hints
Бывает 2 видов:
SIMPLE VIEW MERGING – heuristic transformation, т.е. применяется безусловно, но может быть отменена хинтами.
COMPLEX VIEW MERGING – cost based transformation, применяется, если subquery содержит DISTINCT или GROUP BY.

Nested loops в планах

NB:

In the case of NESTED LOOPS the estimated row count is per iteration
of the loop whereas the actual row count is for all iterations of the
loop.

Restoring objects statistics

From book Expert Oracle SQL: Optimization, Deployment, and Statistics I found new thing: you don’t need to export-import automatically gathered statistics. Instead of you can use DBMS_STATS.RESTORE* procedures.
By default oracle stores data for 31 days.
Quote from Chapter 9 of the book:

• There are several procedures for restoring statistics including DBMS_STATS.RESTORE_SCHEMA_STATS.
• User statistics set with DBMS_STATS.SET_xxx_STATS procedures are not restored. So, for example, any hand-crafted histogram would have to be reapplied after statistics are restored.
• Although this is normally the default behavior, it is good practice to explicitly invalidate any bad plans in the shared pool by using the NO_INVALIDATE => FALSE parameter setting
• The view DBA_OPTSTAT_OPERATIONS provides a history of gather and restore operations.
• By default superseded statistics are retained for 31 days. This can be managed by the function DBMS_STATS.GET_STATS_HISTORY_RETENTION and the procedure DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.

Join elimination rules

comment from extremedb here

I found 7 Rules for Join Elimination in last year

1.Primary Key-Foreign Key – normal join, Starting in 10gR2
2.Primary Key-Foreign Key – ANSI join, Starting in 11gR1
3.Primary Key-Foreign Key – (semi/anti) join, Starting in 11gR1
4.Unique Index – outer join, Starting in 11gR1

Every guru knows above 4 things

5.Primary Key-Primary Key – simple self join, Starting in 11gR2 –> you ‘ve aleady mentioned

There are two more things and one simular thing
6.Primary Key-Primary Key – self join filter subsumption, Starting in 11gR2
7.Join Back Elimination – Using Bit Map Join Index, Starting in 9iR1

Полезное по подсчетам Logical reads

Прочитал у Рендольфа

So here is an important point: If you want to understand the work Oracle has performed in terms of buffer visits you need to consider both, the number of logical I/Os as well as the number of buffers visited without involving logical I/O - this is represented by the “buffer is pinned count” statistics.
Quite often this fact is overlooked and people only focus on the logical I/Os - which is not unreasonable - but misses the point about pinned buffers re-visited without doing logical I/O.
Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the “fetchsize” or “arraysize” for bulk fetches can influence the number of logical I/Os required to process a result set.

List all privileges for user in oracle

-- You can filter results in last lines of query
WITH username(username) AS (
-- fill user name here
    SELECT UPPER('&USERNAME') FROM dual
  ),
  all_user_roles AS (
     SELECT (SELECT username FROM username) || sys_connect_by_path(granted_role, '->') PATH, granted_role, admin_option
     FROM dba_role_privs p
     START WITH grantee IN (SELECT username FROM username)
     CONNECT BY PRIOR granted_role = grantee
  ),
  grantee AS (
    SELECT granted_role NAME, PATH FROM all_user_roles
    UNION
    SELECT username, NULL AS PATH FROM username
  ),
  priv_list AS (
    SELECT 'ROLE' priv_type, granted_role priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option grantable, PATH
    FROM all_user_roles
    UNION
    SELECT 'SYSTEM' priv_type, privilege priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option, PATH
    FROM dba_sys_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'TABLE' priv_type, PRIVILEGE, owner, table_name, NULL AS column_name, grantable, PATH
    FROM dba_tab_privs, grantee
    WHERE grantee = grantee.name
    UNION
    SELECT 'COLUMN' priv_type, PRIVILEGE, owner, table_name, column_name, grantable, PATH
    FROM dba_col_privs, grantee
    WHERE grantee = grantee.name)
SELECT * 
FROM priv_list
-- optional filter
--WHERE table_name = 'MY_TABLE_NAME'
--AND priv = 'DELETE';  

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;

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

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

OPT_ESTIMATE hint

https://www.pythian.com/blog/oracles-opt_estimate-hint-usage-guide/
Guide how to use hint OPT_ESTIMATE hint, which is used in sql profiles

Sql profiles and hints

How do SQL profiles co-exist with user hints?
SQL profiles, when enabled, remove any user hints from the SQL statement. They can also
inadvertently change optimizer configuration; for example, they will
bring OPTIMIZER_FEATURES_ENABLE to the newest possible value, unless
an older value is shown to be better through test-execution of the SQL
statement.
http://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf

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
;

ORA-01031 on CREATE/ALTER USER under sysdba account

If you have the following error with user operations (CREATE USER, ALTER USER etc)

SQL> connect / as sysdba
Connected.
SQL> create user c##common identified by c##common;
create user c##common identified by c##common
                                    *
ERROR at line 1:
ORA-01031: insufficient privileges

check if Database Vault is enabled.

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

After you enable Oracle Database Vault, you no longer can use the
administrative accounts (such as SYS and SYSTEM) to create or enable
user accounts.
Disabling database Vault is version specific. For example in 12c if you forgot password for accounts with DV_ACCTMGR role the only way is to recreate database.

Powershell: Split file to parts by placeholders

I have file all.sql with following structure

--------------start of file1.sql--------------
...
...content of file1.sql...
...
--------------end of file1.sql--------------


--------------start of file2.sql--------------
...
...content of file2.sql...
...
--------------end of file2.sql--------------

and I need to split file all.sql to separate files file1.sql, file2.sql, etc
You can use following Powershell script to do it

$workingDir="c:\[path_to_dir_with_files]\"
$allFileName="all.sql"

$pattern=[regex]'(?sm)--------------start of (.*?)--------------(.*?)--------------end of (.*?)--------------'

$file = Get-content $workingDir$allFileName -Raw 
foreach($match in $pattern.Matches($file)) {
  $outputFileName = $workingDir+$match.Groups[1].value
  Write-Output $outputFileName
  Set-Content -Path $outputFileName -Value $match.Groups[2].value
}

change pattern expression to hit your placeholders.

Get objects with dblinks

Script to get objects with dblinks in source code. It checks views, materialized views and objects with source code (I don’t check through dba_dependencies because it returns objects that use dblinks via synonyms).
It can be easily extended for dba_jobs for example.

Version with all_ views

WITH db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM all_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM all_views
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM all_mviews
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM all_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;

Version with dba_ views

WITH owner_list(owner) AS (
  SELECT USER FROM dual
 ),
 db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM dba_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM dba_views
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM dba_mviews
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM dba_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND s.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;

Notepad++ lines not starting with word regexp

To remove lines not starting with word you can use following regular expression (put your word instead of GRANT

^(?!GRANT).*$

ORA-24005 and ORA-24002 on table DROP

If table drop ends with message

ORA-24005: Inappropriate utilities used to perform DDL on AQ table [queue_table_name]

first try drop through package DBMS_AQADM with force=true

BEGIN
  SYS.DBMS_AQADM.drop_QUEUE_TABLE(QUEUE_TABLE =>'[queue_table_name]', FORCE=> TRUE);
END;
/ 

But what to do if it returns

ORA-24002: QUEUE_TABLE [queue_table_name] does not exist
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 2

Your data dictionary is already little bit corrupted. You can try following

alter session set events '10851 trace name context forever, level 2';
drop table [queue_table_name];