• 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.

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';


20 views

Recent Posts

See All

Find ASM physical disk mapping

When you work in a RAC environment with a list of ASM disks, sometimes you might want to figure out which ASM disk is mapped to which physical device. Below is a simple script that will help you find

Zero downtime ASM migration

This article is part of our video course Oracle RAC DBA video course. Enroll into the course today to start learning oracle rac online. In your environment, you might require to migrate ASM disks from

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