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 [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.