Wednesday 14 August 2013

Fix-ORA-01432: public synonym to be dropped does not exist.



Solution:


First drop the existing Perfstat user before running the spcreate again.

There should be a script called spdrop.sql which would do this for you.

sql>@$ORACLE_HOME/rdbms/admin/spdrop.sql 

Then run spcreate to create the statspack environment.

sql>@$ORACLE_HOME/rdbms/admin/spcreate.sql

once created login to perfstat and run the execute statspack.snap at different intervals and run spreport to see the statspack report.

Tuesday 13 August 2013

How to resize archive logs in oracle

Resize archive logs

Modifying the redo logs:
------------------------
SQL> select group#, bytes, status from v$log;

GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 INACTIVE
2 52428800 CURRENT
3 52428800 INACTIVE

SQL> select group#, member,status from v$logfile;

GROUP# MEMBER
--------------------------------------------------------------------------------
3 /TESTDATA/TEST/testdata/redo03.log
2 /TESTDATA/TEST/testdata/redo02.log
1 /TESTDATA/TEST/testdata/redo01.log


SQL> alter database add logfile group 4 '/TESTDATA/TEST/testdata/redo04.log' size 125M;

alter database add logfile group 5 '/TESTDATA/TEST/testdata/redo05.log' size 125M; 

alter database add logfile group 6 '/TESTDATA/TEST/testdata/redo06.log' size 125M; 


sql>select group#, bytes, status from v$log;

Switch until we are into log group 4

alter database drop logfile group 1; 
alter database drop logfile group 2; 
alter database drop logfile group 3; 


remove the files at OS level

How to find out database growth in a year.

Script:

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024
"Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

Output:
Month       Meg
-------------- ----------
2012 November 143.8125
2012 October       100
2013 March     1024