Users

User related Scripts:
------------------------

Script to take user password backup in Oracle 11g


$ sqlplus "/as sysdba"

set pages 50000 lines 32767

select 'alter user ' || name || ' identified by values "' || password || "';' from sys.user$ 
where name in ('&name');

USER DDL

SET LONG 99999999
select dbms_metadata.get_ddl('USER','&username') from dual;


How to who has changed the user password and when
How to who has changed the user password and when 

$ sqlplus "/as sysdba"

SYS> create table sachin.log (msg varchar2(1000));

Table created.

SYS> create or replace procedure sachin.p (who in varchar2, what in varchar2)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into sachin.log values (who||' modifies '||what||'''s password at '||systimestamp);
  6    commit;
  7  end;
  8  /

Procedure created.

SYS> CREATE OR REPLACE FUNCTION verify_function
  2  (username varchar2,
  3    password varchar2,
  4    old_password varchar2)
  5    RETURN boolean IS 
  6  BEGIN 
  7     sachin.p (user, username);
  8     RETURN(TRUE);
  9  END;
 10  /

Function created.

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;

Profile altered.

SYS> alter user sachin identified by sachin;

User altered.

SYS> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00

1 row selected.

SYS> connect sachin/sachin
Connected.
sachin> password
Password changed
sachin> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00
sachin modifies sachin's password at 23/04/1998 18:55:04.093 +01:00

2 rows selected.


USER last login details
SELECT TO_CHAR(TIMESTAMP#,'MM/DD/YY HH:MI:SS') TIMESTAMP,
USERID, AA.NAME ACTION FROM SYS.AUD$ AT, SYS.AUDIT_ACTIONS AA
WHERE AT.ACTION# = AA.ACTION
and AA.name='LOGON'
and userid in
('&User_id')
ORDER BY TIMESTAMP# DESC;
select OS_USERNAME,action_name,USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff
from dba_audit_session where username ='&user'
AND (timestamp > (sysdate - 61))
order by logon_time,username,timestamp,logoff_time;

GRANT SELECT ON SCHEMA OBJECTS
GRANT SELECT ON SCHEMA OBJECTS TO USER THROUGH ROLE
set heading off;

select 'grant select on '||owner|| '.' ||object_name || to ROLE_NAME;' from dba_objects where owner='SCHEMA_NAME';

grant ROLE_NAME to USER_NAME;

Find a role
select * from dba_roles where role like '&role'
/ 
Show what roles are granted to a userselect grantee,granted_role,admin_option from dba_role_privs 
where grantee like ('&username')
/
Show what table privileges are granted to a role
select owner || '.' || table_name "TABLE",column_name,privilege,grantable
from role_tab_privs where role like '&role'
/
Show what system privileges are granted to a role
select privilege,admin_option from role_sys_privs where role like '&role'
/


GRANT READ WRITE ON SCHEMA OBJECTS
select 'grant read,write on '||owner||'.'||object_name||' to '||'YOURREADWRITE_USER'||';' from all_objects where 

owner='&YOUROBJECT_OWNER';


GRANT USER ACCESS ON SCHEMA
user needs read only access on existing schema
----------------------------------------------
select 'grant '||decode(object_type,'TABLE','select','VIEW','select','SEQUENCE','SELECT ','PROCEDURE','EXECUTE 

','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to target user' 

from dba_objects where OWNER='source user' and object_type not in ('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

user needs full access on existing schema
-----------------------------------------
select 'grant '||decode(object_type,'TABLE','select,insert,delete,update ','VIEW','select,insert,delete,update 

','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||

owner||'.'||object_name||' to targetuser' from dba_objects where OWNER='source user' and object_type not in 
('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

Related:
-------
Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow 

the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new 

permissions to NewUser.

Set pagesize 0
define OWNER=
define NEWUSER=

Spool new_grants.txt

Select
decode(OBJECT_TYPE,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',
'VIEW','GRANT SELECT ON '||'&OWNER'||'.',
'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',
'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',
'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',
'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'
From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
Order By OBJECT_TYPE;

Spool Off;
exit


DBA Audit Trail
col USERNAME for a10
col OS_USERNAME for a10
col USERHOST for a15
col ACTION_NAME for a11
set pages 200 lines 100
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

select USERNAME,OS_USERNAME,USERHOST,TIMESTAMP,ACTION_NAME, LOGOFF_TIME from dba_audit_trail where username='&USERNAME' 

order by USERHOST;


USER
Schema DDL
-------------
 set pagesize 0 
SET LONG 9999999
select dbms_metadata.get_ddl('USER','&USERNAME') FROM DUAL;

Schema Status
-------------
select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='&username';

select username,tablespace_name,decode(max_bytes,-1,'unlimited',ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA in MB" from 

dba_ts_quotas where  username='&username';

select grantee,granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "GRANTED ROLE" from 

dba_role_privs where grantee='&username';

select grantee,privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option') "PRIVILEGE" from 

dba_sys_privs where grantee='&username';

select owner,sum(bytes)/1024/1024/1024 "SIZE in GB" from dba_segments where owner='&owner';

select owner,object_type,count(*) from dba_objects where owner='&owner' group by owner, object_type;


All Users passwords backup
--------------------------------
spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users;  ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off
/
All tables owned by a user
--------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select    owner
,       tablespace_name
,       segment_name
,       segment_type
,       PARTITION_NAME
,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from    dba_segments
where    owner like '&user'
and    segment_type = 'TABLE'
group    by segment_name
order     by ceil(sum(bytes) / 1024 / 1024) desc
/

All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select    owner
,       tablespace_name
,       segment_name
,       segment_type
,       PARTITION_NAME
,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from    dba_segments
where    tablespace_name like '&tablespace_name'
group    by segment_name
order     by ceil(sum(bytes) / 1024 / 1024) desc
/

Total space allocated by Owner:
-------------------------------
set pages 9999 lines 300
col    owner format a15
col    segment_name format a30
col    segment_type format a15
col     tablespace_name format a20
col    mb format 999,999,999
select  owner
,    segment_name
,    segment_type
,       tablespace_name
,    mb
from    (
    select    owner
    ,    segment_name
    ,    segment_type
        ,       tablespace_name
    ,    bytes / 1024 / 1024 "SIZE in MB"
    from    dba_segments
    order    by bytes desc
    )
/

schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "size MB"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

Real Time Linux & Unix Useful Commands

Real Time Linux & Unix Useful Commands:

-------------------------------------------------------

compress Listener log file in Linux and real time useful commands:

=================================================

Unix Level Useful commands

=========================

Step:-1

-------

cd /u01/app/oracle/10.2.0/db_1/network/admin --- listener log location

du -sh *|sort -n

du -sh listener.log

Step:-2

-------

vi filecompress.sh

cd /u01/app/oracle/10.2.0/db_1/network/admin --- listener log location

cp listener.log listener_currentdate.log

cat /dev/null > listener.log

-- press esc key

:wq

Run the filecompress.sh in nohup

---------------------------------

nohup sh -x filecompress.sh > filecompress.log 2>> filecompress.err &

Step:-3

-------

gzip -9 listener_currentdate.log

du -sh *.gz

--- OR ---

tar -zcvf listener_currentdate.tar.gz listener_currentdate.log

du -sh *.tar.gz

All the platforms (Linux, Solaries, AIX,IBMAIX,Sunsolaries) and it is very useful editing commands:

===============================================================================================

vi Editor Commands

vi Editor Commands

$ vi <filename>

Option ==> Action

vi     ==> Starts editing session in memory.

vi     ==> Starts session and opens the specified file.

vi *   ==> Opens first file that matches the wildcard pattern. Use :n to navigate to the next matched file.

view   ==> Opens file in read-only mode.

vi -R  ==> Opens file in read-only mode.

vi -r  ==> Recovers file and recent edits after abnormal abort from editing session (like a system crash).

vi +n  ==> Opens file at specified line number n.

vi +   ==> Opens file at the last line.

vi +/  ==> Opens file at first occurrence of specified string pattern.

Common Techniques to Enter vi Insert Mode:

Enter Insert Command ==> Action

i ==> Insert text in front of the cursor.

a ==> Insert text after the cursor.

I ==> Insert text at the beginning of the line.

A ==> Insert text at the end of the line.

o ==> Insert text below the current line.

O ==> Insert text above the current line.

Useful vi Exit Commands

Exit Command ==> Action

:wq ==> Save and exit.

ZZ  ==> Save and exit.

:x  ==> Save and exit.

:w  ==> Save the current edits without exiting.

:w! ==> Override file protections and save.

:q  ==> Exit the file.

:q! ==> Exit without saving.

:n  ==> Edit next file.

:e! ==> Return to previously saved version.

Common Navigation Commands

Command               ==> Action

j (or down arrow)     ==> Move down a line.

k (or up arrow)       ==> Move up a line.

h (or left arrow)     ==> Move one character left.

l (or right arrow)    ==> Move one character right.

Ctrl+f (or Page Down) ==> Scroll down one screen.

Ctrl+b (or Page Up)   ==> Scroll up one screen.

1G ==> Go to first line in file.

G  ==> Go to last line in file.

nG ==> Go to n line number.

H  ==> Go to top of screen.

L  ==> Go to bottom of screen.

w  ==> Move one word forward.

b  ==> Move one word backward.

0  ==> Go to start of line.

$  ==> Go to end of line.

Common Options for Copying, Deleting, and Pasting Text

Option ==> Action

yy  ==> Yank (copy) the current line.

nyy ==> Yank (copy) n number of lines.

p   ==> Put yanked line(s) below the cursor.

P   ==> Put yanked line(s) above the cursor.

x   ==> Delete the character that the cursor is on.

X   ==> Delete the character to the left of the cursor.

dw  ==> Delete the word the cursor is currently on.

dd  ==> Delete current line of text.

ndd ==> Delete n lines of text

D   ==> Delete to the end of the current line.

Common Options for Changing Text

Option ==> Action

r  ==> Replace the character that the curser is on with the next character you type.

~  ==> Change the case of a character.

cc ==> Delete the current line and insert text.

C  ==> Delete to the end of the line and insert text.

c$ ==> Delete to the end of the line and insert text.

cw ==> Delete to the end of the word and insert text.

R  ==> Type over the characters in the current line.

s  ==> Delete the current character and insert text.

S  ==> Delete the current line and insert text.

Common Options for Text Searching

Option ==> Action

/ ==> Search forward for a string.

? ==> Search backward for a string.

n ==> Repeat the search forward.

N ==> Repeat the search backward.

f ==> Search forward for a character in the current line.

F ==> Search backward for a character in the current line.

:set number ==> Displaying Line Numbers

===================================================================

bg, fg and jobs Linux Commands

Every command you give is a job that is executed. A job can be suspended, placed in the background, moved back to the foreground or terminated.

While running a job you can        Shortcut

---------------------------            ----------

suspend a job                    ctrl+z

terminate a job                    ctrl+c

Function                            Command

--------                            ------------

Move a suspended job to the foreground        fg

Continue a suspended job in the background    bg

List all jobs                            jobs

Kill a job (%N where N is the job number)    kill %N && fg

Start a job directly in the background            command &

When you execute a unix shell-script or command that takes a long time, you can run it as a background job.

1. Executing a background job

$ find . -name "*.aud" -mtime +120 -exec rm {} \; &

2. Sending the current foreground job to the background using CTRL+Z and bg command

    step 1.Press 'CTRL+Z' which will suspend the current foreground job.

    step 2.Execute 'bg to' make that command to execute in background.

Press ‘CTRL+Z’

$ bg

3. View all the background jobs using jobs command.

$jobs

jobs    : lists the jobs that you are running in the background and in the foreground

jobs -p : list only the PID of process group leader

jobs -l : list only jobs that have change status since last notified by their status

jobs -r : resrict output to running jobs

jobs – s : restrict output to stopped jobs

4. Taking a job from the background to the foreground using fg command

$ fg

When executed without arguments, it will take the most recent background job to the below commands:

We have two Methods:

1. throw SCP:

$SCP -P <file Name> oracle@hostname:<destpath>/.

OR

Throw SFTP:

======================================================================================

SFTP

>sftp <user>@<hostname>

Connecting to <hostname>...

<user>'s Password:

sftp>pwd                   (remote working directory)

sftp>!pwd                  (local working directory)

sftp>cd /target/path/   (remote path)

sftp>pwd                   (remote working directory)

sftp>!ls -l                   (local working directory)

sftp>put <filename(s)>

Uploading <filename> to /target/path/<filename>

sftp>ls -l                    (remote location files)

sftp>bye

=============================================================================================

Linux Basic Commands at OS Level:

---------------------------------

!stty erase ^?

ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`

date

env

uptime

who -b

last | grep -i boot

ps -ef | grep pmon

ps -ef | grep tns

ps -ef | grep d.bin

df -h or df -g

uname

/etc/oratab or /etc/var/oracle/oratab

df -kh or df -kh . or df -kh /mountpoint/

du -sh * or du -sh .

du -sg *

ls -ltrh <filename> | sort -n

ls -ld

tellme system

lsof /mountpoint/

find /home -name oraInventory -print

find . -name "*.gz" -depth -mtime +60 -exec rm {} \;

nslookup

tnsping

top

bg, fg, jobs

===========================

It find the particular file locations using below commands

-----------------------------------------------------------------

To find a file/directory

------------------------

find /home -name oraInventory -print

find /home|grep oraInventory

To delete files older than 60 days

----------------------------------

find . -name "*.gz" -depth -mtime +60 -exec rm {} \; 

Take the backups using the below commands(Oracle Home backups & Listner backups)

=================================================================================

TAR(Realtime commands):

tar -cvf newname.tar directory_name (to tar)

tar -xvf filename.tar (to untar)

========================================================

 COMPRESS & Delete FILES at OS LEVEL:

==================================

To list files dated Apr 24

--------------------------

ls -lrt | grep 'Apr 24' | awk -F' ' '{print$9}'

To delete files dated Apr 24

----------------------------

rm -rf `ls -lrt | grep 'Apr 24' | awk -F' ' '{print$9}'`

To list files of Month Apr

--------------------------

ls -lrt | grep 'Apr' | awk -F' ' '{print$9}'

ls -lrt *.trc|grep 'Apr'|xargs rm -rf  {}\;

To delete files of Month Apr

----------------------------

rm -rf `ls -lrt | grep 'Apr' | awk -F' ' '{print$9}'`

To list files older than 60 days

--------------------------------

find . -name "*.trc" -depth -mtime +60 -exec ls -l {} \; 

find /path/to/files* -mtime +60 -print

To delete files older than 60 days

----------------------------------

find . -name "*.trc" -depth -mtime +60 -exec rm {} \; 

find /path/to/files* -mtime +60 -exec rm {} \;

find /path/to/files* -type f -mtime +60 -print0 | xargs -r rm -rf

Below COMPRESS command::

-----------------------

nohup compress *.arc &

ls -lrt *.arc|awk '{print "compress "$9}' >ARC_LIST.txt

SOURCE : Internet

References:

http://eisabainyo.net/weblog/useful-linux-commands/

=================================================================

 REBOOT Server:

===============

If its Dataguard setup or normal db, following steps mandatory just take backup of following three steps and store it in separate notepad. once server reboot activity 

completed cross check once which was taken before server reboot.

===============================================================

ps -ef|grep pmon  

ps -ef|grep pmon|wc -l

ps -ef|grep inh (OR) ps -ef|grep tns

ps -ef|grep inh|wc -l

ps -ef |grep d.bin

hostname

date

uname -a

cat /etc/oratab  /  cat /var/opt/oracle/oratab

uptime 

who -b

df -h  / df -gt

-------------------------------------------------------------------------------------------

Reboot Server  time database side need to  check the below commands: 

(Before server reboot and after server boot, if its is DG database)

-------------------------------------------------------------------

SQL> select name,db_unique_name,database_role,controlfile_type,CREATED from v$database;

SQL >select sequence#,first_time,next_time,completion_time,applied from v$archived_log where applied <> 'YES' and DEST_ID !=0 and status!='D'and completion_time <

(sysdate-1/48) Order By 1;

no rows selected----->in sync

SQL >select process, status ,sequence# from v$managed_standby;

MRP0---->process should reflect

===============================

Realtime Data Guard Db Startup Procedure

===============================

Sqlplus “/ as sysdba”

startup nomount;

alter database mount standby database;

recover managed standby database disconnect from session; ----------->To put it in MRM MODE

exit;

=================================

Realtime Data Guard Db  Shutdown Procedure

=================================

Login as oracle

source the environment

sqlplus “/ as sysdba”

alter database recover managed standby database cancel; ------> to cancel MRM mode

shutdown immediate;

==================================================

Tablespaces Creation steps

Tablespaces Creation Steps:

Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

Concurrency and speed of space operations is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
To create a locally managed tablespace give the following command

SQL> CREATE TABLESPACE ravi DATAFILE '/u02/oracle/ravi/ravi01.dbf' SIZE 100M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
             

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause

of UNIFORM. If you omit SIZE, then the default size is 1M. The following example creates a Locally managed tablespace with uniform extent size of 256K

SQL> CREATE TABLESPACE ravi DATAFILE '/u02/oracle/ravi/ravi01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

 Create Dictionary Managed Tablespace

SQL> CREATE TABLESPACE ravi_lmt DATAFILE '/u02/oracle/ravi/ravi01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY;

Bigfile Tablespaces (Introduced in Oracle Ver. 10g)
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple

datafiles, but the files cannot be as large. Bigfile tablespaces can reduce the number of datafiles needed for a database.

 create a bigfile tablespace given the below inputs.

SQL> CREATE BIGFILE TABLESPACE ravi_bigtbs
    DATAFILE '/u02/oracle/ravi/bigtbs01.dbf' SIZE 30G;

Process 1:

Re- Size the tablespace in below inputs.

To increasing the size of an existing datafile in below given inputs.

SQL> alter  database ravi datafile ‘/u01/oracle/data/ravi01.dbf’ resize 100M;


Process 2:

To resize of a tablespace by adding a new datafile to a tablespace. Please find the below inputs for resie the tablespace.

SQL> alter tablespace add datafile  ‘/u02/oracle/ravi/ravitbs02.dbf’ size 100M;


Process 3:


Oracle will automatravilly increase the size of a datafile whenever space is required. You can specify by how much size the file should increase and Maximum size to

which it should extending.

Existing datafile auto extendable in below given inputs.

SQL> alter database datafile ‘/u01/oracle/ravi/ravitbs01.dbf’ auto extend ON next 5M maxsize 500M;

Extendable while creating a new tablespace in below given inputs.

SQL> create tablespace ravi datafile ‘/u01/oracle/ravi/ravitbs01.dbf’ size 50M auto extend ON next 5M maxsize 500M;

 Decrease the size of a tablespace:

Decrese the size of tablespace by decreasing the datafile associated with it. Decrease a datafile only up to size of empty space in it.

SQL> alter database datafile ‘/u01/oracle/ravi/ravitbs01.dbf’  resize 30M;


Making tablespaces Offline or Online:

Online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to
access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users.

Alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

 Make a Tablespace Offline in below given inputs.

SQL>alter tablespace ravi offline;

Make a bring it back online in below given inputs.

SQL>alter tablespace ravi online;

Make individual datafile offline type the following command

SQL>alter database datafile ‘/u01/oracle/ravi/ravi_tbs01.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/oracle/ravi/ravi_tbs01.dbf’ online;

Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode.  If the datafile has become corrupt or missing when the database is

running in NOARCHIVELOG mode then you can only drop it by giving the following command

SQL>alter database datafile ‘/u01/oracle/ravi/ravi_tbs01.dbf’ offline for drop;

Making a Tablespace Read only.
Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to

perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historravil data so that users cannot modify

it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.

To make a tablespace read only

SQL>alter tablespace ravi read only

Again to make it read write

SQL>alter tablespace ravi read write;

Renaming Tablespaces
Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users

tablespace:

ALTER TABLESPACE users RENAME TO usersts;


The following affect the operation of this statement:

The COMPATIBLE parameter must be set to 10.0 or higher.
If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.
If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.
Dropping Tablespaces
You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You

must have the DROP TABLESPACE system privilege to drop a tablespace.

Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped

will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely

To drop a tablespace give the following command.

SQL> drop tablespace ravi;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it anyhow then add the following keyword

SQL>drop tablespace ravi including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.

But If  you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace ravi including contents and datafiles;

Temporary Tablespace
Temporary tablespace is used for sorting large tables. Every database should have one temporary tablespace. To create temporary tablespace give the following command.

SQL>create temporary tablespace temp tempfile ‘/u01/oracle/data/ravi_temp.dbf’ size 100M
   extent management local  uniform size 5M;

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size.  The

AUTOALLOCATE clause is not allowed for temporary tablespaces.

Increasing or Decreasing the size of a Temporary Tablespace
You can use the resize clause to increase or decrease the size of a temporary tablespace. The following statement resizes a temporary file:

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a temporary file and deletes the operating system file:

SQL> ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP  INCLUDING DATAFILES;

Tablespace Groups
A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:


Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort,

particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary

tablespaces.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

Creating a Temporary Tablespace Group
You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the

specified tablespace group does not currently exist.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

CREATE TEMPORARY TABLESPACE ravi_temp2 TEMPFILE '/u02/oracle/ravi/ravi_temp.dbf'
     SIZE 50M TABLESPACE GROUP group1;

ALTER TABLESPACE ravi_temp2 TABLESPACE GROUP group2;


Assigning a Tablespace Group as the Default Temporary Tablespace
Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:

ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;