CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
Malatesh
Malatesh

Reputation: 1954

Oracle Temp Table Space ASM Vs FileSystem

I have both FileSystem and ASM oracle DB, Now I am creating the temp table space I could do it in FileSystem db using the following sql.

" CREATE TEMPORARY TABLESPACE AppTemp TEMPFILE '/db_data/my_db/temp01.dbf' SIZE 20M REUSE  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M"

Please explain how to achieve this in ASM, I have following syntax, searched on the net 

"CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA1_A3' SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M SEGMENT SPACE MANAGEMENT MANUAL"

How does it allocates the db files to table space ?

Upvotes: 0

Views: 10264

Answers (1)

Giova
Giova

Reputation: 1127

The following statement "CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA1_A3' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M SEGMENT SPACE MANAGEMENT MANUAL" will create a tmeporary tablespace called temp made of a tempfile on DATA1_A3 diskgroup witha system generated name. I suggest you to change it in : "CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA1_A3' SIZE 100M" so you will use the default storage clauses of your database and version.

If you need to add another datafile, you can use the following command: "ALTER TBALESPACE TEMP ADD TEMPFILE '+DATA1_A3' size nnnM"

If you need to modify the size of an existing datafile you have to retrieve de file name from dba_tempfiles or from asmcmd cli and then: "ALTER DATABASE TEMPFILE 'FILEABSOLUTEASMPATH' RESIZE nnnnM"

Are you experiencing problem with this command?

regards Giova

Upvotes: 1

Related Questions