======================================
OLAP CATALOG
======================================
SQL > set lines 133 pages 133
SQL > col COMP_NAME format a40
SQL> select comp_name,version,status from
dba_registry;
COMP_NAME VERSION STATUS
----------------------------------------
------------------------------ -----------
Oracle Machine Generated Data 12.1.0.2.0 VALID
OLAP Catalog 11.2.0.4.0 OPTION OFF
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Spatial
12.1.0.2.0
VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
Oracle Data Mining 12.1.0.2.0 VALID
15 rows selected.
[oracle@dbsc admin]$ cd
[oracle@dbsc ~]$ cd $ORACLE_HOME/
[oracle@dbsc 12.1.0]$ cd olap/
[oracle@dbsc olap]$ ls
admin api mesg
[oracle@dbsc olap]$ cd admin/
[oracle@dbsc admin]$ ls
apsdbmig.sql
apse920.sql apsu111.sql awmxsrol.sql
catnothing.sql dbmsawx.sql olapidrp.plb
olap.sql utlolaplog.sql xoqe102.sql
xoqrelod.sql xoqu111.sql
apse101.sql
apspatch.sql apsu112.sql cataps.sql
catnoxoq.sql dbmscbu.sql olapidsm.plb
prvtawx.plb xoqawmd.sql xoqe111.sql xoqroles.sql
xoqu112.sql
apse102.sql
apsrelod.sql apsu920.sql catawxml.sql
catxoq.sql dbmscoad.sql olapiecm.plb
prvtcbu.plb xoqdbmig.sql xoqe112.sql xoqsys.sql
xoqu920.sql
apse111.sql
apsu101.sql apsviews.sql catnoamd.sql
catxoqsys.sql olapiboo.plb olapilib.plb
prvtcoad.plb xoqdrop.sql xoqe920.sql xoqu101.sql
xumuts.plb
apse112.sql
apsu102.sql awmcrxdb.plb catnoaps.sql
creatind.sql olapidcm.plb olapimdm.plb
prvtcoas.plb xoqe101.sql xoqpatch.sql xoqu102.sql
[oracle@dbsc admin]$ ls catno
catnoamd.sql
catnoaps.sql
catnothing.sql catnoxoq.sql
[oracle@dbsc admin]$ ls catnoamd.sql
catnoamd.sql
[oracle@dbsc admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 16
19:46:56 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, OLAP and Real Application Testing
options
SQL> @catnoamd.sql
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
PL/SQL procedure successfully completed.
1 row deleted.
SQL>
SQL> col COMP_NAME format a40
SQL> set lines 133 pages 133
SQL> select comp_name,version,status from
dba_registry;
COMP_NAME VERSION STATUS
----------------------------------------
------------------------------ -----------
Oracle Machine Generated Data 12.1.0.2.0 VALID
OLAP Catalog 11.2.0.4.0 REMOVED
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
Oracle Data Mining 12.1.0.2.0 VALID
15 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Real Application Testing
options
[oracle@dbsc admin]$ cd $ORACLE_HOME
[oracle@dbsc 12.1.0]$ cd rdbms/admin/
[oracle@dbsc admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 16 19:48:13 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, OLAP and Real Application Testing
options
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN
2020-01-16 19:48:20
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>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END
2020-01-16 19:49:01
DOC> The following query reports the number of objects
that have compiled
DOC> with errors.
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>#
OBJECTS WITH ERRORS
-------------------
5
DOC> The following query reports the number of errors
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>#
ERRORS DURING RECOMPILATION
---------------------------
5
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Real Application Testing
options
[oracle@dbsc admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 16
19:47:23 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, OLAP and Real Application Testing
options
SQL> col COMP_NAME format a40
SQL> set lines 133 pages 133
SQL> select comp_name,version,status from
dba_registry;
COMP_NAME VERSION STATUS
----------------------------------------
------------------------------ -----------
Oracle Machine Generated Data 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
Oracle Data Mining 12.1.0.2.0 VALID
14 rows selected.
SQL>
No comments:
Post a Comment