How to Drop existing undo tablespace ?

You cannot drop an undo tablespace unless you have another undo tablespace. I am demonstrating the scenario to drop the existing undo table and create a new one.

Purpose of dropping an existing may differ. I my case, I wanted to move undo tablespace to different mountpoint.


select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_data_files;


FILE_NAME                                      TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------                --------------------            ---------------------
/u01/app/oracle/oradata/TEST/users01.dbf        USERS                        .008544922

/u01/app/oracle/oradata/TEST/undotbs01.dbf     UNDOTBS1                      15.7519531

/u01/app/oracle/oradata/TEST/sysaux01.dbf       SYSAUX


Thats my existing undo tablespace, Now I will create a new undo tablespace.


create undo tablespace undo2 datafile  '/u02/oradata/TEST/undotbs_01.dbf' size 12G;

Tablespace created.


Now, make the new undo tablespace as default tablespace.


SQL> alter system set undo_tablespace=undo2 scope=both;

System altered.

Now we can go ahead and drop the old undo tablespace i.e. UNDOTBS1

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.



Good Luck!!!!

How to know when table was modified

Being a DBA.....many times we get a question. Can you please tell when this table was last updated. Or may be if any DML operations happened.

Here is the answer for your question.

select * from dba_tab_modifications
select * from all_tab_modifications


Example:


select TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from dba_tab_modifications where rownum<10 and table_owner='SYSMAN';

Sheet Name :- Sheet1

---

TABLE_OWNER	TABLE_NAME	INSERTS	UPDATES	DELETES	TIMESTAMP
SYSMAN	AQ$_EM_EVENT_BUS_TABLE_L	176	0	175	7-Apr-16
SYSMAN	BIN$LKnxq0szBVrgU7QDAwoS5A==$0	1	1	0	26-Feb-16
SYSMAN	DB_GI_OH_TARGET_DETAILS	0	1	0	7-Apr-16
SYSMAN	DB_INSTANCE_CAGING_ECM	11	17	12	7-Apr-16
SYSMAN	EM_AVAILABILITY	16	0	0	7-Apr-16
SYSMAN	EM_COMPOSITE_KEYS	32	0	0	15-Mar-16
SYSMAN	EM_CONSOLE_PERF_STATS_LOG	0	0	2	7-Apr-16
SYSMAN	EM_CPUACT_STATS	0	0	0	7-Apr-16
SYSMAN	EM_CREDENTIAL_SET_TYPES	8	0	0	29-Feb-16

---

Sheet Name :- Sheet2

---

---

Sheet Name :- Sheet3

---

---