How to purge tables from the shared pool

Tables cannot be directly purged from the shared pool using the dbms_shared_pool package. Instead, cursors refering to the table can be purged. To generate statements, use v$sqlarea:

set lines 200
spool purge.sql
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on

select 'exec DBMS_SHARED_POOL.PURGE (''' || ADDRESS ||','|| HASH_VALUE || ''',''C'');'
from V$SQLAREA 
where SQL_ID IN (SELECT SQL_ID 
                 FROM v$sqlarea 
                 WHERE sql_text like 'SELECT * FROM%SBB.ENTITIY_PR%SYS_P5055%');
select 'exit' from dual;
exit

Execute the file "purge.sql" and the cursors refering to the object "SBB.ENTITY_PR" and the partition "SYS_P5055" will be purged. The Oracle 19c documentation for dbms_shared_pool can be found here

How to list index sizes

For non-partitioned global indexes in a specific schema:

select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='NO'
group by i.index_name,s.tablespace_name
order by 3 desc;

INDEX_NAME	TABLESPACE_NAME	GB
AN_EEG_IDX	SH	2406
PK_EEG	SH	994
UIDX_REG_EEG	SH	502
IDX_REL_IDX	SH	156

For non-partitioned global indexes on a specific table:

select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='NO'
and i.table_name = 'ACCOUNTING'
group by i.index_name,s.tablespace_name
order by 2 desc;

INDEX_NAME	TABLESPACE_NAME	GB
IDX_ACC1	SH	120
PK_ACC	SH	994

For partitioned indexes on a specific table:

select s.segment_name,round(sum(s.bytes)/1024/1024/1024,2) "GB" 
from dba_segments s
where segment_name IN (select unique index_name 
                        from dba_indexes 
                        where table_name='SALES' 
                        and PARTITIONED='YES'
                        and index_type <> 'LOB'
                        and owner='SH')
group by s.segment_name;

SEGMENT_NAME	GB
IDX_UK1	28
IDX_ACC_UK2	78,24

For all partitioned indexes in a specific schema:

select i.index_name,i.table_name,s.tablespace_name,round(sum(s.bytes)/1024/1024/1024) "GB"
from dba_indexes i join dba_segments s
on (i.index_name = s.segment_name)
where i.owner='SH'
and i.partitioned='YES'
and i.index_type <> 'LOB'
group by i.index_name,i.table_name,s.tablespace_name
order by 2 ;

INDEX_NAME	TABLE_NAME	TABLESPACE_NAME	GB
IDX_OP_AA	ENTITY	SH	260
IDX_OP_NA	ENTITY_HISTORY	SH	1082
IDX_VER_AA	EVENT	SH	28
IDX_VER_AA_HIST	EVENT	SH	78
IDX_WW_UK2	RELATION	SH	226
IDX_RELNO	RELATION_ENTITY	SH	350

How to list partitions and their LOB segment sizes

SELECT  ts.table_owner "owner",
        ts.table_name "table name",
        s.SEGMENT_NAME "segment name",
        s.SEGMENT_TYPE "segment type",
        s.SEGMENT_SUBTYPE "lob type",
        s.PARTITION_NAME "lob part name",
        lp.column_name "lob name",
        lp.compression "lob compression",
        lp.partition_name "table part name",
        ts.num_rows "num rows",
        lp.tablespace_name "tablespace",
        ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_partitions lp
ON (s.partition_name = lp.lob_partition_name)  
   JOIN DBA_TAB_PARTITIONS ts
        ON (TS.PARTITION_NAME = lp.PARTITION_NAME) 
WHERE lp.table_name='MYTABLE'
AND   ts.table_name='MYTABLE'
-- To limit the output to a specific tablespace, uncomment line below
AND   s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name 
                        FROM dba_lob_partitions lpt 
                        WHERE lpt.table_name IN ( 'MYTABLE' ) )
GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lp.COMPRESSION,/*lp.subpartition_name*/lp.partition_name, lp.column_name,ts.num_rows,lp.tablespace_name
/*
To limit output to lob subpartitions with a specific size, 
uncomment the restriction above and change the operator to suit your needs ( <, >, =) 
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY 12 DESC;

Output:

owner	table name	segment name	segment type	lob type	lob part name	lob name	lob compression	table part name	num rows	tablespace	size GB
JIM	MYTABLE	SYS_LOB0000079505C00019$$	LOB PARTITION	SECUREFILE	SYS_LOB_P3273	MYLOB1	NO	SYS_P3270	864051	DATA1	20
JIM	MYTABLE	SYS_LOB0000079505C00019$$	LOB PARTITION	SECUREFILE	SYS_LOB_P4684	MYLOB1	NO	SYS_P4681	593520	DATA1	17
JIM	MYTABLE	SYS_LOB0000079505C00019$$	LOB PARTITION	SECUREFILE	SYS_LOB_P5692	MYLOB1	NO	SYS_P5689	2021989	DATA1	16

For queries against a subpartitioned table, see this post.

How to generate dbms_application_info settings together with DDL in a sql script

Many DBAs forget to incoporate usage of dbms_application_info into their own scipts. This is particulary important if you start long-running maintenance jobs that could last for days or weeks.

By sending MODULE and ACTION to the databaser server, you allow for more granular searching using v$session, end-to-end tracing using trcsess. In addtion, more information is collected by any ongoing auditing so it's easier to see who did what later.

Here is a sniplet that can be used when generating a DDL script for multiple objects.

accept table_owner prompt 'Table owner: '
accept table_name prompt 'Table name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_lob_&&table_owner..&&table_name..sql
select 'alter session set nls_language=''american'';' from dual;
select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_move_lob_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveLOB_&&table_name''); ' from dual;

-- your DDL statements generated here, for example:
select 'alter table ' || owner || '.' || table_name || ' move tablespace data2 online;'
from dba_tables
where owner='&&table_owner';

select 'exit' from dual;
exit

How to extract all mountpoints for oracle data files in a database

To extract all unique mount points used for datafiles in a database, used the query below:

SELECT distinct SUBSTR(FILE_NAME, 1,
               INSTR(FILE_NAME, '/', -1, 1) --> Search for position of the first occurrence of the char '/', start at end of string
              -1) "PATH" --> starting from the rightmost side of the string, search from position found in INSTR above, and work your way to position 1
FROM DBA_DATA_FILES
;

Example output:

PATH
---------------------------------
/sales_db_02/oradata/SALES
/salesdb/oradata/SALES
/sales_db_01/oradata/SALES

If the database is in mount-mode (for example, a physical standby database), exchange dba_data_files with v$datafile:

 SELECT distinct SUBSTR(NAME, 1,
                   INSTR(NAME, '/', -1, 1)  
                  -1) "PATH" 
FROM v$datafile;

For logfiles, exchange dba_data_files with v$logfile and file_name with member:

  SELECT distinct SUBSTR(member, 1,
               INSTR(member, '/', -1, 1) 
              -1) "PATH" 
FROM v$logfile
;

Workaround for ORA-39358 during import

When running import, you may sometimes run into the problem below:

impdp parfile=myparfile.par

Import: Release 12.2.0.1.0 - Production on Tue Jun 8 14:54:34 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0

Solution:

 oerr ora 39358
39358, 00000, "Export dump file version %s not compatible with target version %s"
// *Cause:    The Oracle Data Pump export job version was newer than the target
//            compatibility version.
// *Action:   Upgrade the target database to a compatibility level of at least
//            the export job version, or rerun the export job with a job
//            version that is lower than or equal to the compatibility version
//            of the target database.

The simplest way out for me was to downgrade the export. On the source database server, add the following parameter to your export file:

VERSION=12.2

So that my complete parameter file looked as follows:

userid=system/passwd
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=HR.dmp
LOGFILE=exp_HR.log
JOB_NAME=HR
EXCLUDE=STATISTICS
schemas=HR
VERSION=12.2

Rerun the export:

expdp parfile=myparfile.par

Transfer the newly generated dumpfile to your destination server, and rerun the import. This time around, you shouldn't see any errors relating to incompatible versions.

How to fix error message from data guard broker "Property 'DbFileNameConvert' has inconsistent values"

After a rebuild of a physical standby database, I was tailing the broker log file drcSALES.log, and noticed the following warning:

Property 'DbFileNameConvert' has inconsistent values:
METADATA='/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
SPFILE=  '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
DATABASE='/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Note that the metadata and spfile is missing the trailing slash character /.

To fix this incorrect setup:

dgmgrl / as sysdba
show configuration;
Configuration - SALES

  Protection Mode: MaxPerformance
  Members:
  SALES      - Primary database
    SALES_STB  - Physical standby database
      SALES_TSTB - Physical standby database (receiving current redo)
    SALES_RO   - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Check the current setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES'

Update the property:

DGMGRL> edit database 'SALES_STB' set property DbFileNameConvert='/data1/oradata/SALES/,/data1_stb/oradata/SALES/, /data2/oradata/SALES/, /data2_stb/oradata/SALES/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "dbfilenameconvert" updated

Verify the new setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Restart your physical database, and the issue is fixed.