how to check the creation date of tables in SQL?

Question

The question is:

List of the DW1_ tables from the USER_OBJECTS with table names, creation date and time in ISO standard.

that's what I have so far, but it does not work.

select table_name, to_char(create_date, 'yyyy-mm-dd') from all_tables 
where table_name like 'DW1%'
group by table_name;

It said create_date is invalid identifier.

Can anyone help me with this question?

Create Date is not a field in all_tables from my understanding... — sgeddes, Mar 15 '13 at 03:04
should be from user_objects not all_tables, but I still dont know which method I could use here. — user2169562, Mar 15 '13 at 03:06
http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle — PM 77-1, Mar 15 '13 at 03:07
select object_name, to_char(created, 'yyyy-mm-dd') "Creation date" from user_objects where object_name like 'DW1%'; thx everyone anyways — user2169562, Mar 15 '13 at 03:10
If you're looking for table names that begin with "DW1_", what you need is: table_name like 'DW1\_%' escape '\' — David Aldridge, Mar 15 '13 at 08:17

score 6 · Answer 1 · answered Mar 15 '13 at 03:09

6

Is this what you're looking for?

select object_name, created 
from user_objects 
where object_name LIKE 'DW1%' 
    and object_type = 'TABLE';

SQL Fiddle Demo

answered Mar 15 '13 at 03:09

sgeddes

62,311
6
61
83

Why do I prefer this answer, using `user_objects`, compared to those using `dba_objects`? Because often there are no read permissions on `dba_objects`, but on `user_objects`. – t0r0X Nov 13 '21 at 00:21

score 1 · Answer 2 · answered Mar 15 '13 at 12:45

1

select object_name, to_char(created, 'yyyy-mm-dd') AS created -- 'AS' is optional
  from user_objects 
 where object_name like 'EMP%' -- replace with your table
/

answered Mar 15 '13 at 12:45

Art

5,616
1
20
22

score 0 · Answer 3 · answered Mar 15 '13 at 03:07

0

try this

SELECT object_name tablename, created FROM dba_objects
WHERE object_name like 'DW1%'
and object_type = 'TABLE'

answered Mar 15 '13 at 03:07

rs.

26,707
12
68
90

how to check the creation date of tables in SQL?

3 Answers3