To check the instance details in real application clusters

 To check the instance details in real application clusters

select INST_NAME,INST_NUMBER from gv$active_instances;

Rman Perfromance views

Rman Performance views :

select sid, serial#, username , status, inst_id, PROGRAM,to_char(LOGON_TIME,'DD-MM-YY HH24:MI S') from gv$session
where sid='&sid' order by 7;  2
Enter value for sid: 259
old   2: where sid='&sid' order by 7
new   2: where sid='259' order by 7

COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM   V$PROCESS p, V$SESSION s
WHERE  p.ADDR = s.PADDR
AND    CLIENT_INFO LIKE 'rman%'
/

COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM   V$PROCESS p, V$SESSION s
WHERE  p.ADDR = s.PADDR
AND    CLIENT_INFO LIKE 'rman%'
/ 

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM   V$SESSION_LONGOPS
WHERE  OPNAME LIKE 'RMAN%'
AND    OPNAME NOT LIKE '%aggregate%'
AND    TOTALWORK != 0
AND    SOFAR <> TOTALWORK; 

ASM

select  name
,       group_number
,       disk_number
,       total_mb
,       free_mb
from    v$asm_disk
order   by group_number
/

IN DETAIL 

Back in 2009 I posted a script which I found very useful to review ASM disks. I gave that post the low-key title of The ASM script of all ASM scripts. Now that script has been improved I have to go a bit further with the hyperbole and we have the The Mother of all ASM scripts.  If it ever gets improved then the next post will just be called ‘Who’s the Daddy’.
I have been using the current script across all our systems for the last 3 years and I find it very useful, a colleague, Allan Webster, has added a couple of improvements and it is now better than before.
The improvements show current disk I/O statistics and a breakdown of the types of files in each disk group and the total sizes of that filetype. The I/O statistics are useful when you have a lot of databases, many of which are test and development and so you do not look at them as that often. It just gives a quick overview that allows you to get a feel if anything is wrong and to see what the system is actually doing. There are also a few comments at the beginning defining the various ASM views available.

REM ASM views:

REM VIEW            |ASM INSTANCE                                     |DB INSTANCE

REM ----------------------------------------------------------------------------------------------------------

REM V$ASM_DISKGROUP |Describes a disk group (number, name, size       |Contains one row for every open ASM

REM                 |related info, state, and redundancy type)        |disk in the DB instance.

REM V$ASM_CLIENT    |Identifies databases using disk groups           |Contains no rows.

REM                 |managed by the ASM instance.                     |

REM V$ASM_DISK      |Contains one row for every disk discovered       |Contains rows only for disks in the

REM                 |by the ASM instance, including disks that        |disk groups in use by that DB instance.

REM                 |are not part of any disk group.                  |

REM V$ASM_FILE      |Contains one row for every ASM file in every     |Contains rows only for files that are

REM                 |disk group mounted by the ASM instance.          |currently open in the DB instance.

REM V$ASM_TEMPLATE  |Contains one row for every template present in   |Contains no rows.

REM                 |every disk group mounted by the ASM instance.    |

REM V$ASM_ALIAS     |Contains one row for every alias present in      |Contains no rows.

REM                 |every disk group mounted by the ASM instance.    |

REM v$ASM_OPERATION |Contains one row for every active ASM long       |Contains no rows.

REM                 |running operation executing in the ASM instance. |

set wrap off

set lines 155 pages 9999

col "Group Name" for a6    Head "Group|Name"

col "Disk Name"  for a10

col "State"      for a10

col "Type"       for a10   Head "Diskgroup|Redundancy"

col "Total GB"   for 9,990 Head "Total|GB"

col "Free GB"    for 9,990 Head "Free|GB"

col "Imbalance"  for 99.9  Head "Percent|Imbalance"

col "Variance"   for 99.9  Head "Percent|Disk Size|Variance"

col "MinFree"    for 99.9  Head "Minimum|Percent|Free"

col "MaxFree"    for 99.9  Head "Maximum|Percent|Free"

col "DiskCnt"    for 9999  Head "Disk|Count"

prompt

prompt ASM Disk Groups

prompt ===============

SELECT g.group_number  "Group"

,      g.name          "Group Name"

,      g.state         "State"

,      g.type          "Type"

,      g.total_mb/1024 "Total GB"

,      g.free_mb/1024  "Free GB"

,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"

,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"

,      100*(min(d.free_mb/d.total_mb)) "MinFree"

,      100*(max(d.free_mb/d.total_mb)) "MaxFree"

,      count(*)        "DiskCnt"

FROM v$asm_disk d, v$asm_diskgroup g

WHERE d.group_number = g.group_number and

d.group_number <> 0 and

d.state = 'NORMAL' and

d.mount_status = 'CACHED'

GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb

ORDER BY 1;

prompt ASM Disks In Use

prompt ================

col "Group"          for 999

col "Disk"           for 999

col "Header"         for a9

col "Mode"           for a8

col "State"          for a8

col "Created"        for a10          Head "Added To|Diskgroup"

--col "Redundancy"     for a10

--col "Failure Group"  for a10  Head "Failure|Group"

col "Path"           for a19

--col "ReadTime"       for 999999990    Head "Read Time|seconds"

--col "WriteTime"      for 999999990    Head "Write Time|seconds"

--col "BytesRead"      for 999990.00    Head "GigaBytes|Read"

--col "BytesWrite"     for 999990.00    Head "GigaBytes|Written"

col "SecsPerRead"    for 9.000        Head "Seconds|PerRead"

col "SecsPerWrite"   for 9.000        Head "Seconds|PerWrite"

select group_number  "Group"

,      disk_number   "Disk"

,      header_status "Header"

,      mode_status   "Mode"

,      state         "State"

,      create_date   "Created"

--,      redundancy    "Redundancy"

,      total_mb/1024 "Total GB"

,      free_mb/1024  "Free GB"

,      name          "Disk Name"

--,      failgroup     "Failure Group"

,      path          "Path"

--,      read_time     "ReadTime"

--,      write_time    "WriteTime"

--,      bytes_read/1073741824    "BytesRead"

--,      bytes_written/1073741824 "BytesWrite"

,      read_time/reads "SecsPerRead"

,      write_time/writes "SecsPerWrite"

from   v$asm_disk_stat

where header_status not in ('FORMER','CANDIDATE')

order by group_number

,        disk_number

/

Prompt File Types in Diskgroups

Prompt ========================

col "File Type"      for a16

col "Block Size"     for a5    Head "Block|Size"

col "Gb"             for 9990.00

col "Files"          for 99990

break on "Group Name" skip 1 nodup

select g.name                                   "Group Name"

,      f.TYPE                                   "File Type"

,      f.BLOCK_SIZE/1024||'k'                   "Block Size"

,      f.STRIPED

,        count(*)                               "Files"

,      round(sum(f.BYTES)/(1024*1024*1024),2)   "Gb"

from   v$asm_file f,v$asm_diskgroup g

where  f.group_number=g.group_number

group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED

order by 1,2;

clear break

prompt Instances currently accessing these diskgroups

prompt ==============================================

col "Instance" form a8

select c.group_number  "Group"

,      g.name          "Group Name"

,      c.instance_name "Instance"

from   v$asm_client c

,      v$asm_diskgroup g

where  g.group_number=c.group_number

/

prompt Free ASM disks and their paths

prompt ==============================

col "Disk Size"    form a9

select header_status                   "Header"

, mode_status                     "Mode"

, path                            "Path"

, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"

from   v$asm_disk

where header_status in ('FORMER','CANDIDATE')

order by path

/

prompt Current ASM disk operations

prompt ===========================

select *

from   v$asm_operation

/

This is how some of the changes look

Added To    Total   Free                                Seconds  Seconds

Group Disk Header    Mode     State    Diskgroup      GB     GB Disk Name  Path                PerRead PerWrite

----- ---- --------- -------- -------- ---------- ------ ------ ---------- ------------------- ------- --------

1    0 MEMBER    ONLINE   NORMAL   20-FEB-09      89     88 FRA_0000   /dev/oracle/disk388    .004     .002

1    1 MEMBER    ONLINE   NORMAL   31-MAY-10      89     88 FRA_0001   /dev/oracle/disk260    .002     .002

1    2 MEMBER    ONLINE   NORMAL   31-MAY-10      89     88 FRA_0002   /dev/oracle/disk260    .007     .002

2   15 MEMBER    ONLINE   NORMAL   04-MAR-10      89     29 DATA_0015  /dev/oracle/disk203    .012     .023

2   16 MEMBER    ONLINE   NORMAL   04-MAR-10      89     29 DATA_0016  /dev/oracle/disk203    .012     .021

2   17 MEMBER    ONLINE   NORMAL   04-MAR-10      89     29 DATA_0017  /dev/oracle/disk203    .007     .026

2   27 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0027  /dev/oracle/disk260    .011     .023

2   28 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0028  /dev/oracle/disk259    .009     .020

2   38 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0038  /dev/oracle/disk190    .012     .025

2   39 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0039  /dev/oracle/disk189    .014     .015

2   40 MEMBER    ONLINE   NORMAL   31-MAY-10      89     30 DATA_0040  /dev/oracle/disk260    .011     .024

2   41 MEMBER    ONLINE   NORMAL   31-MAY-10      89     30 DATA_0041  /dev/oracle/disk260    .009     .022

2   42 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0042  /dev/oracle/disk260    .011     .018

2   43 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0043  /dev/oracle/disk260    .003     .026

2   44 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0044  /dev/oracle/disk260    .008     .019

2   45 MEMBER    ONLINE   NORMAL   31-MAY-10      89     30 DATA_0045  /dev/oracle/disk193    .008     .018

2   46 MEMBER    ONLINE   NORMAL   31-MAY-10      89     30 DATA_0046  /dev/oracle/disk192    .007     .024

2   47 MEMBER    ONLINE   NORMAL   31-MAY-10      89     30 DATA_0047  /dev/oracle/disk191    .005     .022

2   48 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0048  /dev/oracle/disk190    .008     .021

2   49 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0049  /dev/oracle/disk189    .008     .026

2   50 MEMBER    ONLINE   NORMAL   31-MAY-10      89     29 DATA_0050  /dev/oracle/disk261    .009     .030

56 rows selected.

File Types in Diskgroups

========================

Group                   Block

Name   File Type        Size  STRIPE  Files       Gb

------ ---------------- ----- ------ ------ --------

DATA   CONTROLFILE      16k   FINE        1     0.01

DATAFILE         16k   COARSE    404  2532.58

ONLINELOG        1k    FINE        3     6.00

PARAMETERFILE    1k    COARSE      1     0.00

TEMPFILE         16k   COARSE     13   440.59

FRA    AUTOBACKUP       16k   COARSE      2     0.02

CONTROLFILE      16k   FINE        1     0.01

ONLINELOG        1k    FINE        3     6.00

kill all sessions for a user

set head off 
spool tmp.tmp 
select 'Alter system kill session '''||sid||','||serial#||''';' 
from v$session where username='USER_TO_KILL_IN_CAPS' ;
spoof off
@tmp.tmp

Opatch Helpers

How to tar the oracle home

cd /opt/oracle/product/10.2.0
tar -cvf - . |compress > /opt/oracle/admin/10203.beforeupgrade.tar.Z

cd /opt/oracle/oraInventory
tar -cvf - . |compress > /opt/oracle/admin/inventory_bkp_beforeupgrade.tar.Z






export PATH=$PATH:$ORACLE_HOME/OPatch

unzip p12879912_10204_.zip
cd 12879912
opatch napply -skip_subset -skip_duplicate 

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> -- Execute the next statement only if this is the first 10.2.0.4 CPU applied or this is the first CPU applied since CPUApr2011.
SQL> @utlrp.sql
SQL> QUIT 

SELECT * FROM registry$history where ID = '6452863';
 

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
SQL> QUIT 

1. cd $ORACLE_HOME/cpu/view_recompile
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP UPGRADE
   SQL> @view_recompile_jan2008cpu.sql
   SQL> SHUTDOWN;
   SQL> STARTUP;
   SQL> QUIT
   

2. If the database is
   
   
   

   cd $ORACLE_HOME/rdbms/admin
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> @utlrp.sql
   

   Then, manually recompile any invalid
    
3.  
4. select action_time, action, version, id, comments from dba_registry_history order by action_time; 

 When i had faced the file copy failed alert , this below step was followed 

cp /backup/oracle/patching/12879912/8568398/files/lib/libjox10.a /opt/oracle/product/10.2.0/lib/libjox10.a
524     cd /opt/oracle/product/10.2.0/lib/
525     ls -lrt libjo*
526     mv libjox10.a libjox10.a_orig
527     history
oracle @ gvi0aitmt04p:itmdwp1
[/opt/oracle/product/10.2.0/lib]$ cp /backup/oracle/patching/12879912/8568398/files/lib/libjox10.a /opt/oracle/product/10.2.0/lib/libjox10.a
 

Copy failed from '//lib/libjox10.a'..

then it was sucess with the warnings 

 check opatch utility information

cd $ORACLE_HOME/OPatch/opatch lsinventory

Rman point in time recovery

Day before yesterday i completed a build and handovered to the client , i never know that the client was waiting to hand over and to try a app upgrade, unfortunately that went wrong and he approached for a recover point in time, luckly i had sheduled the rman job on the cron ( ushually this will be sheduled in a automated tool and that will take nearly  a week time to set up)
i have taken the db in mount state .

Shutdown immediate;
startup nomount;



 Refference :

export NLS_LANG = american_america.us7ascii
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"


connect catalog xx/xx@xx
run {
allocate channel t1 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.xxx.opt)';
allocate channel t2 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.xxx.opt)';
sql 'alter session set NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"';
SET UNTIL TIME 'Feb 14 2012 21:00:00';
restore database;
recover database;
release channel t1;
release channel t2;
}

The opt files were by default for my environment pls change accordingly and as per ur environment

i had completed the restore and recover ..

Logs for your refference:


connected to recovery catalog database

RMAN>

RMAN> run {
2> allocate channel t1 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=

(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.metap5.opt)';
3> allocate channel t2 type 'sbt_tape' maxpiecesize 4000m parms 'ENV=

(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin/tdpo.metap5.opt)';
sql 'alter session set NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"';
4> 5> SET UNTIL TIME 'Feb 14 2012 21:00:00';
restore database;
6> 7> recover database;
8> release channel t1;
9> release channel t2;
10> }

allocated channel: t1
channel t1: sid=1088 devtype=SBT_TAPE
channel t1: Tivoli Data Protection for Oracle: version 5.2.0.0

allocated channel: t2
channel t2: sid=1087 devtype=SBT_TAPE
channel t2: Tivoli Data Protection for Oracle: version 5.2.0.0

sql statement: alter session set NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"

executing command: SET until clause

Starting restore at 15-FEB-12

channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/metap5/system_01.dbf
restoring datafile 00004 to /u02/oradata/metap5/metap5_tools_01.dbf
restoring datafile 00005 to /u02/oradata/metap5/metap5_users_01.dbf
restoring datafile 00007 to /u02/oradata/metap5/metap5_meta_index_01.dbf
restoring datafile 00008 to /u03/oradata/metap5/metap5_meta_index_03.dbf
restoring datafile 00010 to /u03/oradata/metap5/metap5_meta_index_035dbf
restoring datafile 00013 to /u03/oradata/metap5/metap5_meta_data_04.dbf
restoring datafile 00015 to /u03/oradata/metap5/metap5_meta_data_06.dbf
restoring datafile 00017 to /u03/oradata/metap5/metap5_meta_data_08.dbf
restoring datafile 00019 to /u03/oradata/metap5/metap5_meta_data_10.dbf
channel t1: reading from backup piece df_METAP5_775200607_9_1
channel t1: restored backup piece 1
piece handle=df_METAP5_775200607_9_1 tag=TAG20120214T053006
channel t1: restore complete, elapsed time: 00:02:56
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u02/oradata/metap5/undo_01.dbf
restoring datafile 00003 to /u01/oradata/metap5/sysaux_01.dbf
restoring datafile 00006 to /u01/oradata/metap5/metap5_meta_data_01.dbf
restoring datafile 00009 to /u03/oradata/metap5/metap5_meta_index_04.dbf
restoring datafile 00011 to /u03/oradata/metap5/metap5_meta_index_034dbf
restoring datafile 00012 to /u03/oradata/metap5/metap5_meta_data_02.dbf
restoring datafile 00014 to /u03/oradata/metap5/metap5_meta_data_05.dbf
restoring datafile 00016 to /u03/oradata/metap5/metap5_meta_data_07.dbf
restoring datafile 00018 to /u03/oradata/metap5/metap5_meta_data_09.dbf
restoring datafile 00020 to /u03/oradata/metap5/metap5_meta_data_11.dbf
channel t1: reading from backup piece df_METAP5_775200606_8_1
channel t1: restored backup piece 1
piece handle=df_METAP5_775200606_8_1 tag=TAG20120214T053006
channel t1: restore complete, elapsed time: 00:03:16
Finished restore at 15-FEB-12

Starting recover at 15-FEB-12

starting media recovery

channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=45
channel t1: reading from backup piece arc_METAP5_775201925_12_1
channel t1: restored backup piece 1
piece handle=arc_METAP5_775201925_12_1 tag=TAG20120214T055205
channel t1: restore complete, elapsed time: 00:00:55
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=44
channel t1: reading from backup piece arc_METAP5_775201925_11_1
channel t1: restored backup piece 1
piece handle=arc_METAP5_775201925_11_1 tag=TAG20120214T055205
channel t1: restore complete, elapsed time: 00:00:03
archive log filename=/opt/oracle/admin/metap5/arch/metap544_1_773984687.arclog thread=1

sequence=44
archive log filename=/opt/oracle/admin/metap5/arch/metap545_1_773984687.arclog thread=1

sequence=45
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=46
channel t1: reading from backup piece arc_METAP5_775289526_16_1
channel t1: restored backup piece 1
piece handle=arc_METAP5_775289526_16_1 tag=TAG20120215T061206
channel t1: restore complete, elapsed time: 00:01:06
archive log filename=/opt/oracle/admin/metap5/arch/metap546_1_773984687.arclog thread=1

sequence=46
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-FEB-12

released channel: t1

released channel: t2

Tablespaces

     Tablespace 

      A tablespace is a logical storage unit in an oracle database (its not a physical file), But tablespace inturn consists of atleast one datafile from the database. and that datafile is physicaly located in the file system on the database server , 

Our actual data's (Table,Index) will be segregated on this datafile . 

The above details are very basic understanding of an oracle tablespace and datafile. But i am not intended to write about the datafiles/tablespaces here . I am trying to have some space given here help me/others when we perform some task on the datafile and tablespaces . 

 At first , There are three types of tablespaces in oracle 

• Permanent Tablespaces
• Undo Tablespaces
• Temporary Tablespaces

Tasks that may involve to deal with permenant tablespaces were written below , if i miss anything here pls post back to add them, bcz i am trying to have some site to guide all the tasks that would help others / me . 

At First to check the tablespace usage :

I felt the below query helps to figure out what tablespaces that may require our attention to add more space . 

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

/

Lets say if we need to see what are the files associated with each tablespace 

Query the below one ,

set lines 100
col file_name format a70
select file_name
,      ceil(bytes / 1024 / 1024) "size MB"
from   dba_data_files
where  tablespace_name like '&TSNAME'

/

Tablespaces that are >=80% full, and how much to add to make them 80% again

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB"  for 999999999
col "%Used"  for 999
col "Add (80%)"  for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from    dba_data_files group by tablespace_name) tsu
,  (select ts.tablespace_name
 ,       nvl(sum(bytes)/1024/1024, 0) free_mb
 from    dba_tablespaces ts, dba_free_space fs
 where   ts.tablespace_name = fs.tablespace_name (+)
 group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

User quotas on all tablespaces

col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')
/

List all objects in a tablespace

 

set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/

 

 

Show all tablespaces used by a user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/

 

Create a temporary tablespace

create temporary tablespace temp
tempfile '' size 500M
/

Alter a databases default temporary tablespace

alter database default temporary tablespace temp
/

Show segments that are approaching max_extents

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

List the contents of the temporary tablespace(s)

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid 
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

Alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;

Alter tablespace: Data Files and Tempfiles

ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
12 Portable DBA: Oracle
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

Alter tablespace: Rename

ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

Alter tablespace: Tablespace Management

ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;

Create tablespace: Permanent Tablespace

CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;

Create tablespace: Temporary Tablespace

CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;