How to write script in unix

[meenakumar]

Hi,
I need to write a script to find out all the sys objects for which database already had statistics available(in unix).Can someone pls help? Thanks.

[hrishy]

Hi Meena

try somethin like this

thats attached in my attachement

Read up on unix here document thats the key to make shell talk to oracle

[meenakumar]

Hi,
Thanks for reply,I know how to write script in shell unix.My question is how to generate report for all the sys object for which database already had stat generated?Which V$ or DBMS package.Any ideas.Thanks

[KenEwald]

Hi,
I need to write a script to find out all the sys objects for which database already had statistics available(in unix).Can someone pls help? Thanks.

Meena,

Why?

[meenakumar]

somebody ask this question to me in an interview.

[hrishy]

Hi

You can store it in your own table

Dbms_Stats.Create_Stat_Table (
ownname => 'hrishy',
stattab => 'hrishy',
tblspace => 'hrishy');

BEGIN
Dbms_Stats.Gather_System_Stats(
interval => 1440,
stattab => 'hrishy',
statid => 'meenas-request');
END;


You can also do
execute dbms_stats.gather_system_stats('Start');
-- some time delay while the database is under a typical workload
execute dbms_stats.gather_system_stats('Stop');

you can query a table (owned by the SYS schema) called aux_stats$. After gathering system statistics,

select	pname, pval1 
from 	sys.aux_stats$ 
where 	sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              564
MAXTHR                           13899776
MBRC                                    6
MREADTIM                           10.496
SLAVETHR                           182272
SREADTIM                            1.468

regards
Hrishy

[WilliamR]

I still don't see what Unix has to do with it. What difference would it make to stats what OS platform you were on? You might as well say "in Estonia".

[KenEwald]

You're talking about running a SQL script from a Unix shell script, right?

And do you want to know what the shell, SQL script, or both should look like?

You're not going to get Oracle object stats directly from the OS.

Did you get the job?

-Ken