11 Replies Latest reply: Feb 24, 2014 9:09 PM by Hemant K Chitale RSS

    SELECT object_name using sysdate

    User235837 -Oracle

      Hi,

       

        Iam using 11g r2 i need to get object details which are created after jan - 2 -2014 , can anyone please help me to generate the select statement.

       

      using owner , object_type ...

       

      Thanks,

        • 1. Re: SELECT object_name using sysdate
          sb92075

          User235837 - Oracle wrote:

           

          Hi,

           

            Iam using 11g r2 i need to get object details which are created after jan - 2 -2014 , can anyone please help me to generate the select statement.

           

          using owner , object_type ...

           

          Thanks,

           

          Your posting history shows you have been here for three years.

          How is it that you still can't write your own basic SELECT statement?

           

          SELECT owner,

                 object_name,

                 object_type

          FROM   dba_objects

          WHERE  created > To_date('2014-01-02', 'YYYY-MM-DD');

          • 2. Re: SELECT object_name using sysdate
            AK - Oracle

            SELECT owner, object_name from dba_objects where creates > to_date('2014-01-02', 'YYYY-MM-DD');

            • 3. Re: SELECT object_name using sysdate
              sb92075

              AK - Oracle wrote:

               

              SELECT owner, object_name from dba_objects where creates > to_date('2014-01-02', 'YYYY-MM-DD');

               

              Close, but no cigar.

              you did not include OBJECT_TYPE which OP requested.

              • 4. Re: SELECT object_name using sysdate
                Partha Sarathy S

                I don't mean you should not ask these type of questions. But it would be better, you can do these things by just querying oracle internal views or tables. So you need details about object types. Just do

                 

                desc dba_objects

                This shows you the list of columns this view has. From this, you can just get the created column and write a select query.

                • 5. Re: SELECT object_name using sysdate
                  Mark D Powell

                  User235837, as Parth pointed out you could have probably figured out how to write this query faster by just describing the view and making an attempt at the query than posting the question.  To help with working with the Oracle rdbms dictionary Oracle has documented the dictionary views, database parameters, and dynamic performance views in the Oracle version# Reference manual.  All the manuals are available online.  You can download copies from OTN.  Though to be honest the documentation is a little thin on many of these objects, that is, the column descriptions for many v$ views are fairly vague but the manual is a good supplement to describe.

                  - -

                  Also you should consider adding status to SB's query.  Knowing the object in question is VALID or INVALID is often useful when looking into issues related to table triggers, stored procedures, packages, or functions.

                  - -

                  HTH -- Mark D Powell --

                  • 6. Re: SELECT object_name using sysdate
                    JohnWatson

                    Using dba_objects.created is not always reliable, some operations will distort it. For example,

                     

                    orclz> select object_name,created,last_ddl_time from user_objects where object_name like '%DEPT';

                     

                    OBJECT_NAME                    CREATED             LAST_DDL_TIME

                    ------------------------------ ------------------- -------------------

                    DEPT                           2014-02-21 16:16:11 2014-02-21 16:16:11

                    PK_DEPT                        2014-02-21 16:16:11 2014-02-21 16:16:11

                     

                    orclz> exec dbms_redefinition.redef_table(user,'DEPT',table_part_tablespace=>'EXAMPLE')

                     

                    PL/SQL procedure successfully completed.

                     

                    orclz> select object_name,created,last_ddl_time from user_objects where object_name like '%DEPT';

                     

                    OBJECT_NAME                    CREATED             LAST_DDL_TIME

                    ------------------------------ ------------------- -------------------

                    DEPT                           2014-02-22 14:20:49 2014-02-21 16:16:11

                    PK_DEPT                        2014-02-22 14:20:52 2014-02-22 14:21:00

                     

                    orclz>

                    • 7. Re: SELECT object_name using sysdate
                      Mark D Powell

                      John, nice information.  If I were to have guessed off the top of my head I would have thought that Oracle would have either retained the created date at the original date and just updated the last_ddl_time for a recreate via dbms_redefinition or have set both columns to the same re-created date/time.  Obviously that is not true.  What Oracle versions do you know this is true for?  -- Mark --

                      • 8. Re: SELECT object_name using sysdate
                        Dave Rabone

                        Posters with handles

                         

                        where translate ( lower ( handle),  '  -_', '' ) like '%oracle' 

                         

                        are supposed to be Oracle employees (see Community Forum).

                        OP is to be commended for using initiative to find out what they need to know, but I'm not sure that it's a good look for Oracle's training.

                        • 9. Re: SELECT object_name using sysdate
                          JohnWatson

                          I've done a couple more experiments (all with 12.1) involving ALTER TABLE MOVE and ALTER INDEX REBUILD (same OBJECT_ID, new DATA_OBJECT_ID), and ALTER TABLE EXCHANGE PARTITION (different OBJECT_ID, same DATA_OBJECT_ID). In all cases the CREATED value follows the OBJECT_ID.

                          DBMS_REDEFINITION changes OBJECT_ID as well as DATA_OBJECT_ID. Perhaps that is the reason for the anomalous behaviour.

                          • 10. Re: SELECT object_name using sysdate
                            Mark D Powell

                            Thanks John.  Now if I can just remember this.  -- Mark --

                            • 11. Re: SELECT object_name using sysdate
                              Hemant K Chitale

                              DATA_OBJECT_ID changes because the underlying Segment is a new segment.  DBMS_REDEFINITION actually requires creating a new *table*, thus a new Object (OBJECT_ID)

                               

                               

                              Hemant K Chitale