Last DDL time by tablespace

There are times when one may need to know when the last DDL was performed on a particular tablespace. Here's how to find out. The code is saved in a file called last_ddl.sql. At the SQL Prompt the user enters:

SQL> @last_ddl 

The user should have SELECT privileges on DBA_OBJECTS and DBA_SEGMENTS. This script has been tested on Oracle versions 7.3.4.5 and 8.1.7. Here is the code:

alter session set nls_date_format='DD-MON-YYYY HH:MI:SS'; 
select 'Last DDL by Tablespace as on ' ||substr(to_char(sysdate,'DD-MON-YYYY    HH2 
4:MI:SS'),1,35) today from dual; 
select  b.tablespace_name, 
to_date(max(a.last_ddl_time),'DD-MON-YYYY HH:MI:SS') LAST_DDL_TIME 
from dba_objects a, dba_segments b 
where a.owner=b.owner 
and a.object_name=b.segment_name 
group by b.tablespace_name 
/ 

Here is the resulting output:

RESULT of the TEST are as Under 
---------------------------- 
Connected to: 
Oracle7 Server Release 7.3.4.5.0 - Production 
With the distributed, replication and parallel query options 
PL/SQL Release 2.3.4.5.0 - Production 


USERNAME 
------------------------------ 
MONTH 

[email protected]> @last_ddl 

Session altered. 

TODAY 
---------------------------------------------------------------- 
Last DDL by Tablespace as on 19-MAR-2002 09:49:45 

TABLESPACE_NAME                LAST_DDL_TIME 
------------------------------ -------------------- 
CALLWEEKLY_DATA                 18-MAR-2002 10:48:09 
CALLWEEKLY_INDEX                01-MAR-2002 12:45:22 
REP_DATA                        06-MAR-2002 08:25:12 
REP_INDEX                       16-MAR-2001 11:39:03 
SYSTEM                          15-MAR-2000 10:56:11 
TOOLS                           13-MAR-2000 10:47:02 
VOYAGER_DATA                    19-MAR-2002 09:09:39 
VOYAGER_INDEX                   19-MAR-2002 01:10:21 

8 rows selected. 

[email protected]> create table testddl 
2  tablespace voyager_data 
3  as select * from prof 
4  where 1=2; 

Table created. 

[email protected]> @last_ddl 

Session altered. 

TODAY 
---------------------------------------------------------------- 
Last DDL by Tablespace as on 19-MAR-2002 09:50:21 

TABLESPACE_NAME                LAST_DDL_TIME 
------------------------------ -------------------- 
CALLWEEKLY_DATA                 18-MAR-2002 10:48:09 
CALLWEEKLY_INDEX                01-MAR-2002 12:45:22 
REP_DATA                        06-MAR-2002 08:25:12 
REP_INDEX                       16-MAR-2001 11:39:03 
SYSTEM                          15-MAR-2000 10:56:11 
TOOLS                           13-MAR-2000 10:47:02 
VOYAGER_DATA                    19-MAR-2002 09:50:15 
VOYAGER_INDEX                   19-MAR-2002 01:10:21 
------------------------------------ 
8 rows selected. 

Reader Feedback

Peter M. writes: This is a handy script, but is the use of the data formatting in the various select statements not superfluous due to the setting of the NLS_DATE_FORMAT?

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, relational model, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.