Some more question???

1)When looking at V$SYSSAT, you see that sorts(disk) is high. Is this bad or good? If bad - how do you correct?
2)How do you know a table needed to be reorganized? Index?
3)What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
4)How can you find out how many users are currently logged into the database? How can you find their operating system ID?
5)If a user can not access the database, how do you help him?
6)How to find the SQL statement causing a lock?
7)How can you determine the space left in a file system? (UNIX)?
8)What is “snapshot too old” error? What’s the reason? How to solve the problem?
9)What does this error mean: ORA- 12203: TNS: Unable to connect to destination?
What steps would you take to diagnose and solve this error?
10)If the database crashes, which files do you need to recover it?
11)How do you know a table needed to be reorganized? Index?

Thanks in advance
Jung

For your Questions the answers are as follows.

I)
The ratio of disk sorts to Memory sorts should be less than 15%.

For this increase the size of of SORT_AREA_SIZE in init.ora parameter file.

The formula should be like this.

SQL>select disk.value "DISK",mem.value "MEM",
(disk.value/mem.value)*100 "RATIO"
from V$SYSSTAT mem,V$SYSSTAT disk where mem.value= 'sorts (memory)' and disk.name='sorts (disk)';

Definitely you have to see that ratio is not above 15%.

II)

You have to ANALYZE the tables and query the tables DBA_INDEXES etc.

III)

There are 3 components of shared pool.Which determines the shared_pool_size.Library cache,data dictionary cache and UGA.

You have to use the V$LIBRARYCACHE and V$ROWCACHE
to know the ratio or statistics limit of three important factors based on which you can increase the size of the SHARED_POOL_SIZE.

They are

i.The gethitratio of Librarycache should be above 90%
ii.The pinhitratio on the library cache should not be greater than 1%.
iii.The Gethitratio on the dictionary cache should be less than 15%.

Use the 2 views i have mentioned above .Which has columns as mentioned in the 3 points.check it.If it violates then you can increase the size of Shared_pool_size.

There are also several other factors you have to go in to before you increase them.

IV)You can write a script for this if you want it print your mail id in this forum i will sen it across.

V)If he forgets the password then as follows

The DBA will reset the password.This can also be managed by oracle OEM.

If he is unable to create objects when max quota limit has been attained then DBA will increase the size.

VI)
Again for this the a simple scipt as follows

===============
spool locking3.rslt

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "OWNER" on "TABLE NAME" on "INDEX NAME";

column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8

select A.username "Waiting User",
A.osuser "OS User",
A.sid "SID",
B.username "Holding User",
B.osuser "OS User",
B.sid "SID"
from v$session A,
v$session B,
dba_waiters C
where C.waiting_session = A.sid and
C.holding_session = B.sid
order by
B.waiting_session,
B.holding_session;

set echo off;
spool off;
set long 80;
commit;
=====================

VII)UNIX a Good OS i dont have much exposure.


VIII) ORA_01555 or called as snapshot too old error.

If a Query fails with the above message then the rollback image needed for the read consistency has been probably over written by an active transaction(current transaction-writing the old transaction details).

To resove create bigger RBS or More RBS.

IX)Under the oracle doc's ite been very clearly given.There are several reasons and solution given.It may be due to invalid net service name etc.


X)If the db crashes based on the file type you have to do.

If the crash has happened due to power failure instance recovery is automatically done.

But due to media failure you have to see log file missing or datafile missing or control file lost or what ??? go to docs again and study that in detail.

XI)Its again you can do this from dtat dictionay tables or collect ststistics via scripts.Out your mail id in this thread to post the same.

Dear Jung i would really appreciate your keen interest in knowing many things but at the same i would like to advice kindly get in to Oracle docs first.

I am sure many of the things like solutions for practical problem wont be available there for this forum will be great helpful to you.

Cheers

padmam

[Edited by padmam on 05-02-2001 at 11:55 AM]

You are the MAN!!!

Best Regards
jung