To find total size of the database

clear breaks clear computes clear columns set pagesize 50 set linesize 120 set heading on column tablespace_name heading 'Tablespace' justify left format a20 truncated column tbsize heading 'Size(Mb) ' justify left format 9,999,999.99 column tbused heading 'Used(Mb) ' justify right format 9,999,999.99 column tbfree heading 'Free(Mb) ' justify right format 9,999,999.99 column tbusedpct heading 'Used % ' justify left format a8 column tbfreepct heading 'Free % ' justify left format a8 break on report compute sum label 'Totals:' of tbsize tbused tbfree on report select t.tablespace_name, round(a.bytes,2) tbsize, nvl(round(c.bytes,2),'0') tbfree, nvl(round(b.bytes,2),'0') tbused, to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) '%' tbusedpct, to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) '%' tbfreepct from dba_tablespaces t, (select tablespace_name, round(sum(bytes)/1024/1024,2) bytes from dba_data_files group by tablespace_name union select tablespace_name, round(sum(bytes)/1024/1024,2) bytes from dba_temp_files group by tablespace_name ) a, (select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes from dba_segments e group by e.tablespace_name union select tablespace_name, sum(max_size) bytes from v$sort_segment group by tablespace_name) b, (select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes from dba_free_space f group by f.tablespace_name union select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes from dba_temp_files tmp, v$sort_segment sort where tmp.tablespace_name = sort.tablespace_name group by tmp.tablespace_name) c where t.tablespace_name = a.tablespace_name (+) and t.tablespace_name = b.tablespace_name (+) and t.tablespace_name = c.tablespace_name (+) order by t.tablespace_name /

The simplest query for checking what’s happening in a database

select event, state, count(*) from v$session_wait group by event, state order by 3 desc;SQL> select sql_hash_value, count(*) from v$sessionwhere status = 'ACTIVE' group by sql_hash_value order by 2 desc;select sql_text,users_executing from v$sql where hash_value = &hash_value;

Starting Up with a Non-Default Server Parameter File

Create a one-line text initialization parameter file that contains only the SPFILEparameter. The value of the parameter is the non-default server parameter filelocation.For example, create a text initialization parameter file/u01/oracle/dbs/spf_init.ora that contains only the following parameter:SPFILE = /u01/oracle/dbs/test_spfile.oraSTARTUP PFILE = /u01/oracle/dbs/spf_init.ora

How to backup the archivelogs which are not backed up already

rman > BACKUP ARCHIVELOG UNTIL TIME 'SYSDATE' NOT BACKED up;

Migrating to Automatic Undo Management

DECLAREutbsiz_in_MB NUMBER;BEGINutbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;end;/The function returns the sizing information directly.

Create Schema stmt

Example: CREATE SCHEMA AUTHORIZATION scottCREATE TABLE dept (deptno NUMBER(3,0) PRIMARY KEY,dname VARCHAR2(15),loc VARCHAR2(25))CREATE TABLE emp (empno NUMBER(5,0) PRIMARY KEY,ename VARCHAR2(15) NOT NULL,job VARCHAR2(10),13-2 Oracle Database Administrator’s Guidemgr NUMBER(5,0),hiredate DATE DEFAULT (sysdate),sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(3,0) NOT NULLCONSTRAINT dept_fkey REFERENCES dept)CREATE VIEW sales_staff ASSELECT empno, ename, sal, commFROM empWHERE deptno = 30WITH CHECK OPTION CONSTRAINT sales_staff_cnstGRANT SELECT ON sales_staff TO human_resources;Specifically, the CREATE SCHEMA statement can include only CREATE TABLE,CREATE VIEW, and GRANT statements

Disabling the Recyclebin at session level and system level

alter session set recyclebin = off;
alter system set recyclebin = off

how to know whether 32-bit or 64-bit oracle s/w installed

select * from v$version;

max_enabled_roles error in all the versions including 10.2.0.3

Today I got a mail from one of the end user that he is not able to login and he is getting the ORA-28031 error while logging.As the parameter is already set to 150 and we can not increase anymore. After doing a little search I came to know that this is a bug and resolved in 10.2.0.4.So as a work around is set role none to the user who is getting the error by usingalter user username default role none;But I am not sure is this was the way to resolve the issue.

How to find TOP SQL in a particular period.

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST, (SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIMEFROM DBA_HIST_SQLSTAT DHSSWHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOTWHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/08/2008','MM/DD/YYYY')AND END_INTERVAL_TIME<=TO_DATE('09/09/2008','MM/DD/YYYY'))GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;

How to do database characterset migration

The link http://sabdarsyed.blogspot.com/2008/09/how-we-used-oracle-csscan-utility-and.html

Using metadata

pl/sql block which helps us in getting DDL easily for many database objects.
example:
begin
for objects in
(select object_name,owner,object_type
from dba_objects
where owner=’&schema_name’ and object_type='INDEX'
)
loop
dbms_output.put_line(dbms_metadata.get_ddl(objects.object_type,objects.object_name,objects.owner));
end loop;

Finding 64-bit or 32-bit at OS level

getconf KERNEL_BITS