Table spaces and users 11G

SHOW TABLE SPACE IN ENTIRE DATABASE 

select TABLESPACE_NAME

from DBA_DATA_FILES

SHOW TABLE SPACE IN ENTIRE DATABASE

select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by 

from dba_data_files

order by file_id;

Output should be 

SYSTEM   /u01/finsys/db/apps_st/data1/system01.dbf   1000     NO       0

SYSTEM   /u01/finsys/db/apps_st/data1/system02.dbf   1000     NO       0

SYSTEM   /u01/finsys/db/apps_st/data1/system03.dbf   1000     NO       0

SHOW TABLE SPACE IN ENTIRE DATABASE WITH RMAN

RMAN> REPORT SCHEMA;

Output should be 

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    1000     SYSTEM               ***     /u01/finsys/db/apps_st/data1/system01.dbf

2    1000     SYSTEM               ***     /u01/finsys/db/apps_st/data1/system02.dbf

3    1000     SYSTEM               ***     /u01/finsys/db/apps_st/data1/system03.dbf

4    1000     SYSTEM               ***     /u01/finsys/db/apps_st/data1/system04.dbf

5    1000     SYSTEM               ***     /u01/finsys/db/apps_st/data1/system05.dbf

6    40       CTXD                 ***     /u01/finsys/db/apps_st/data1/ctxd01.dbf

DATA FILES CREATION   DATE

select to_char(CREATION_TIME,'DD-MM-RRRR HH24:MM:SS'),name from v$datafile;

Output should be 

01-09-2010 16:09:55       /u01/finsys/db/apps_st/data1/ctxd02.dbf

02-09-2010 16:09:06       /u01/finsys/db/apps_st/data1/a_nolog02.dbf

03-01-2012 08:01:26       /u01/finsys/db/apps_st/data1/a_txn_data06.dbf

CREATE TABLESPACE

CREATE tablespace  thilinadata

datafile ‘/u01/finsys/db/apps_st/data1/thilinadata.dbf'

size 1G;

Add data file to TABLESPACE 

ALTER TABLESPACE thilinadata

ADD DATAFILE '/u01/finsys/db/apps_st/data1/thilinadata1.dbf''

SIZE 1000M;

CREATE USERS PASSWORD AND SET DEFAULT TABLESPACE

create user thilina

identified by thilina

default tablespace thilinadata;

CHANGE USERS DEFAULT TABLESPACE AND PASSWORD 

ALTER USER thilina

IDENTIFIED BY thilina;

DEFAULT TABLESPACE thilinadata; 

CHANGE USERS DEFAULT TABLESPACE

CREATE USER thilina

DEFAULT TABLESPACE thilinadata;

DROP USERS 

DROP USER THILINA

LOCK USERS 

ALTER USER THILINA ACCOUNT LOCK;  

UNLOCK USERS 

ALTER USER THILINA ACCOUNT UNLOCK;  

RENAME TABLESPACE

ALTER TABLESPACE ts_current_name RENAME TO ts_new_name;

DROP TABLESPACE

DROP TABLESPACE THILINA;

DROP TABLESPACE THILINA INCLUDING CONTENTS;

DROP TABLESPACE thilinadata INCLUDING CONTENTS AND DATAFILES;

USERS AND DEFAULT TABLESPACE

SELECT username,default_tablespace,account_status  FROM  DBA_USERS WHERE USERNAME='THILINA';