2 Replies Latest reply: Jul 29, 2012 3:49 AM by 808794 RSS

    How to know the select stmt used in creating a table?

    808794
      Hi,
      If we create a table by selecting some records form another table, can we see the select statement used to create that table.
      example: Create table emp2 as select * from emp where deptno=20;
      is there any data dictionary table which stores select * from emp where deptno=20

      Thanks in advance.

      Edited by: 805791 on Jul 28, 2012 9:01 PM
        • 1. Re: How to know the select stmt used in creating a table?
          JustinCave
          No. The SELECT statement is not associated with the table in the data dictionary.

          If the statement was executed recently, you may find the entire CREATE TABLE in the V$SQL view. Or, if you have licensed the AWR, you may find it in the DBA_HIST_SQLTEXT view. But there is no guarantee that the query will be in either view.

          Justin

          Edited by: Justin Cave on Jul 29, 2012 3:46 AM

          Additionally, this question doesn't appear to relate to the Oracle Call Interface so it should probably have been asked in a different forum. The General Questions forum or the SQL and PL/SQL forum would have been more appropriate.
          • 2. Re: How to know the select stmt used in creating a table?
            808794
            Thanks Justin.
            I wanted to post this in SQL section. I did not observe this until you mentioned that.