Tip

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 

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>

    Requires Free Membership to View

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 first published in August 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.