SQL> alter session set container=PDB1 ;
Session altered.
SQL> create table dba_table1 as select * from dba_tables where 1=1 ;
Table created.
SET ARRAYSIZE 1000 COPYCOMMIT 2
copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
2185 rows inserted into DBA_TABLE1.
2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.
SQL> select count(*) from dba_table1 ;
COUNT(*)
----------
4369
SQL>
SET ARRAYSIZE 1000 COPYCOMMIT 2
copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
2185 rows inserted into DBA_TABLE1.
2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.
SQL> select count(*) from dba_table1 ;
COUNT(*)
----------
6554
SQL>
copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;
copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;SQL>
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
2185 rows inserted into DBA_TABLE1.
2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.
SQL> select count(*) from dba_table1 ;
COUNT(*)
----------
8739
===
SQL> COPY FROM system/sys@//192.168.1.120:1521/pdb1.localdomain TO system/sys@//192.168.1.120:1521/pdb1.localdomain APPEND DBA_TABLE1 using select * from dba_tables ;
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
Table DBA_TABLE1 created.
2186 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
2186 rows inserted into DBA_TABLE1.
2186 rows committed into DBA_TABLE1 at system@//192.168.1.120:1521/pdb1.localdomain.
SQL>
Friday, 7 March 2025
Oracle copy command !!!!
Oracle copy command !!!!
==================================================================
Tuesday, 25 February 2025
Find Oracle Objects ...
Find Oracle Objects
============================================
set pagesize 200 linesize 200 set term on feed on --set term off feed off col object_name format a30 col object_type format a30 col owner format a15 col created format a21 col last_ddl_time format a21 col status format a10 select distinct * from (select 'D' con_id ,object_name ,object_type , owner , status , to_char(created,'DD/MM/yyyy hh24:mi:ss') created , to_char(last_ddl_time,'DD/MM/yyyy hh24:mi:ss') last_ddl_time from dba_objects where object_name like upper('%&&uobject%') union all select to_char(con_id) ,object_name ,object_type , owner , status , to_char(created,'DD/MM/yyyy hh24:mi:ss') created , to_char(last_ddl_time,'DD/MM/yyyy hh24:mi:ss') last_ddl_time from cdb_objects where object_name like upper('%&&uobject%') union all select 'v' con_id,name object_name, type object_type, 'SYS' owner, 'FIXED' status, null created, null last_ddl_time from v$fixed_table where name like upper('%&&uobject%') order by object_name ) / undefine uobject
Sunday, 23 February 2025
How to export PDB tables with out password with sys user ?
How to export PDB tables with out password with sys user ?
========================================================================================
export ORACLE_PDB_SID=ANUJGP
expdp \'/ as sysdba\' schemas=ANUJ NOLOGFILE=y ESTIMATE_ONLY=y include=TABLE:"IN('JOB_LIST_143')"
Export: Release 19.0.0.0.0 - Production on Sun Feb 23 15:17:50 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" schemas=ANUJ NOLOGFILE=y ESTIMATE_ONLY=y include=TABLE:IN('JOB_LIST_143')
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "ANUJ"."JOB_LIST_143" 192 KB
Total estimation using BLOCKS method: 192 KB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Feb 23 15:18:09 2025 elapsed 0 00:00:18
******************************************
export ORACLE_PDB_SID=ANUJGP
expdp \'/ as sysdba\' schemas=ANUJ directory=ORACLE_BASE dumpfile=JOB_LIST1.dmp logfile=JOB_LIST1.log include=TABLE:"IN('LIST_143')" compression=all ESTIMATE=STATISTICS metrics=Y JOB_NAME=JOB_LISTEXP
expdp \'/ as sysdba\' schemas=ANUJ directory=ORACLE_BASE dumpfile=JOB_LIST1.dmp logfile=JOB_LIST1.log include=TABLE:"IN('LIST_143')" compression=all ESTIMATE=STATISTICS metrics=Y JOB_NAME=JOB_LISTEXP
Export: Release 19.0.0.0.0 - Production on Sun Feb 23 15:34:49 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."JOB_LISTEXP": "/******** AS SYSDBA" schemas=ANUJ directory=ORACLE_BASE dumpfile=JOB_LIST1.dmp logfile=JOB_LIST1.log include=TABLE:IN('LIST_143') compression=all ESTIMATE=STATISTICS metrics=Y JOB_NAME=JOB_LISTEXP
W-1 Startup took 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
W-1 . estimated "ANUJ"."LIST_143" 5.481 KB
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
W-1 Completed 2 INDEX_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
W-1 Completed 1 TABLE_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
W-1 Completed 1 PROCACT_INSTANCE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
W-1 Completed 1 TABLE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
W-1 Completed 1 INDEX objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
W-1 Completed 1 CONSTRAINT objects in 1 seconds
W-1 . . exported "ANUJ"."LIST_143" 4.820 KB 1 rows in 0 seconds using direct_path
W-1 Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
W-1 Master table "SYS"."JOB_LISTEXP" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.JOB_LISTEXP is:
/u01/app/oracle/JOB_LIST1.dmp
Job "SYS"."JOB_LISTEXP" successfully completed at Sun Feb 23 15:36:15 2025 elapsed 0 00:01:25
=======nohup expdp "'/ as sysdba'" DUMPFILE=DIR:XXX_%U.dmp SCHEMAS=anuj LOGFILE=DIR:anuj.log cluster=N parallel=4 status=10 metrics=Y ESTIMATE=STATISTICS CONSISTENT=Y & ==============
How to export and import Unified Audit Trail from > Oracle 18c
How to export and import Unified Audit Trail from > Oracle 18c ==================================================================
Via old method
exp \"/ as sysdba\" file=expaudtable.dmp log=expaudtable.log tables=sys.aud$https://anuj-singh.blogspot.com/2016/11/oracle-datapump-dir-grant-info.html
expdp \"/ as sysdba\" full=y directory=ORACLE_BASE logfile=unified_audit_trail.dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS
Export: Release 19.0.0.0.0 - Production on Sun Feb 23 14:03:48 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" full=y directory=ORACLE_BASE logfile=unified_audit_trail.dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.078 KB 36 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5177" 204.5 MB 664247 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5986" 106.6 MB 350671 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5831" 84.63 MB 275667 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5347" 40.27 MB 124343 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5513" 30.60 MB 95274 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P181" 41.19 MB 31632 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P6152" 26.15 MB 85764 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5665" 24.40 MB 77046 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3069" 3.388 MB 7075 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P457" 8.178 MB 3558 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3256" 1.510 MB 3023 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4655" 1.249 MB 2205 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5002" 1.159 MB 3171 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3966" 1.178 MB 2198 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4151" 813.7 KB 1985 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4325" 714.2 KB 1261 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4836" 507.6 KB 869 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3796" 483.1 KB 854 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2745" 646.2 KB 434 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4485" 293.0 KB 462 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3630" 218.0 KB 352 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3454" 115.6 KB 149 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2499" 109.2 KB 105 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2912" 84.62 KB 70 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P1987" 67.64 KB 42 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P1335" 65.55 KB 32 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2227" 59.61 KB 19 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P1130" 51.52 KB 2 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 7.242 KB 1 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.585 KB 18 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/unified_audit_trail.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Sun Feb 23 14:07:13 2025 elapsed 0 00:03:18
ls -lthr /u01/app/oracle/unified_audit_trail.dmp
-rw-r----- 1 oracle asmadmin 581M Feb 23 14:07 /u01/app/oracle/unified_audit_trail.dmp
===================
with datapump api
declare
l_datapump_handle NUMBER; -- Data Pump job handle
l_datapump_dir VARCHAR2(20) := 'ORACLE_BASE'; -- Data Pump Directory
l_status varchar2(200); -- Data Pump Status
begin
l_datapump_handle := dbms_datapump.open(operation => 'EXPORT', -- operation = EXPORT, IMPORT, SQL_FILE
job_mode =>'FULL', -- job_mode = FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
job_name => 'AUD FULL EXPORT JOB RUN 002', -- job_name = NULL (default) or: job name (max 30 chars)
version => 'latest'); -- version = COMPATIBLE (default), LATEST (dbversion), a value (11.0.0 or 12)
dbms_datapump.add_file(handle => l_datapump_handle,filename => 'exp_FULL_AUD_%U.dmp',directory => l_datapump_dir);
dbms_datapump.add_file(handle => l_datapump_handle,filename => 'exp_FULL_AUD.log' ,directory => l_datapump_dir ,filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
dbms_datapump.metadata_filter(handle => l_datapump_handle, name => 'EXCLUDE_PATH_EXPR', value => 'IN(''AUDIT_TRAILS'')');
dbms_datapump.set_parameter(l_datapump_handle,'CLIENT_COMMAND','Full Consistent Data Pump Export of AUD Schema HR with PARALLEL 8');
dbms_datapump.set_parameter(l_datapump_handle,'FLASHBACK_TIME','SYSTIMESTAMP');
dbms_datapump.set_parallel(l_datapump_handle,8); -----<<< parallel
dbms_datapump.start_job(handle => l_datapump_handle);
dbms_datapump.wait_for_job(handle => l_datapump_handle,
job_state => l_status );
dbms_output.put_line( l_status );
end;
/
-rw-r--r-- 1 oracle asmadmin 0 Feb 23 14:14 exp_FULL_AUD.log
-rw-r----- 1 oracle asmadmin 16384 Feb 23 14:15 exp_FULL_AUD_07.dmp
-rw-r----- 1 oracle asmadmin 36864 Feb 23 14:15 exp_FULL_AUD_03.dmp
-rw-r----- 1 oracle asmadmin 57344 Feb 23 14:16 exp_FULL_AUD_02.dmp
-rw-r----- 1 oracle asmadmin 20480 Feb 23 14:16 exp_FULL_AUD_04.dmp
-rw-r----- 1 oracle asmadmin 28672 Feb 23 14:16 exp_FULL_AUD_01.dmp
-rw-r----- 1 oracle asmadmin 24576 Feb 23 14:16 exp_FULL_AUD_06.dmp
-rw-r----- 1 oracle asmadmin 16384 Feb 23 14:16 exp_FULL_AUD_05.dmp
Wednesday, 12 February 2025
Oracle 23ai sqlplus ping command
tns file !!!!
cat /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
===
https://www.oracle.com/ie/a/otn/docs/database/oracle-net-23ai-new-features.pdf
on 23ai !!!!!
sqlplus -v
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
new sqlplus ping command
sqlplus -help
Saturday, 8 February 2025
DBMS_SERVICE for Single Instance Databases and status
http://anuj-singh.blogspot.com/2024/12/how-to-create-oracle-database-services.html
edit tnsnames.ora
cd $ORACLE_HOME/network/admin
anujv =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.85.129)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = anuj)
)
)
to Create service
BEGIN
DBMS_SERVICE.create_service(
service_name => 'ANUJ',
network_name => 'ANUJ'
);
END;
/
====
BEGIN
DBMS_SERVICE.start_service(
service_name => 'ANUJ'
);
END;
/
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT name,
network_name
FROM dba_services
ORDER BY 1;
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT name,
network_name
FROM v$active_services
ORDER BY 1;
check the status from both view ..
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT 'ACTIVE' name,
network_name
FROM v$active_services
where 1=1
and NETWORK_NAME is not null
union all
SELECT 'DBA***' name,
network_name
FROM dba_services
where 1=1
and NETWORK_NAME is not null
order by 2
;set linesize 300
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
col PDB_NAME for a20
SELECT distinct 'ACTIVE' name,
CON_ID,
CON_NAME PDB_NAME,
network_name
FROM v$active_services
where 1=1
and NETWORK_NAME is not null
union all
SELECT distinct 'DBA***' name,
b.CON_ID,
PDB_NAME PDB_NAME,
network_name
FROM dba_services,dba_pdbs b
where 1=1
and NETWORK_NAME is not null
and b.PDB_ID=CON_ID
order by 2
;
NAME CON_ID PDB_NAME NETWORK_NAME
------------------------------ ---------- -------------------- ------------------------------
ACTIVE 1 CDB$ROOT vihcdbd8XDB
ACTIVE 1 CDB$ROOT VIHCD8X
DBA*** 2 PDB$SEED vihcdbd8
DBA*** 2 PDB$SEED VIHCD8X
===== lsnrctl services |grep -i -A3 ANUJ sqlplus vihaan1/vihaan1@192.168.85.129:1521/anujv
set linesize 300 pagesize 300
col USERNAME for a20
col MACHINE for a20
col for MACHINE for a20
col PROGRAM for a35
col SERVICE_NAME for a20
col OSUSER for a25
SELECT service_name, inst_id, count(*), username, machine, program, osuser
FROM gv$session
WHERE 1=1
-- and service_name NOT IN ('SYS$BACKGROUND','SYS$USERS')
--AND inst_id=1
GROUP BY service_name, inst_id, username, machine, program, osuser
ORDER BY service_name, inst_id, username, machine, program;
BEGIN DBMS_SERVICE.disconnect_session( service_name => 'ANUJ', disconnect_option => DBMS_SERVICE.immediate ); END; /
Friday, 31 January 2025
Stale Statistics sql
---- check_stale_stats.sql
-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - -
-- NAME: CHECK_STALE_STATS.SQL
-- Execute as SYS as sysdba
-- ----------------------------------------------------------------------------------------------------------------
-- AUTHOR:
-- Raja Ganesh - Oracle Support Services - DataServer Group
-- Copyright 2008, Oracle Corporation
-- -----------------------------------------------------------------------------------------------------------------
-- PURPOSE:
-- This script is an automated way to deal with stale statistics
-- operations that are required to be done as part of manual
-- upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- -------------------------------------------------------------------------------------------------------------------
-- VERSION HISTORY
-- V1 sdixon : added 12.1.0' and added 19c as well to supported versions
--
SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON
DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;
-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
LOOP
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST STALE',OBJLIST=>p_otab);
-- Defining Loop 2 to find number of objects containing stale stats
FOR i in 1 .. p_otab.count
LOOP
IF p_otab(i).objname NOT LIKE 'SYS_%'
AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
'MON_MODS$','TABPART$','HISTGRM$',
'MON_MODS_ALL$',
'HIST_HEAD$','IN $','TAB$',
'WRI$_OPTSTAT_OPR','PUIU$DATA',
'XDB$NLOCKS_CHILD_NAME_IDX',
'XDB$NLOCKS_PARENT_OID_IDX',
'XDB$NLOCKS_RAWTOKEN_IDX', 'XDB$SCHEMA_URL',
'XDBHI_IDX', 'XDB_PK_H_LINK')
THEN
-- Incrementing count for each object found with statle stats
mcount := mcount + 1;
END IF;
-- End of Loop 2
END LOOP;
-- Displays no stale statistics, if coun is 0
IF mcount!=0
THEN
-- Displays Schema with stale stats if count is greater than 0
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- '|| x.schema || ' schema contains stale statistics use the following to gather the statistics '||'--');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
-- Displays Command to be executed if schema with stale statistics is found depending on the version.
IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0','11.2.0','12.1.0','12.2.0','19.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
ELSE
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Version is '||p_version);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in '|| x.schema || ' schema.');
DBMS_OUTPUT.PUT_LINE(chr(13));
END IF;
-- Reset count to 0.
mcount := 0;
-- End of Loop 1
END LOOP;
END;
/
SET FEEDBACK ON
-- - - - - - - - - - - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - - - - - - - - - - - SET SERVEROUTPUT ON DECLARE ObjList dbms_stats.ObjectTab; BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname=> 'ANUJ', objlist=> ObjList, options=> 'LIST STALE'); FOR i in ObjList.FIRST..ObjList.LAST LOOP dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname); END LOOP; END; /
-
Subscribe to:
Posts (Atom)
