cricket fight in 2016 at ipl

November 24, 2016

By postgresdba

top ten swing ball in cricket history

November 24, 2016

By postgresdba

11gr2 software post installation, dbstart/dbshut scripts which come with an Oracle install,shut down multiple db same time

November 23, 2016

By postgresdba

You can use the dbstart/dbshut scripts which come with an Oracle install. They are available
under $ORACLE_HOME/bin.

After a fresh install you have to edit the /etc/oratab file:
# cat /etc/oratab
# format: $ORACLE_SID:$ORACLE_HOME:N|Y
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:N
# sed -i ‘s/:N$/:Y/’ /etc/oratab

# grep my_sid /etc/oratab
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:Y

Then you can use the scripts like this:
$ whoami
juser
$ dbstart $ORACLE_HOME
$ # execute DB jobs …
$ dbshut $ORACLE_HOME

dbstart brings all up which is needed for Pro*C/OCI programs.
Using dbstart/dbshut is an improvement above the custom method mentioned in the question:
method time called tools
―――――――――――――――――――――――――――――――――――――――――――――――――――――
dbstart 5.7 s lsnrctl, sqlplus
dbshut 5.7 s lsnrctl, sqlplus
custom startup 27.9 s lsnrctl, sqlplus, emctl
custom shutdown 31.0 s lsnrctl, sqlplus, emctl
(times on a Core i7/2.8GHz system, slow spinning hard disk.)

How dbstart/dbshut work
A dbstart $ORACLE_HOME$ call is basically equivalent to:
$ lsnrctl start
$ echo -e ‘connect / as sysdba\nstartup\nquit’| sqlplus /nolog

And a dbshut $ORACLE_HOME$ is basically equivalent to:
$ lsnrctl stop
$ echo -e ‘connect / as sysdba\nshutdown\nquit’| sqlplus /nolog
(you can verify if everything is shutdown via ps aux | grep ‘tnsl\|ora’)

Note that the order of the commands is important. That means when lsnrctl start is executed after the sqlplus-startup command then the Pro*C/OCI program still complains about an unavailable TNS-listener.
And this is exactly the problem with the command sequence in the question – where the emctl start just workarounds the wrong order because it fixes the TNS-listener setup part.
Also note that for executing Pro*C/OCI programs the EMCTL service is not needed.

11gr2 software installation errors and solutions

November 23, 2016

By postgresdba

“error code 35” : The machine name in the “/etc/hosts” file is not correct. It needs an entry for the loopback adapter
(localhost) and the machine name. If you are using DNS for name resolution,
you still need the loopback adapter reference in this file.

“error code 37” : The DNS not working properly. You may also get this error is the
“/etc/hosts” file is not configured correctly.

“sqlplus: error while loading shared libraries: libclntsh.so.11.1” : The prerequisites
have not been met. Work through them again. Specifically, make sure the “gcc” package
has been installed.

Listener fails to start – Typically this is due to incorrect name resolution.
Make sure the “/etc/hosts” and/or DNS is configured correctly.

Linking errors – Almost always due to missing prerequisites. Review the setup sections.

Oracle Database 11g Release 2 Installation On Oracle Linux 5 –Preinstallation

November 23, 2016

By postgresdba

Download the following software.

OTN: Oracle Database 11g Release 2 (11.2.0.1) Software (64-bit)

MOS: Oracle Database 11g Release 2 (11.2.0.2 or 11.2.0.3) Software (64-bit)

Unpack Files

Unzip the files.

# 11.2.0.1

unzip linux.x64_11gR2_database_1of2.zip

unzip linux.x64_11gR2_database_2of2.zip

#11.2.0.2

unzip p10098816_112020_Linux-x86-64_1of7.zip

unzip p10098816_112020_Linux-x86-64_2of7.zip

#11.2.0.3

unzip p10404530_112030_Linux-x86-64_1of7.zip

unzip p10404530_112030_Linux-x86-64_2of7.zip

You should now have a single directory called “database” containing installation files.

Hosts File

The “/etc/hosts” file must contain a fully qualified name for the server.

For example.

127.0.0.1 localhost.localdomain localhost

192.168.2.181 ol5-11gr2.localdomain ol5-11gr2

Oracle Installation Prerequisites

Perform either the Automatic Setup or the Manual Setup to complete the basic prerequisites.

The Additional Setup is required for all installations.

Automatic Setup

If you plan to use the “oracle-validated” package to perform all your prerequisite setup, follow the instructions

at http://public-yum.oracle.com to setup the yum repository for OL, then perform the following command.

# yum install oracle-validated

All necessary prerequisites will be performed automatically.

It is probably worth doing a full update as well, but this is not strictly speaking necessary.

# yum update

Manual Setup

If you have not used the “oracle-validated” package to perform all prerequisites, you will need to manually perform the following setup tasks.

Oracle recommend the following minimum parameter settings.

fs.suid_dumpable = 1

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

The current values can be tested using the following command.

/sbin/sysctl -a | grep

Add or amend the following lines in the “/etc/sysctl.conf” file.

fs.suid_dumpable = 1

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

# semaphores: semmsl, semmns, semopm, semmni

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_default=262144

net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the “/etc/security/limits.conf” file.

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 4096

oracle hard nofile 65536

oracle soft stack 10240

Install the following packages if they are not already present.

# From Oracle Linux 5 DVD

cd /media/cdrom/Server

rpm -Uvh binutils-2.*

rpm -Uvh compat-libstdc++-33*

rpm -Uvh compat-libstdc++-33*.i386.rpm

rpm -Uvh elfutils-libelf*

rpm -Uvh gcc-4.*

rpm -Uvh gcc-c++-4.*

rpm -Uvh glibc-2.*

rpm -Uvh glibc-common-2.*

rpm -Uvh glibc-devel-2.*

rpm -Uvh glibc-headers-2.*

rpm -Uvh ksh*

rpm -Uvh libaio-0.*

rpm -Uvh libaio-devel-0.*

rpm -Uvh libgomp-4.*

rpm -Uvh libgcc-4.*

rpm -Uvh libstdc++-4.*

rpm -Uvh libstdc++-devel-4.*

rpm -Uvh make-3.*

rpm -Uvh sysstat-7.*

rpm -Uvh unixODBC-2.*

rpm -Uvh unixODBC-devel-2.*

rpm -Uvh numactl-devel-*

cd /

eject

Create the new groups and users.

groupadd oinstall

groupadd dba

groupadd oper

groupadd asmadmin

useradd -g oinstall -G dba,oper,asmadmin oracle

passwd oracle

We are not going to use the “asmadmin” group, since this installation will not use ASM.

Additional Setup

The following setup tasks must be performed regardless of which setup method you used

previously.

Set secure Linux to permissive by editing the “/etc/selinux/config” file, making sure

the SELINUX flag is set as follows.

SELINUX=permissive

Alternatively, this alteration can be done using the GUI tool (Applications > System

Settings > Security Level). Click on the SELinux tab and disable the feature.

If SELinux is disabled after installation, the server will need a reboot for the

change to take effect.

If you have the Linux firewall enabled, you will need to disable or configure it, as

shown here or here.

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.2.0/db_1

chown -R oracle:oinstall /u01

chmod -R 775 /u01

xhost +

file, remembering to adjust them for your specific installation.

# Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=ol5-112.localdomain; export ORACLE_HOSTNAME

ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=DB11G; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

DISPLAY=:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller

MATERIALIZED VIEW

November 22, 2016

By postgresdba

Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT …;

The BUILD clause options are shown below.
IMMEDIATE : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.
FAST : A fast refresh is attempted. If materialized view logs are not present against
the source tables in advance, the creation fails.
COMPLETE : The table segment supporting the materialized view is truncated and
repopulated completely using the associated query.
FORCE : A fast refresh is attempted. If one is not possible a complete refresh is
performed.

A refresh can be triggered in one of two ways.
ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

The QUERY REWRITE clause tells the optimizer if the materialized view should be
consider for query rewrite operations. An example of the query rewrite functionality
is shown below.

The ON PREBUILT TABLE clause tells the database to use an existing table segment,
which must have the same name as the materialized view and support the same column
structure as the query.

Check Privileges
Check the user who will own the materialized views has the correct privileges.
At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are
creating materialized views using database links, you may want to grant them CREATE
DATABASE LINK privilege also.

CONNECT sys@db2
GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;

Create Materialized View
Connect to the materialized view owner and create the database link and the materialized view itself.

CONNECT scott/tiger@db2
CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING ‘DB1.WORLD’;

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM [email protected];
Alternatively, we could have used a prebuilt table, as shown below.

Create the tale first. This could be populated
using an export/import.
CREATE TABLE emp_mv AS
SELECT * FROM [email protected];

Build the materialized view using the existing table segment.
CREATE MATERIALIZED VIEW emp_mv
REFRESH FORCE
ON DEMAND
ON PREBUILT TABLE
AS
SELECT * FROM [email protected];
Remember to gather stats after building the materialized view.

BEGIN
DBMS_STATS.gather_table_stats(
ownname => ‘SCOTT’,
tabname => ‘EMP_MV’);
END;
/

Create Materialized View Logs
Since a complete refresh involves truncating the materialized view segment and
re-populating it using the related query, it can be quite time consuming and involve
a considerable amount of network traffic when performed against a remote table.
To reduce the replication costs, materialized view logs can be created to capture all
changes to the base table since the last refresh. This information allows a fast
refresh, which only needs to apply the changes rather than a complete refresh of the
materialized view.

To take advantage of the of the fast refresh, connect to the master instance and
create the materialized view log.

CONNECT scott/tiger@db1

CREATE MATERIALIZED VIEW LOG ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Refresh Materialized Views
If a materialized view is configured to refresh on commit, you should never need to
manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is
a very intensive operation for volatile base tables. It makes sense to use fast
refreshes where possible.
For on demand refreshes, you can choose to manually refresh the materialized view or
refresh it as part of a refresh group.
The following code creates a refresh group defined to refresh every minute and assigns
a materialized view to it.

BEGIN
DBMS_REFRESH.make(
name => ‘SCOTT.MINUTE_REFRESH’,
list => ”,
next_date => SYSDATE,
interval => ‘/*1:Mins*/ SYSDATE + 1/(60*24)’,
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/

BEGIN
DBMS_REFRESH.add(
name => ‘SCOTT.MINUTE_REFRESH’,
list => ‘SCOTT.EMP_MV’,
lax => TRUE);
END;
/
A materialized view can be manually refreshed using the DBMS_MVIEW package.
EXEC DBMS_MVIEW.refresh(‘EMP_MV’);
Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called using the Oracle Scheduler

Cleaning Up
To clean up we must remove all objects.

CONNECT scott/tiger@db2
DROP MATERIALIZED VIEW emp_mv;
DROP DATABASE LINK DB1.WORLD;

BEGIN
DBMS_REFRESH.destroy(name => ‘SCOTT.MINUTE_REFRESH’);
END;
/

CONNECT scott/tiger@db1
DROP MATERIALIZED VIEW LOG ON scott.emp;

Aggregations and Transformations
Materialized views can be used to improve the performance of a variety of queries,
including those performing aggregations and transformations of the data. This allows
the work to be done once and used repeatedly by multiple sessions, reducing the total
load on the server.

The following query does an aggregation of the data in the EMP table.
CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
—————————————————————————

Create a materialized view to perform the aggregation in advance, making sure you
specify the ENABLE QUERY REWRITE clause.
CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM emp
GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, ‘EMP_AGGR_MV’);
The same query is now rewritten to take advantage of the pre-aggregated data in the
materialized view, instead of the session doing the work for itself.

–ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
–ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV | 3 | 21 | 3 (0)| 00:00:01 |

tablespace create,add,delete,alter,views

November 22, 2016

By postgresdba

Tablespace : is logical storage unit in Oracle Database.
ii) Tablespace connsit of one or more datafiles (check below)
iii) Information about Tablespace can be obtained from view DBA_TABLESPACES, USER_TABLESPACES, DBA_TEMP_FILES (for Temporary Tablespaces)
iv) Tablespace is further divided in to logical units Segments (Segment is divided in to Extent and Extent in to Block) . To know more about Segment, Extents and Blocks click here
v) Various type of tablespace are BIGFILE, SYSTEM, SYSAUX, UNDO

Datafiles : is physical structure to store oracle data
ii) One or more physical datafile are logically grouped together to make a tablespace
iii) Information about Datafile can be obtained from view DBA_DATA_FILESiv) A Datafile can be associated with only one tablespace

Adding Tablespace in databaseUse CREATE TABLESPACE command to add tablespace in Database like
CREATE TABLESPACE DATAFILE
CREATE TABLESPACE my_tablespace DATAFILE ‘/u01/oracle/oradata/data01.dbf’;

To create undo tablespace
CREATE UNDOTABLESPACE DATAFILE SIZE [AUTOEXTEND ON|OFF] [RETENTION GURANTEE|NOGURANTEE]

To create Temporary tablespace
CREATE TEMPORARYTABLESPACE TEMPFILE SIZE [AUTOEXTEND ON|OFF]

Adding Datafile in a TablespaceUse ALTER TABLESPACE to add datafile in tablespace like
ALTER TABLESPACE ADD DATAFILE
ALTER TABLESPACE my_tablespace ADD DATAFILE ‘/u01/oracle/oradata/data02.dbf’;

To add temporary file in TEMP table space
ALTER TABLESPACE ADD TEMPFILE ‘’ SIZE ;

Modify DatafileYou can modify datafile using ALTER DATABASE command like
ALTER DATABASE DATAFILE AUTOEXTEND ON|OFF NEXT MAXSIZE ;

ALTER DATABASE DATAFILE ‘/u01/oracle/oradata/data02.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;

which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required.

tablespace AUTOEXTEND ON Next Size

November 22, 2016

By postgresdba

AUTOEXTEND ON Next Size

UPDATE 28-Feb-11 : Apparently, in 11gR1, Bug 8318050 affects the behaviour of
Autoextend On datafiles such that the NEXT size specified may not be honoured. See
Oracle Support Article#8318050.8

In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification
component.

In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must
also be specified. However, Autoextend’s NEXT size is not mandatory and Oracle can
“default”. Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database
block (based on the blocksize of the tablespace).

But if you create your Tablespace using OMF (i.e. where “db_create_file_dest” is
configured), then Oracle defaults the initial size to 100MB and *also* defaults
the AUTOEXTEND to ON with a of 100MB ! That is much neater.

Why is the default 1 Database block bad ? Because when the datafile is full, Oracle
will extend it 1 block at-a-time, making a call to the OS to add 1 block on each
occassion. Obviously, even if you are extending a table or index with an Extent
of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB).
That is where you will see “data file init write” waits.

In the example below, the (automatic) datafile for Tablespace AN_OMF_TBS get’s
created with both the initial and increment at 100MB and AutoExtend ON. However,
for Tablespace REGULAR_TBS, I have to specify the initial size for the datafile.
If I do not specify AutoExtend, the file is created with AutoExtend OFF. For the
third tablespace, called ANOTHER_TBS, when I designate AutoExtend ON but do not
specify the incremental size, Oracle defaults it to 1 Oracle Block.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible,
increment_by from dba_data_files order by file_id;

6 rows selected.

SQL> alter system set db_create_file_dest=’/var/tmp’;

System altered.

SQL> create tablespace an_omf_tbs;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible,
increment_by from dba_data_files order by file_id;

TABLESPACE_NAME FILE_NAME FILE_SIZE_MB AUT INCREMENT_BY
————— ——————————————————- ———— — ————
SYSTEM /oracle_fs/Databases/ORT24FS/system01.dbf 590 YES 1,280
UNDOTBS1 /oracle_fs/Databases/ORT24FS/undotbs01.dbf 155 YES 640
SYSAUX /oracle_fs/Databases/ORT24FS/sysaux01.dbf 270 YES 1,280
USERS /oracle_fs/Databases/ORT24FS/users01.dbf 85 YES 160
EXAMPLE /oracle_fs/Databases/ORT24FS/example01.dbf 100 YES 80
TEST_A_TBS /oracle_fs/Databases/ORT24FS/test_a_tbs.dbf 100 NO 0
AN_OMF_TBS /var/tmp/ORT24FS/datafile/o1_mf_an_omf_t_541wgb0c_.dbf 100 YES 12,800

7 rows selected.

SQL> create tablespace REGULAR_TBS datafile ‘/oracle_fs/Databases/ORT24FS/regular_tbs.dbf’;
create tablespace REGULAR_TBS datafile ‘/oracle_fs/Databases/ORT24FS/regular_tbs.dbf’
*
ERROR at line 1:
ORA-01119: error in creating database file ‘/oracle_fs/Databases/ORT24FS/regular_tbs.dbf’
ORA-17610: file ‘/oracle_fs/Databases/ORT24FS/regular_tbs.dbf’ does not exist and no size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> create tablespace REGULAR_TBS datafile ‘/oracle_fs/Databases/ORT24FS/regular_tbs.dbf’ size 100M;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from
dba_data_files order by file_id;

8 rows selected.

SQL> create tablespace ANOTHER_TBS datafile ‘/oracle_fs/Databases/ORT24FS/another_tbs.dbf’ size 100M autoextend on ;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files
order by file_id;

9 rows selected.

SQL>

You would be well-adviced to remember that when you create or add a non-OMF datafile,
you should specify the Increment size with the AutoExtend ON. Else, you might suffer
the overheads of Oracle having to make multiple calls to the OS whenever extending the datafile (imagine extending a datafile 1 block at each call for an extent of 64MB !)

SQL> create tablespace LAST_TBS datafile ‘/oracle_fs/Databases/ORT24FS/last_tbs.dbf’
size 100M autoextend on next 100M;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible,
increment_by from dba_data_files order by file_id;

10 rows selected.

SQL>

Creating Temporary Tablespace

November 22, 2016

By postgresdba

Creating Temporary Tablespace
From Oracle 9i, we can specify a default temporary tablespace when you create a
database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE
statement.
e.g.
SQL> CREATE DATABASE oracular …..
DEFAULT TEMPORARY TABLESPACE temp_ts …..;

Oracle provides various ways of creating TEMPORARY tablespaces.
Prior to Oracle 7.3 – CREATE TABLESPACE temp DATAFILE …;
Example:
SQL> CREATE TABLESPACE TEMPTBS DATAFILE ‘/path/temp.dbf’ SIZE 2048M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT
MANAGEMENT DICTIONARY;

Oracle 7.3 & 8.0 – CREATE TABLESPACE temp DATAFILE … TEMPORARY;
Example:
SQL> CREATE TABLESPACE TEMPTBS DATAFILE ‘/path/temp.dbf’ SIZE 2048M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE
TEMPORARY EXTENT MANAGEMENT DICTIONARY;

Oracle 8i and above – CREATE TEMPORARY TABLESPACE temp TEMPFILE …;
Examples:
SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE ‘/path/temp.dbf’ SIZE 1000M
AUTOEXTEND ON NEXT 8K MAXSIZE 1500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K;

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS2 TEMPFILE ‘/path/temp2.dbf’ SIZE 1000M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL BLOCKSIZE 2K;

The MAXSIZE clause will default to UNLIMITED, if no value is specified.
All extents of temporary tablespaces are the same size, so UNIFORM keyword is
optional – if UNIFORM is not defined it will default to 1 MB.

Example using OMF (Oracle Managed Files):
SQL> CREATE TEMPORARY TABLESPACE temp;

Restrictions:
(1) We cannot specify nonstandard block sizes for a temporary tablespace or if you
intend to assign this tablespace as the temporary tablespace for any users.
(2) We cannot specify FORCE LOGGING for an undo or temporary tablespace.
(3) We cannot specify AUTOALLOCATE for a temporary tablespace.

Tempfiles (Temporary Datafiles)
Unlike normal datafiles, tempfiles are not fully allocated. When you create a tempfiles,
Oracle only writes to the header and last block of the file. This is why it is much
quicker to create a tempfiles than to create a normal datafile.

Tempfiles are not recorded in the database’s control file. This implies that just
recreate them whenever you restore the database, or after deleting them by accident.
You can have different tempfile configurations between primary and standby databases
in dataguard environment, or configure tempfiles to be local instead of shared in a
RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace.
However, one can remove a tempfile from a database. Look at this example:
SQL> alter database tempfile ‘tempfile_name’ drop including datafiles;
//If the file was created as tempfile

SQL> alter database datafile ‘tempfile_name’ drop;
//If the file was created as datafile

Dropping temp tablespace
SQL> drop tablespace temp_tbs;
SQL> drop tablespace temp_tbs including contents and datafiles;

If you remove all tempfiles from a temporary tablespace, you may encounter error:
ORA-25153: Temporary Tablespace is Empty.

Use the following statement to add a tempfile to a temporary tablespace:
SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/path/temp01.dbf’ SIZE 512m
AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Except for adding a tempfile, you cannot use the ALTER TABLESPACE statement for a
locally managed temporary tablespace (operations like rename, set to read only,
recover, etc. will fail).

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are
similar to ordinary datafiles except:
You cannot create a tempfile with the ALTER DATABASE statement.
You cannot rename a tempfile or set it to read-only.
Tempfiles are always set to NOLOGGING mode.
When you create or resize tempfiles, they are not always guaranteed allocation of
disk space for the file size specified. On certain file systems (like UNIX) disk
blocks are allocated not at file creation or resizing, but before the blocks are
accessed.
Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic
performance view V$TEMPFILE.
Note: This arrangement enables fast tempfile creation and resizing, however, the disk
could run out of space later when the tempfiles are accessed.

Default Temporary Tablespaces
From Oracle 9i, we can define a default temporary tablespace at database creation time, or by issuing an “ALTER DATABASE” statement:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

By default, the default temporary tablespace is SYSTEM. Each database can be
assigned one and only one default temporary tablespace. Using this feature,
a temporary tablespace is automatically assigned to users.

The following restrictions apply to default temporary tablespaces:
-DEFAULT TEMPORARY TABLESPACE must be of type TEMPORARY.
-DEFAULT TEMPORARY TABLESPACE cannot be taken off-line.
-DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

To see the default temporary tablespace for a database, execute the following query:
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name
like ‘%TEMP%’;

The DBA should assign a temporary tablespace to each user in the database to prevent
them from allocating sort space in the SYSTEM tablespace. This can be done with one
of the following commands:
SQL> CREATE USER scott TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

To change a user account to use a non-default temp tablespace
SQL> ALTER USER user1 SET TEMPORARY TABLESPACE temp_tbs;

Assigning temporary tablespace group as default temporary tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;
Assigning temporary tablespace group to a user (same as assigning temporary tablespace
to a user):
SQL> ALTER USER scott TEMPORARY TABLESPACE temp_grp;

All new users that are not explicitly assigned a TEMPORARY TABLESPACE will get the
default temporary tablespace as its TEMPORARY TABLESPACE. Also, when you assign a
TEMPORARY tablespace to a user, Oracle will not change this value next time you change
the default temporary tablespace for the database.

Monitoring Temporary Tablespaces
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use
V$TEMPFILE and DBA_TEMP_FILES instead.

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;
TABLESPACE_NAME FILE_NAME BYTES
—————– ——————————– ————–
TEMP /../temp01.dbf 11,175,650,000

SQL> select file#, name, round(bytes/(1024*1024),2) “SIZE IN MB’s” from v$tempfile;

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE.

DBA_FREE_SPACE does not record free space for temporary tablespaces.
Use DBA_TEMP_FREE_SPACE or V$TEMP_SPACE_HEADER instead.

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
—————————— ———- ———-
TEMPTBS 4214226944 80740352

From 11g, we can check free temp space in new view DBA_TEMP_FREE_SPACE.
SQL> select * from DBA_TEMP_FREE_SPACE;

Resizing tempfile
SQL> alter database tempfile temp-name resize integer K|M|G|T|P|E;
SQL> alter database tempfile ‘/path/temp01.dbf’ resize 1000M;

Resizing temporary tablespace
SQL> alter tablespace temptbs resize 1000M;

Renaming (temporary) tablespace, this is from Oracle 10g
SQL> alter tablespace temp rename to temp2;

reclaim used space
Several methods existed to reclaim the space used for a larger than normal temporary
tablespace.
(1) Restarting the database, if possible.
(2) The method that exists for all releases of Oracle is, simply drop and recreate the
temporary tablespace back to its original (or another reasonable)size.
(3) If you are using Oracle9i or higher, drop the large tempfile (which will drop the
tempfile from the data dictionary and the OS file system).

Views:
DBA_TEMP_FILES
DBA_DATA_FILES
DBA_TABLESPACES
DBA_TEMP_FREE_SPACE (Oracle 11g)
V$TEMPFILE
V$TEMP_SPACE_HEADER
V$TEMPORARY_LOBS
V$TEMPSTAT
V$TEMPSEG_USAGE