I'm Top Oracle DBA

Generate script for create temporary tablespace on Oracle Databases 10g onward

|

Many Types of Database files, and sometimes I feel more confused, but you have to be aware of block size and increment, like my command.

***note***
before you’ll use my command. Please read to understand.

—– Those commands are separated into 4 types —–
1st —> for temporary tablespace autoextend is NO and temporary tablespace size is not over 32g.
2nd —> for temporary tablespace autoextend is NO and temporary tablespace size over 32g
(that means you have to change or add the command add tempfile more)
for the example
temporary tablespace size is 118G and your blocksize is 32g it means you have to have 4 or more tempfile
when you execute my command you’ll get
                      create temporary tablespace xxxxx datafile ‘+xxxxx’ size 120832m;
 You have to change like below.
                      create temporary tablespace xxxxx tempfile ‘+xxxxx’ size 32767m;
                      alter temporary tablespace xxxxx tempfile ‘+xxxxx’ size 32667m;
                      alter temporary tablespace xxxxx tempfile ‘+xxxxx’ size 32667m;
                      alter temporary tablespace xxxxx tempfile ‘+xxxxx’ size 22527m;
and    You have to check the tablespace that you generate in step 1st, if it has in 2nd,3rd,4th or not,
             if in 2nd,3rd,4th have tablespace same in step 1st you have to change command in
             2nd,3rd,4th from ‘create tablespace ‘ to ‘alter tablespace ‘ as well.

3rd —> for temporary tablespace autoextend is Yes and temporary tablespace size is not over 32g.
4th —> for temporary tablespace autoextend is Yes and temporary tablespace size over 32g.
                  You have to edit like step 2nd.

—– command is not flexible, but it can be used every time that I worked, I’ll edit it later. asap.
—– xxxx it mean location of tempfiles.

1.

—CREATE TEMPORARY TABLESPACE AUTOEXTEND = NO
—AND SUM(BYTES=MB)
—AND SUM_BYTES <= 32767 (BLOCK SIZE = 32G)
—DON’T FORGET SIMILAR TEMPFILE

SELECT 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' TEMPFILE ''XXX'' SIZE '||SUM(BYTES/1024/1024)||'M;'
FROM DBA_TEMP_FILES
HAVING SUM(BYTES/1024/1024) <= 32767
AND AUTOEXTENSIBLE='NO'
GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE
ORDER BY TABLESPACE_NAME;

2.

—CREATE TEMPORARY TABLESPACE AUTOEXTEND = NO
—AND SUM(BYTES=MB)
—AND SUM_BYTES > 32767
—FOR DECOMPOSITE TEMPFILES
—DON’T FORGET SIMILAR TEMPFILE

SELECT 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' TEMPFILE ''XXX'' SIZE '||SUM(BYTES/1024/1024)||'M;'
FROM DBA_TEMP_FILES
HAVING SUM(BYTES/1024/1024) > 32767
AND AUTOEXTENSIBLE='NO'
GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE
ORDER BY TABLESPACE_NAME;

3.

—CREATE TEMPORARY TABLESPACE AUTOEXTEND = YES
—AND SUM(BYTES=MB)
—AND SUM_BYTES <= 32767
—FOR DECOMPOSITE TEMPFILES
—DON’T FORGET SIMILAR TEMPFILE
—INCREMENT_BY (K)

SELECT 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' TEMPFILE ''XXX'' SIZE '||ROUND(SUM(BYTES/1024/1024))||'M AUTOEXTEND ON NEXT '||ROUND(SUM(INCREMENT_BY/1024)) ||'M MAXSIZE '||ROUND(SUM(MAXBYTES/1024/1024))||'M;'
FROM DBA_TEMP_FILES
HAVING SUM(BYTES/1024/1024) <= 32767
AND AUTOEXTENSIBLE='YES'
GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE
ORDER BY TABLESPACE_NAME;

4.

—CREATE TEMPORARY TABLESPACE AUTOEXTEND = YES
—AND SUM(BYTES=MB)
—AND SUM_BYTES > 32767
—FOR DECOMPOSITE TEMPFILES
—DON’T FORGET SIMILAR TEMPFILE
—INCREMENT_BY (K)

SELECT 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' TEMPFILE ''XXX'' SIZE '||ROUND(SUM(BYTES/1024/1024))||'M AUTOEXTEND ON NEXT '||ROUND(SUM(INCREMENT_BY/1024)) ||'M MAXSIZE '||ROUND(SUM(MAXBYTES/1024/1024))||'M;'
FROM DBA_TEMP_FILES
HAVING SUM(BYTES/1024/1024) > 32767
AND AUTOEXTENSIBLE='YES'
GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE
ORDER BY TABLESPACE_NAME;
604
error: Content is protected !!