• Arun Kumar

Create Tablespace in Oracle ASM

CHECK BELOW PARAMETERS


DB_CREATE_FILE_DEST

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE


CREATE TABLESPACE

SQL> CREATE TABLESPACE test_tbs;

In ASM datafiles / tempfiles are create with 100mb initial size and autoextend unlimited


You can change these default parameters too.


CHECKING TABLESPACE UTILIZATION

set colsep |
set linesize 100 pages 100 trimspool on numwidth 14 
col name format a25
col owner format a15 
col "Used (GB)" format a15
col "Free (GB)" format a15 
col "(Used) %" format a15 
col "Size (M)" format a15
SELECT d.status "Status", d.tablespace_name "Name", 
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", 
TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
FROM sys.dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
union all
SELECT d.status 
"Status", d.tablespace_name "Name", 
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" 
FROM sys.dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

61 views

Recent Posts

See All

11gR2 Non-RAC to RAC Migration

In this article we will be looking at Non-RAC to RAC migration using DBCA. I have RACN1 and RACN2 where I will show you how to migrate a single instance database running on machine DT_VM. Create Templ

RMAN Backups in RAC

BACKUP DEFAULT LOCATION All backups by default go to FRA disk group TAKE BACKUP TO CUSTOM LOCATION ON ASM RMAN> backup as compressed backupset database format '+RMANDG'; or run{ allocate channel ch1

Create Diskgroup Sqlplus Command

In this article we will be creating new ASM diskgroup using SQLPLUS command while connected to ASM instance. You must have an unused partition / disk on the server that can be used to create ASM diskg

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback