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.

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 

MONTH@VOYAGER> @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. 

MONTH@VOYAGER> create table testddl 
2  tablespace voyager_data 
3  as select * from prof 
4  where 1=2; 

Table created. 

MONTH@VOYAGER> @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.

This was last published in August 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close