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
or
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
Copyright (c) 1982, 2016, Oracle. All rights reserved.
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 Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
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 Copyright (c) 2003, 2015, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
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
-------------------
1
SQL>
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
---------------------------
0
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 Copyright (c) 2014, 2015, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
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;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
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;
25
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;
35
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;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
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:');
55
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';
60
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;
68
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;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP function local_enquote_name;
Function dropped.
SQL> SET serveroutput off
SQL>
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>
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 = CDB$ROOT Id = 1 ====
/tmp/tmp10.log:==== Current Container = PDB$SEED Id = 2 ====
/tmp/tmp10.log:==== Current Container = PDB$SEED Id = 2 ====
/tmp/tmp10.log:==== Current Container = VIHAAN Id = 5 ====
/tmp/tmp10.log:==== Current Container = VIHAAN Id = 5 ====
/tmp/tmp11.log:==== Current Container = ANUJ Id = 3 ====
/tmp/tmp11.log:==== Current Container = ANUJ Id = 3 ====
/tmp/tmp11.log:==== Current Container = PDB2 Id = 4 ====
/tmp/tmp11.log:==== Current Container = PDB2 Id = 4 ====
/tmp/tmp11.log:==== Current Container = VIHAAN2 Id = 6 ====
/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
1
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>
