nohup sql script

$ nohup sqlplus system/sys @hwm2.sql>hwm2.txt 2>&1 &


nohup sqlplus '/ as sysdba' @stats.sql &

cat stats.sql

spool stats.spool
set time on timing on
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
prompt dbms_stats.gather_dictionary_stats
EXECUTE dbms_stats.gather_dictionary_stats;

prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
spool off
exit

============

--  stats1.sql

spool stats1.spool

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

set time on timing on feedback on 

select sysdate from dual;

set echo on feedback on time on timing on verify on

-- show con_name;

set pages 300 lines 350

col open_mode for a10

col host_name for a10

col database_role for a15

col db_unique_name for a10

col controlfile_type for a10

col database_role for a17

col host_name for a35

col open_mode for a20

select name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "db up time" from v$database,gv$instance;

SET VERIFY OFF

select sysdate from dual;

prompt ========================================

prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()

prompt ========================================

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();

prompt ========================================

prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')

prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');

prompt ========================================

prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS')

prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

prompt ========================================

prompt DBMS_STATS.GATHER_DICTIONARY_STATS

prompt ========================================

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();

select sysdate from dual;

spool off

======================

statsx.sql

spool stats1.spool
alter session set nls_date_format='dd-MON-YYYY hh24:mi';


set time on timing on feedback on
select sysdate from dual;
set echo on feedback on time on timing on verify on

-- show con_name;
set pages 300 lines 350
col open_mode for a10
col host_name for a10
col database_role for a15
col db_unique_name for a10
col controlfile_type for a10
col database_role for a17
col host_name for a35
col open_mode for a20
select distinct d.name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging,
startup_time "db up time" from v$database d ,gv$instance i
--,gv$pdbs p
;


SET VERIFY OFF

select sysdate from dual;


prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()
prompt ========================================

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM', Degree => 8);


prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS' , Degree => 8)
prompt ========================================

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', Degree => 8);

prompt ========================================
prompt DBMS_STATS.GATHER_DICTIONARY_STATS
prompt ========================================

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');
exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');
exec dbms_stats.lock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL');

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS(Degree => 8);

exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');
exec dbms_stats.unlock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');
exec dbms_stats.unlock_table_stats('SYS', 'X$UNIFIED_AUDIT_TRAIL');

select sysdate from dual;

===========

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




$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-d /home/oracle/scripts \
-b MyScript \
-l /home/oracle/logs \
-u mustafa/mustafa \
-z mydbserver.mydomain:1521/CDBX \
-n 2 \
-c 'PDB1 PDB2 PDB3 PDB4 PDB5 PDB6 PDB7 PDB8'
myfile.sql



$ORACLE_HOME/perl/bin/perl => PERL binary to run catcon.pl
$ORACLE_HOME/rdbms/admin/catcon.pl => catcon.pl path
-d /home/oracle => directory where script is located.
-b statsx => Prefix for the log files so you can separate them from other scripts logs.
statsx.sql => actual script name

to check 

alter session set nls_date_format='DD-MM-YYYY';
col last_analyzed for a13
set termout off  trimspool off feedback off
--spool dictionary_statistics

prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner in ('SYS')
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1 DESC;

prompt 'Statistics for SYSTEM tables'

SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner in ('SYSTEM')
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1 DESC;



prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1 DESC;

--spool off

cat gather.sh
#!/bin/ksh
#
export ORACLE_SID=vihcdbd8
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d /home/oracle -l /home/oracle/logs -b statsx statsx.sql

 nohup ./gather.sh &

in log dir 

find . | xargs grep 'Container:' -sl
./statsx0.log
./statsx1.log
./statsx2.log
./statsx3.log

or

 find . | xargs grep 'Container:' -sl | xargs cat {} \;

check above log files