gather_stats script

$ cat gather_stat.sql

set timing on
exec dbms_stats.gather_table_stats ('SCOTT', 'EMP', cascade=>TRUE);
exec dbms_stats.gather_table_stats ('SCOTT', 'DEPT', cascade=>TRUE);
alter system flush shared_pool;

$ cat gather.sh

#!/bin/bash
. /home/oracle/.bash_profile
#-----------Start [ Do not Change Configuration Without DBA Approval ]---------
#user of the database
user_name=system
#password of the user
pwd=xxxxxxx
#netstring(service name or db_name)
netstr=pdb1
netstr1=pdb2
/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus $user_name/$pwd@$netstr << EOF
@/backup/backup/gather/gather_stat.sql
conn $user_name/$pwd@$netstr1
set echo on;
show parameter instance_name;
alter system flush shared_pool;
exit;
EOF

$cat gather_cron.log

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 24 20:05:01 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string pdb1
SQL>; set time on;
20:05:01 SQL> set timing on;
20:05:01 SQL> exec dbms_stats.gather_table_stats ('SCOTT', 'EMP', cascade=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:46.35
20:05:48 SQL> exec dbms_stats.gather_table_stats ('SCOTT', 'DEPT', cascade=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.76
20:05:58 SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.14
20:05:58 SQL> Connected.
20:05:58 SQL>
NAME TYPE VALUE
------------------------------------
instance_name string pdb2
20:05:58 SQL>
System altered.
Elapsed: 00:00:00.06
20:05:59 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

crontab -l

30 20 * * * /backup/backup/gather/gather.sh > /backup/backup/gather/gather_cron.log 2>&1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s