Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    17

    Unanswered: Create Table - Execution

    Hi

    Could anyone let me know what happens (sequence of steps)
    Oracle takes in executing a create table command...

    I would like to know - How the v$ views are affected...?
    How it locates a block to write this information ?
    Where it maintains the table info...?


    May be a flowchart picture somewhere...or any doc that explains...
    it would be awesome...

    Thanks,
    -A.
    Last edited by tech_punter; 10-05-03 at 16:48.

  2. #2
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Do an explain plan for create table x.... to get what you want...trace it using /setting your sql_trace to true and hten use tkprof to try to interpret what all happens...

    for instance here...

    I create a table...

    Code:
    sys@NICK817.TARRY.LOCAL> alter session set timed_statistics = true
      2  /
    
    Session altered.
    
    sys@NICK817.TARRY.LOCAL> alter session set sql_trace = true
      2  /
    
    Session altered.
    
    sys@NICK817.TARRY.LOCAL> create table t
      2  (x int,
      3  y char(5),
      4  z varchar2(10))
      5  /
    
    Table created.
    
    sys@NICK817.TARRY.LOCAL> select x.spid
      2  from v$process x, v$session y
      3  where x.addr = y.paddr
      4  and y.audsid= userenv('sessionid')
      5  /
    
    SPID
    ---------
    3316
    980
    2064
    3348
    3368
    3400
    3412
    2468
    2468
    
    9 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   MERGE JOIN
       2    1     SORT (JOIN)
       3    2       FIXED TABLE (FULL) OF 'X$KSUSE'
       4    1     SORT (JOIN)
       5    4       FIXED TABLE (FULL) OF 'X$KSUPR'
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              0  consistent gets
              0  physical reads
              0  redo size
            684  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
              9  rows processed
    
    sys@NICK817.TARRY.LOCAL>
    sys@NICK817.TARRY.LOCAL>
    
    sys@NICK817.TARRY.LOCAL> exit
    Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.1 - Production
    
    /* Tracing stops as well...don't keep it running you udump will fill up like mad in a prod env.. */
    
    
    E:\oracle\admin\nick817\udump>tkprof ora02468 my_rpt.txt
    
    TKPROF: Release 9.2.0.3.0 - Production on Sun Oct 5 22:16:08 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    E:\oracle\admin\nick817\udump>sqlplus
    
    SQL*Plus: Release 9.2.0.3.0 - Production on Sun Oct 5 22:23:50 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Enter user-name: appdev@nick817
    Enter password:
    
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.1 - Production
    
    appdev@NICK817.TARRY.LOCAL> drop table t
      2  /
    
    Table dropped.
    
    appdev@NICK817.TARRY.LOCAL>
    I've attached a trace file there you can see that obj$ and fet$ tables will be of some interest to you. In fatc these are the tables which experienced dba's also used to mine the pre 9i logfiles for rogue dml/ddl activities.

    Try to figure out what happens there in the trace. Although it's a mere ddl. You'd want to be more interested in the SARG's and the other dml's


    Cheers!
    Attached Files Attached Files
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •