Datasheet
36
Chapter 1
Using Oracle ASM
Note in this example that Oracle went ahead and filled out the rest of the path, giving us
a complete filename in the
DBA_DATA_FILES view to work with. We can also see this new file
in the ASM instance using the
V$ASM_FILES view, as shown here:
SQL> select group_number, file_number, type, blocks, bytes from v$asm_file;
GROUP_NUMBER FILE_NUMBER TYPE BLOCKS BYTES
------------ ----------- -------------------- ---------- ----------
1 256 DATAFILE 14 114688
If you want to drop a tablespace that contains ASM files, you need only issue the
drop tablespace command. Oracle will clean up all of the ASM datafiles associated with
that tablespace. You can have a mix of ASM datafiles and normal datafiles assigned to a
tablespace, as shown in this
create table statement:
Create tablespace part_asm_tbs
Datafile ’c:\oracle\oradata\11gDB\part_asm_tbs_01.dbf’ size 10m,
’+COOKED_DGROUP1’ size 100k;
Let’s look and see where the datafiles were created:
Select tablespace_name, file_name
from dba_data_files Where tablespace_name=’PART_ASM_TBS’;
TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
PART_ASM_TBS C:\ORACLE\ORADATA\11GDB\PART_ASM_TBS_01.DBF
PART_ASM_TBS +COOKED_DGROUP1/11GDB/datafile/part_asm_tbs.256.613066047
Note that in this case, if you drop the PART_ASM_TBS tablespace, only the ASM files
related to that tablespace would be removed from the disk when you issue the
drop
tablespace
command. In cases such as these, you need to make sure you include the
including contents and datafiles parameter with the drop tablespace command.
Creating Tablespaces Referencing Specific ASM Disk Groups
There are going to be many times when you will not want to define a default ASM disk
group to write all tablespaces to. In this case, you will want to reference the specific ASM
disk group that you want a datafile created in when you issue the
create tablespace com-
mand. Here is an example:
create tablespace another_test
datafile ’+COOKED_DGROUP1’ size 100k;
95134c01.indd 36 1/28/09 9:43:45 AM