Tablespace Size

 SELECT /* + RULE */  df.tablespace_name "Tablespace", 
  df.bytes / (1024 * 1024) "Size (MB)", 
  SUM(fs.bytes) / (1024 * 1024) "Free (MB)", 
  Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", 
  Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" 
  FROM dba_free_space fs, 
 (SELECT tablespace_name,SUM(bytes) bytes 
  FROM dba_data_files 
  GROUP BY tablespace_name) df 
WHERE fs.tablespace_name (+)  = df.tablespace_name 
 GROUP BY df.tablespace_name,df.bytes 
UNION ALL 
SELECT /* + RULE */ df.tablespace_name tspace, 
 fs.bytes / (1024 * 1024), 
 SUM(df.bytes_free) / (1024 * 1024), 
 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), 
 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) 
  FROM dba_temp_files fs, 
  (SELECT tablespace_name,bytes_free,bytes_used 
    FROM v$temp_space_header 
  GROUP BY tablespace_name,bytes_free,bytes_used) df 
 WHERE fs.tablespace_name (+)  = df.tablespace_name 
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used 
 ORDER BY 4 DESC; 

col SEGMENT_NAME format a20

col SEGMENT_TYPE format a15;

col TABLESPACE_NAME format a25;

SELECT * FROM

(select 

 SEGMENT_NAME, 

 SEGMENT_TYPE, 

 BYTES/1024/1024/1024 GB, 

 TABLESPACE_NAME 

from 

 dba_segments

order by 3 desc ) WHERE

ROWNUM <= 40 AND TABLESPACE_NAME='TABLESPACE_NAME';

AWR Reports

select snap_id, begin_interval_time,end_interval_time from dba_hist_snapshot;

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

How to find the best Interval to Generate your AWR Reports?

To Single Instance this can be done using bellow query:

SET LINESIZE 200
SET PAGESIZE 200
UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999

SELECT
    s.startup_time,
    di.instance_name,
    MIN(snap_id) snap_start,
    MAX(snap_id) snap_end,
    MIN(end_interval_time) start_interval,
    MAX(end_interval_time) end_interval,
    EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Days(s) '
    || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Hour(s) '
    || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Minute(s) ' range_interval,
    MAX(snap_id) - MIN(snap_id) qtd_snaps
FROM
    dba_hist_snapshot s,
    dba_hist_database_instance di
WHERE
    di.dbid = s.dbid
    AND   di.instance_number = s.instance_number
    AND   end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy'
) - (&num_days - 1) )
GROUP BY
    s.startup_time,
    di.instance_name
ORDER BY
    startup_time ASC;

Script to Generate a CREATE DATABASE command from an existing database

Execution Environment:
     <SQL, SQL*Plus, iSQL*Plus>

Access Privileges:
     Requires connect as sysdba privileges

Usage:
     sqlplus /nolog
     SQL> connect sys/<password> as sysdba
     SQL> @gencrdb

Instructions:

Copy the scripts into a file named gencrdb.sql. Execute the script from sqlplus 
connected as sysdba.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named crdb.sql.
This file can be used to create a database with the same initial configuration
of the one in which the script was executed..

Description
The included script will generate a CREATE DATABASE command for an existing
database.  It can be used to recreate the database in connection with a full
export and import, used for reference etc.

  o  This will also work in an OPS or RAC environment, you will need to
     create the logfiles for additional threads manually.

  o  You need to be connected as SYSDBA or have select privileges
     on all referenced dictionary tables / views.



References
[Include references to  FAQ, Troubleshooting guide, and Current issues
Articles from Top Tech Docs or other relevant references.]

Script
-- gencrdb.sql
--
-- Generate a CREATE DATABASE command from an existing database.
-- (C) 2002 Oracle Corporation, written by Harm ten Napel
-- This script will work from 8i onwards.
--
-- DISCLAIMER
--
-- This script is provided for educational purposes only. It is NOT supported
-- by Oracle World Wide Technical Support.  The script has been tested and
-- appears to work as intended.  However, you should always test any script
-- before relying on it. 
--
spool crdb.sql
set pages 1000
set head off
set termout off
set feedback off
set newpage none
set serveroutput on      
select 'CREATE DATABASE '||name text from v$database;
-- select 'CONTROLFILE REUSE' from dual;  -- optional
select 'LOGFILE' from dual;
declare
    print_var varchar2(200);
    cursor c1 is select member from gv$logfile where inst_id = 1
    order by group#;
    logfile gv$logfile.member%TYPE;
    cursor c2 is select bytes from gv$log where inst_id = 1
    order by group#;
    bytes number;
    lsize varchar2(30);    
begin
    open c1;
    open c2;
    for record in (
    select group#, count(*) members from gv$logfile where inst_id = 1
    group by group#) loop
         dbms_output.put_line(print_var);
         fetch c2 into bytes;
         if mod(bytes,1024) = 0 then
            if mod(bytes,1024*1024) = 0 then
               lsize := to_char(bytes/(1024*1024))||'M';
            else
               lsize := to_char(bytes/1024)||'K';
            end if;
         else
            lsize := to_char(bytes);
         end if;
         lsize := lsize||',';
         if record.members > 1 then
           fetch c1 into logfile;
           print_var := 'GROUP '||record.group#||' (';
           dbms_output.put_line(print_var);
           print_var := ''''||logfile||''''||',';
           for i in 2..record.members loop  
               fetch c1 into logfile;
               dbms_output.put_line(print_var);
               print_var := ''''||logfile||''''||',';
           end loop;
           print_var := rtrim(print_var,',');
           dbms_output.put_line(print_var);
           print_var := ') SIZE '||lsize;
         else
           fetch c1 into logfile;
           print_var := 'GROUP '||record.group#||' '''||
                        logfile||''''||' SIZE '||lsize;
         end if;
    end loop;
    close c1;
    close c2;
    print_var := rtrim(print_var,',');
    dbms_output.put_line(print_var);
end;
/
select 'MAXLOGFILES '||RECORDS_TOTAL from v$controlfile_record_section
        where type = 'REDO LOG';
select 'MAXLOGMEMBERS '||dimlm from sys.x$kccdi;
select 'MAXDATAFILES '||RECORDS_TOTAL from v$controlfile_record_section
        where type = 'DATAFILE';
select 'MAXINSTANCES '||RECORDS_TOTAL from v$controlfile_record_section
        where type = 'DATABASE';
select 'MAXLOGHISTORY '||RECORDS_TOTAL from v$controlfile_record_section
        where type = 'LOG HISTORY'; 
select log_mode from v$database;
select 'CHARACTER SET '||value from v$nls_parameters
     where parameter = 'NLS_CHARACTERSET';
select 'NATIONAL CHARACTER SET '||value from v$nls_parameters
     where parameter = 'NLS_NCHAR_CHARACTERSET';          
select 'DATAFILE' from dual;
declare
   cursor c1 is select * from dba_data_files
   where tablespace_name = 'SYSTEM'  order by file_id;
   datafile dba_data_files%ROWTYPE;
   print_datafile dba_data_files.file_name%TYPE;
begin
   open c1;
   fetch c1 into datafile;
   -- there is always 1 datafile
   print_datafile := ''''||datafile.file_name||
   ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
   loop
        fetch c1 into datafile;
        if c1%NOTFOUND then
           -- strip the comma and print the last datafile
           print_datafile := rtrim(print_datafile,',');
           dbms_output.put_line(print_datafile);
           exit;
        else
            -- print the previous datafile and prepare the next
            dbms_output.put_line(print_datafile);
            print_datafile := ''''||datafile.file_name||
            ''' SIZE '||ceil(datafile.bytes/(1024*1024))||' M,';
        end if;
   end loop;     
end;
/             
select ';' from dual;
spool off
-- end script

Data dictionary views for managing users

DBA_USERS

DBA_ROLES

DBA_COL_PRIVS

DBA_ROLE_PRIVS

DBA_SYS_PRIVS

DBA_TAB_PRIVS

ROLE_ROLE_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

SESSION_PRIVS

SESSION_ROLES

Find Blocks in the Buffer cache

Below is the query to find how many blocks  for each segment are currently in the buffer Cache.

RAC Database:

select o.owner, o.object_name,v.inst_id, count(*) number_of_blocks
from dba_objects o, gv$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name,v.inst_id
order by o.object_name,v.inst_id,count(*);

Non-RAC Database:

select o.owner, o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name
order by o.object_name,count(*);

Excluding Tablespace from RMAN Backup

You have a tablespace with test data that you don't need to backup. You can exclude such tablespaces from a full backup of the database.


Below command shows the list of tablespaces that are already configured to be excluded from backups:

RMAN> show exclude;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TEST are:

RMAN configuration has no stored or default parameters

Use following command to exclude tablespace from full database backup.

RMAN> configure exclude  for tablespace users;

Tablespace USERS will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored


To confirm that the tablespace is excluded RMAN backup, run the below command

RMAN>  show exclude;

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';


Now, if you want to include a previously excluded tablespace in your backup and that is done by using the following command.

RMAN> configure exclude  for tablespace users clear;

Tablespace USERS will be included in future whole database backups

old RMAN configuration parameters are successfully deleted

RMAN> show exclude;

RMAN configuration parameters for database with db_unique_name TEST are:

RMAN configuration has no stored or default parameters

Now, if you want include all tablespaces that are configured in "exclude tablespace",  You can use the 'noexclude' option as part of a backup database command.

RMAN>backup database noexclude;

Find user's IP address

select sid, machine,UTL_INADDR.get_host_address
substr(machine,instr(machine,'\')+1))ip from v$session
where type='USER' and username is not null order by sid;

Drop Database using RMAN

Connect to a target database and make sure that the database is in "mount exclusive" state and not open. You need to start in the RESTRICT mode.

RMAN> connect target /

connected to target database: TEST (DBID=233344476, not open)

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

using target database control file instead of recovery catalog

sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "TEST" and DBID is 233344476

Do you really want to drop all backups and the database (enter YES or NO)? YES

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=421 device type=DISK

specification does not match any backup in the repository

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=421 device type=DISK

specification does not match any datafile copy in the repository

specification does not match any control file copy in the repository

specification does not match any control file copy in the repository

specification does not match any archived log in the repository

database name is "TEST" and DBID is 233344476

database dropped

Database Size

For Non-RAC database:

  select DATA.TOTAL/1024/1024 "DataFile Size Mb",
  LOG.TOTAL/1024/1024 "Redo Log Size Mb",
  CONTROL.TOTAL/1024/1024 "Control File Size Mb",
  (DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
  (select sum(a.bytes) TOTAL from dba_data_files a) DATA,
  (select sum(b.bytes) TOTAL from v$log b) LOG,
  (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;


For RAC database:

select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from gv$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

OS Specific Commands

To Find Swap, RAM, and OS Version

OS	SWAP	RAM	OS VERSION
AIX	/usr/sbin/lsps -a	/usr/sbin/lsattr -HE -l sys0 -a realmem	oslevel
HP PA-RISC	swapinfo -a	grep "Physical:" /var/adm/syslog/syslog.log	uname -a
HP Itanium	swapinfo -a	/usr/contrib/bin/machinfo | grep -i Memory	uname -a
Tru64	swapon -s	vmstat -P	/usr/sbin/sizer -v
Solaris	swap -l	/usr/sbin/prtconf | grep -i memory	uname -r
Linux	free	free	uname -a
Mac OS X	# df -h /	# /usr/sbin/system_profiler SPHardwareDataType | grep Memory	# sw_vers

To Determine the Unix Kernel Parameters

OS	LOCATION	COMMAND TO SEARCH
AIX	automatically configured	do 'env' for LINK_CNTRL, To determine if AIX is 64 bit enabled do 'genkex | grep 64' or 'genkex | grep call'
HP	/stand/system or use SAM -->Kernel Configuration	/etc/sysdef, /usr/sbin/kmtune (kmtune desupported in 11.31, use /usr/sbin/kctune -v), or /usr/sbin/kcweb -F
Tru64	/etc/sysconfigtab	/sbin/sysconfig -q ipc or /sbin/sysconfig -q vm or /sbin/sysconfig -q proc
Solaris	/etc/system	"/etc/sysdef | grep SHM" or "/etc/sysdef | grep SEM"
Linux	/usr/src/linux/include/asm/shmparam.h /usr/src/linux/include/linux/sem.h /proc/sys/kernel/sem /proc/sys/kernel/shmall /proc/sys/kernel/shmmax /proc/sys/kernel/shmmni	ipcs -lms
Mac OS X	/etc/sysctl.conf	"# /usr/sbin/sysctl -a | grep "

O/S Software Installed Packages and Patches

OS	COMMAND FOR PACKAGES	COMMAND FOR PATCHES
AIX	lslpp -w | grep -i "software title" (applies to APARs and PTFs)	/usr/sbin/instfix -ik patch number
HP	Prior to 11: /usr/sbin/swlist -lproduct PH\* hp-ux 11 and after: * /usr/sbin/swlist -l patch \*\.*,c=patch * /usr/contrib/bin/show_patches (from patch PHCO_19550)	/usr/sbin/swlist -l fileset | grep -i
Solaris	/bin/pkginfo -l | grep -i "software title"	/bin/showrev -p
Tru64	/usr/sbin/setld -i | grep -i "software title"	/usr/sbin/setld -i | more For patchkits: /usr/sbin/dupatch -track -type kit
Linux	To see if a particular RPM is installed (without the architectur): $ rpm -qa | grep "package name" To see if a particular RPM is installed (with the architectur): $ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" | grep "package name" To see what RPM provided a particular object/library: $ rpm -q --whatprovides --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" "full path/library name" To see the contents listing of an RPM: $ rpm -ql "RPM name" For example: $ rpm -ql compat-libstdc++-33-3.2.3-47.3.ppc /usr/lib/libstdc++.so.5 /usr/lib/libstdc++.so.5.0.7
Mac OS X	to be supplied later	to be supplied later

To Check if an OS is 64 bit Capable or not

OS	COMMAND	RESULTS
Aix	lslpp -L | grep 64bit	It should return "bos.64bit"
HP	getconf KERNEL_BITS	It should return "64"
Solaris	/bin/isainfo -kv	If the isainfo command does not exist it is not 64-bit. It should return "64-bit sparcv9 kernel modules"

Relationship between Init.ora parameters and Kernel parameters

The following table documents Unix and Linux kernel parameters that should be monitored and possibly increased after changes are made to the related init.ora parameter. Please check your Operating System document for specific details on the parameter changes.

      Init.ora Parameter                    Kernel Parameter

  db_block_buffers                           shmmax, shmall

 db_files(maxdatafiles)                    nfile, maxfiles

 large_pool_size                              shmmax, shmall

 log_buffer                                      shmmax, shmall

 processes                                      nproc, semmsl, semmns

 shared_pool_size                            shmmax, shmall

Lock Account

 set echo on
spool /home/admin/lockAccount.log
BEGIN
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
 LOOP
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
  execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
 END LOOP;
END;
/
spool off