Anuj Singh Oracle DBA: Compile the invalid catalog and catproc in Container and PDB database

Saturday, 26 December 2020

Compile the invalid catalog and catproc in Container and PDB database

Doc ..

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/administering-a-cdb-with-sql-plus.html#GUID-4A64CE26-DD34-4543-B08E-6B4C61A24BAB

Run catcon.pl to start utlrp.sql, and to recompile any remaining invalid objects.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

--n parameter: is set to 1, so the script runs each PDB recompilation in sequence.

--e parameter: turns echo on.

--b parameter: Sets the log file base name. It is set to utlrp.

export PATH=$ORACLE_HOME/perl/bin:$PATH

for all the pdb!!

pwd

/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin

www

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_catcon_32574.lst]

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --b utlrp --d '''.''' utlrp.sql

catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_catcon_5882.lst]

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

--incl_con 'PDB9' <<<< for only This pdbs

www

pwd

/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --incl_con 'PDB9' --b utlrp --d '''.''' utlrp.sql

************************************************************************************************************

output !!!

cat /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlrp0.log

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 26 13:13:50 2020

SQL> Connected.

SQL> 2

Session altered.

SQL> 2

Session altered.

SQL>

ALTER SYSTEM KILL SESSION '613,57640' force timeout 0 -- process 22678

SQL> SQL> 2

Session altered.

SQL> SQL>

SQL> 2

Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2

Session altered.

SQL>

NOW_CONNECTED_TO

--------------------------------------------------------------------------------

==== Current Container = PDB9 Id = 3 ====

SQL>

NOW_CONNECTED_TO

--------------------------------------------------------------------------------

==== Current Container = PDB9 Id = 3 ====

SQL> SQL> 2

CATCONSECTION

-----------------------------------

==== CATCON EXEC IN CONTAINERS ====

SQL>

BEGIN_RUNNING

--------------------------------------------------------------------------------

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:50 Proc:0 ====

SQL>

BEGIN_RUNNING

--------------------------------------------------------------------------------

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:50 Proc:0 ====

SQL> 2

Session altered.

SQL> 2

Session altered.

SQL> SQL> Rem

SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $

SQL> Rem

SQL> Rem utlrp.sql

SQL> Rem

SQL> Rem NAME

SQL> Rem utlrp.sql - Recompile invalid objects

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem This script recompiles invalid objects in the database.

SQL> Rem

SQL> Rem When run as one of the last steps during upgrade or downgrade,

SQL> Rem this script will validate all remaining invalid objects. It will

SQL> Rem also run a component validation procedure for each component in

SQL> Rem the database. See the README notes for your current release and

SQL> Rem the Oracle Database Upgrade book for more information about

SQL> Rem using utlrp.sql

SQL> Rem

SQL> Rem Although invalid objects are automatically re-validated when used,

SQL> Rem it is useful to run this script after an upgrade or downgrade and

SQL> Rem after applying a patch. This minimizes latencies caused by

SQL> Rem on-demand recompilation. Oracle strongly recommends running this

SQL> Rem script after upgrades, downgrades and patches.

SQL> Rem

SQL> Rem NOTES

SQL> Rem * This script must be run using SQL*PLUS.

SQL> Rem * You must be connected AS SYSDBA to run this script.

SQL> Rem * There should be no other DDL on the database while running the

SQL> Rem script. Not following this recommendation may lead to deadlocks.

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate

SQL> Rem gviswana 06/12/03 - Switch default back to serial

SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning

SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics

SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc

SQL> Rem gviswana 06/25/02 - Add documentation

SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial

SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368

SQL> Rem rburns 11/12/01 - validate all components after compiles

SQL> Rem rburns 11/06/01 - fix invalid CATPROC call

SQL> Rem rburns 09/29/01 - use 9.2.0

SQL> Rem rburns 09/20/01 - add check for CATPROC valid

SQL> Rem rburns 07/06/01 - get version from instance view

SQL> Rem rburns 05/09/01 - fix for use with 8.1.x

SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33

SQL> Rem skabraha 09/25/00 - validate is now a keyword

SQL> Rem kosinski 06/14/00 - Persistent parameters

SQL> Rem skabraha 06/05/00 - validate tables also

SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.

SQL> Rem rshaikh 09/22/99 - quote name for recompile

SQL> Rem ncramesh 08/04/98 - change for sqlplus

SQL> Rem usundara 06/03/98 - merge from 8.0.5

SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).

SQL> Rem Mark Ramacher (mramache) was the original

SQL> Rem author of this script.

SQL> Rem

SQL>

SQL> Rem ===========================================================================

SQL> Rem BEGIN utlrp.sql

SQL> Rem ===========================================================================

SQL>

SQL> @@utlprp.sql 0

SQL> Rem

SQL> Rem NAME

SQL> Rem utlprp.sql - Recompile invalid objects in the database

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem This script recompiles invalid objects in the database.

SQL> Rem

SQL> Rem This script is typically used to recompile invalid objects

SQL> Rem remaining at the end of a database upgrade or downgrade.

SQL> Rem

SQL> Rem Although invalid objects are automatically recompiled on demand,

SQL> Rem running this script ahead of time will reduce or eliminate

SQL> Rem latencies due to automatic recompilation.

SQL> Rem

SQL> Rem This script is a wrapper based on the UTL_RECOMP package.

SQL> Rem UTL_RECOMP provides a more general recompilation interface,

SQL> Rem including options to recompile objects in a single schema. Please

SQL> Rem see the documentation for package UTL_RECOMP for more details.

SQL> Rem

SQL> Rem INPUTS

SQL> Rem The degree of parallelism for recompilation can be controlled by

SQL> Rem providing a parameter to this script. If this parameter is 0 or

SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate

SQL> Rem level of parallelism based on Oracle parameters cpu_count and

SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential

SQL> Rem recompilation is used. Please see the documentation for package

SQL> Rem UTL_RECOMP for more details.

SQL> Rem

SQL> Rem NOTES

SQL> Rem * You must be connected AS SYSDBA to run this script.

SQL> Rem * There should be no other DDL on the database while running the

SQL> Rem script. Not following this recommendation may lead to deadlocks.

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem jmuller 12/09/14 - Fix bug 19728696 (sort of): clarify comments

SQL> Rem pyam 04/08/14 - 18478064: factor out to reenable_indexes.sql

SQL> Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL

SQL> Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name

SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes

SQL> Rem not needed.

SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270

SQL> Rem cdilling 01/21/08 - add support for ORA-30552

SQL> Rem cdilling 08/27/07 - check disabled indexes only

SQL> Rem cdilling 05/22/07 - add support for ORA-38301

SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes

SQL> Rem rburns 03/17/05 - use dbms_registry_sys

SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics

SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability

SQL> Rem rburns 09/20/04 - fix validate_components

SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here

SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808

SQL> Rem gviswana 05/28/03 - Created

SQL> Rem

SQL>

SQL> SET VERIFY OFF;

SQL>

SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2020-12-26 13:13:50

SQL>

SQL> DOC

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

SQL>

SQL> DECLARE

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

6 /

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2020-12-26 13:13:51

SQL>

SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist

SQL> Rem here, is no longer needed.

SQL>

SQL> DOC

DOC> The following query reports the number of invalid objects.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status in (3,4,5,6);

OBJECTS WITH ERRORS

-------------------

SQL>

SQL> DOC

DOC> The following query reports the number of exceptions caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC> Note: Typical compilation errors (due to coding errors) are not

DOC> logged into this table: they go into DBA_ERRORS instead.

DOC>#

SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION

---------------------------

SQL>

SQL> Rem =====================================================================

SQL> Rem Reenable indexes that may have been disabled, based on the

SQL> Rem table SYS.ENABLED$INDEXES

SQL> Rem =====================================================================

SQL>

SQL> @@?/rdbms/admin/reenable_indexes.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/reenable_indexes.sql /main/3 2015/02/04 13:57:27 sylin Exp $

SQL> Rem

SQL> Rem reenable_indexes.sql

SQL> Rem

SQL> Rem NAME

SQL> Rem reenable_indexes.sql - <one-line expansion of the name>

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem <short description of component this file declares/defines>

SQL> Rem

SQL> Rem NOTES

SQL> Rem <other useful comments, qualifications, etc.>

SQL> Rem

SQL> Rem BEGIN SQL_FILE_METADATA

SQL> Rem SQL_SOURCE_FILE: rdbms/admin/reenable_indexes.sql

SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/reenable_indexes.sql

SQL> Rem SQL_PHASE: REENABLE_INDEXES

SQL> Rem SQL_STARTUP_MODE: NORMAL

SQL> Rem SQL_IGNORABLE_ERRORS: NONE

SQL> Rem SQL_CALLING_FILE: rdbms/admin/noncdb_to_pdb.sql

SQL> Rem END SQL_FILE_METADATA

SQL> Rem

SQL> Rem MODIFIED (MM/DD/YY)

SQL> Rem sylin 01/30/15 - bug20422151 - longer identifier

SQL> Rem surman 01/08/15 - 19475031: Update SQL metadata

SQL> Rem pyam 04/03/14 - Reenable indexes based on sys.enabled$indexes

SQL> Rem (formerly in utlprp.sql)

SQL> Rem pyam 04/03/14 - Created

SQL> Rem

SQL>

SQL> Rem

SQL> Rem Declare function local_enquote_name to pass FALSE

SQL> Rem into underlying dbms_assert.enquote_name function

SQL> Rem

SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)

2 return varchar2 is

3 begin

4 return dbms_assert.enquote_name(str, FALSE);

5 end local_enquote_name;

6 /

Function created.

SQL> Rem

SQL> Rem If sys.enabled$index table exists, then re-enable

SQL> Rem list of functional indexes that were enabled prior to upgrade

SQL> Rem The table sys.enabled$index table is created in catupstr.sql

SQL> Rem

SQL> SET serveroutput on

SQL> DECLARE

2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

3 commands tab_char;

4 p_null CHAR(1);

5 p_schemaname dbms_id;

6 p_indexname dbms_id;

7 rebuild_idx_msg BOOLEAN := FALSE;

8 non_existent_index exception;

9 recycle_bin_objs exception;

10 cannot_change_obj exception;

11 no_such_table exception;

12 pragma exception_init(non_existent_index, -1418);

13 pragma exception_init(recycle_bin_objs, -38301);

14 pragma exception_init(cannot_change_obj, -30552);

15 pragma exception_init(no_such_table, -942);

16 type cursor_t IS REF CURSOR;

17 reg_cursor cursor_t;

19 BEGIN

20 -- Check for existence of the table marking disabled functional indices

22 SELECT NULL INTO p_null FROM DBA_OBJECTS

23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and

24 object_type = 'TABLE' and rownum <=1;

26 -- Select indices to be re-enabled

27 EXECUTE IMMEDIATE q'+

28 SELECT 'ALTER INDEX ' ||

29 local_enquote_name(e.schemaname) || '.' ||

30 local_enquote_name(e.indexname) || ' ENABLE'

31 FROM enabled$indexes e, ind$ i

32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND

33 bitand(i.property, 16) != 0+'

34 BULK COLLECT INTO commands;

36 IF (commands.count() > 0) THEN

37 FOR i IN 1 .. commands.count() LOOP

38 BEGIN

39 EXECUTE IMMEDIATE commands(i);

40 EXCEPTION

41 WHEN NON_EXISTENT_INDEX THEN NULL;

42 WHEN RECYCLE_BIN_OBJS THEN NULL;

43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;

44 END;

45 END LOOP;

46 END IF;

48 -- Output any indexes in the table that could not be re-enabled

49 -- due to ORA-30552 during ALTER INDEX...ENBLE command

51 IF rebuild_idx_msg THEN

52 BEGIN

53 DBMS_OUTPUT.PUT_LINE

54 ('The following indexes could not be re-enabled and may need to be rebuilt:');

56 OPEN reg_cursor FOR

57 'SELECT e.schemaname, e.indexname

58 FROM enabled$indexes e, ind$ i

59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';

61 LOOP

62 FETCH reg_cursor INTO p_schemaname, p_indexname;

63 EXIT WHEN reg_cursor%NOTFOUND;

64 DBMS_OUTPUT.PUT_LINE

65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);

66 END LOOP;

67 CLOSE reg_cursor;

69 EXCEPTION

70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;

71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;

72 WHEN OTHERS THEN CLOSE reg_cursor; raise;

73 END;

75 END IF;

77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';

79 EXCEPTION

80 WHEN NO_DATA_FOUND THEN NULL;

82 END;

83 /

PL/SQL procedure successfully completed.

SQL>

SQL> DROP function local_enquote_name;

Function dropped.

SQL> SET serveroutput off

SQL>

SQL> Rem =====================================================================

SQL> Rem Run component validation procedure

SQL> Rem =====================================================================

SQL>

SQL> SET serveroutput on

SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off

SQL>

SQL> Rem ===========================================================================

SQL> Rem END utlrp.sql

SQL> Rem ===========================================================================

SQL>

END_RUNNING

--------------------------------------------------------------------------------

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:53 Proc:0 ====

SQL>

END_RUNNING

--------------------------------------------------------------------------------

==== @./utlrp.sql Container:PDB9 Id:3 20-12-26 01:13:53 Proc:0 ====

SQL> SQL>

SQL> 2

Session altered.

*******************************************************************************************************************

-d utlrp.sql <<<sql path

-b file_name in this case tmp1

-l /tmp dir

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -l /tmp -b tmp1 utlrp.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/tmp1_catcon_4317.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/tmp1*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/tmp1_*.lst] files for spool files, if any

catcon.pl: completed successfully

check log file ...

grep "Current Container =" /tmp/tmp1*.log

/tmp/tmp10.log:==== Current Container = CDB$ROOT Id = 1 ====

/tmp/tmp10.log:==== Current Container = PDB$SEED Id = 2 ====

/tmp/tmp10.log:==== Current Container = VIHAAN Id = 5 ====

/tmp/tmp11.log:==== Current Container = ANUJ Id = 3 ====

/tmp/tmp11.log:==== Current Container = PDB2 Id = 4 ====

/tmp/tmp11.log:==== Current Container = VIHAAN2 Id = 6 ====

=========

To run below sql to check invalid objects in all the pdbs

--- invalid.sql

set linesize 300 pagesize 300

col name for a10

col owner for a20

col object_type for a20

select t.con_id,p.name,t.owner,t.object_type,t.status,count(*) from cdb_objects t ,v$containers p

where p.con_id = t.con_id

and t.status = 'INVALID'

group by t.con_id,p.name,t.owner,t.object_type,t.status

order by 3;

-b file_name <<<<< invalid.sql

-l /tmp dir for log file

-d dir name for this file >>> invalid.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle -l /tmp -b invalid invalid.sql

*****************************

Script Name

cat tablespace.sql

===

cat who.sql

alter session set nls_date_format='dd-mm-YYYY hh24:mi';

set linesize 300 pagesize 0 serveroutput on

column "db details" format a300

select

' Sysdate:-' || (select sysdate from dual) ||

' \SERVER_HOST:-' ||SYS_CONTEXT('USERENV', 'SERVER_HOST') ||

' \DB_UNIQUE_NAME:-' ||SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME') ||

' \INSTANCE_NAME:-' ||SYS_CONTEXT('USERENV', 'INSTANCE_NAME') ||

--' \SERVICE_NAME:-' ||SYS_CONTEXT('USERENV', 'SERVICE_NAME') ||

' \PLATFORM_NAME:-' ||(select platform_name from v$database) ||

' \INSTANCE NUMBER:-' ||INSTANCE_NUMBER ||

' \STARTUP_TIME:-' || STARTUP_TIME ||

' \STATUS:-' || STATUS ||

'\ CONTAINER NAME:- ' ||sys_context('userenv','con_name') ||

' \DATABASE ROLE:- ' ||sys_context('userenv','database_role') ||

' \CLIENT IP ADDRESS: ' ||sys_context('userenv','ip_address') ||

'\OS USER: ' ||sys_context('userenv','os_user') ||

SYS_CONTEXT('USERENV', 'DB_DOMAIN') "db details"

from gv$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME');

set pagesize 100

====

@/home/oracle/who.sql

set linesize 300

col NAME for a20

SELECT LTRIM (d.tablespace_name) as name,

NVL (ddf.BYTES - NVL(u.BYTES, 0), 0) as freeSp,

DECODE (d.CONTENTS, 'UNDO', NVL (TRUNC ((ddf.BYTES - NVL (u.BYTES, 0))/(ddf.bytes)*100,3),0), NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100,3), 0)) as avPct,

NVL (dfs.antall, 0) as chunks,

ddf.autoextend_flag as autoext,

NVL (TRUNC ((ddf.bytes)), 0) as maxSize,

NVL (TRUNC ( (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.bytes)*100,3),0) as maxPct

FROM dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) maxbytes, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs,

(SELECT tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible),'YES', 'Y','N') autoextend_flag

FROM dba_data_files GROUP BY tablespace_name) ddf,

(SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_undo_extents WHERE status <> ('EXPIRED') GROUP BY tablespace_name) u

WHERE d.tablespace_name = ddf.tablespace_name(+)

AND d.tablespace_name = dfs.tablespace_name(+)

AND d.tablespace_name = u.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY')

UNION ALL

SELECT LTRIM (d.tablespace_name) as name,

NVL (TRUNC (ddf.BYTES), 0) - NVL (TRUNC (dfs.BYTES), 0) as freeSp,

100 - NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0) as avPct,

DECODE (NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0),0, 1,100, 0) as chunks,

ddf.autoextend_flag as autoext,

NVL (TRUNC ((ddf.bytes)), 0) maxSize,

NVL (TRUNC (NVL (dfs.BYTES, 0) / (ddf.bytes)* 100,3),0) as maxPct

FROM dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes, COUNT (1) antall, DECODE (MAX (autoextensible), 'YES', 'Y','N') autoextend_flag

FROM dba_temp_files GROUP BY tablespace_name) ddf,

(SELECT ss.tablespace_name, SUM ((ss.used_blocks * ts.BLOCKSIZE)) BYTES, MAX ((ss.used_blocks * ts.BLOCKSIZE)) maxbytes, COUNT (1) antall

FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs

WHERE d.tablespace_name = ddf.tablespace_name(+)

AND d.tablespace_name = dfs.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY'

ORDER BY 1

;

========

cd to this dir

[oracle@localhost admin]$ pwd

/u01/app/oracle/product/version/db_1/rdbms/admin

[oracle@localhost admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d /home/oracle -l /home/oracle -S -c 'ORCL' -b tablespace tablespace.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/tablespace_catcon_9130.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/tablespace*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/tablespace_*.lst] files for spool files, if any

catcon.pl: completed successfully

[oracle@localhost admin]$

cat tablespace0.log <<<<< output file

Sysdate:-31-12-2022 09:11 \SERVER_HOST:-localhost \DB_UNIQUE_NAME:-orclcdb \INSTANCE_NAME:-orclcdb \PLATFORM_NAME:-Linux x86 64-bit \INSTANCE NUMBER:-1 \STARTUP_TIME:-31-12-2022 05:31 \STATUS:-OPEN\ CONTAINER NAME:- ORCL \DATABASE ROLE:- PRIMARY \CLIENT IP ADDRESS: \OS USER: oracle

NAME FREESP AVPCT CHUNKS A MAXSIZE MAXPCT

-------------------- ---------- ---------- ---------- - ---------- ----------

APEX_129159770360740 2686976 60.975 1 Y 2686976 39.024

SYSAUX 1258291200 5.072 3 Y 1258291200 94.927

SYSTEM 429916160 .853 2 Y 429916160 99.146

TEMP 37748736 100 1 Y 37748736 0

UNDOTBS2 38207488 72.875 21 Y 52428800 62.25

USERS 420741120 5.7 2 Y 420741120 94.299

6 rows selected.

SQL>

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Saturday, 26 December 2020

Compile the invalid catalog and catproc in Container and PDB database

Compile the invalid catalog and catproc in Container and PDB database

No comments:

Oracle DBA

anuj blog Archive