Help with query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help with query

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013

    Help with query

    Since I no longer have access to Oracle Enterprise Manager, I wrote the following queries to pull back inofrmation about the tablespaces and table.

    I would appreciate any constructive critisism and suggestions that people have. So far I haven't found an existing query that gives me the information that I want.

    Thanks in advance.

    Code:
    COL "Table Name" FORMAT A20
    COL "TS Name"    FORMAT A10
    
    SELECT dba_tablespaces.tablespace_name               "TS Name", 
           dba_tablespaces.extent_management             "TS Type",
           ROUND(dba_tablespaces.initial_extent/1024, 2) "Init (KB)",
           ROUND(dba_tablespaces.min_extlen/1024, 2)     "Min  (KB)",
           dba_segments.KBUsed                           "Used (KB)",
           NVL(dba_data_files.FreeKBytes, 0)             "Free (KB)",
           dba_data_files.MaxKBytes                      "Max  (KB)",
           dba_tablespaces.pct_increase                  "% Increase"
      FROM dba_tablespaces,
           ( SELECT tablespace_name, 
                    ROUND(SUM(bytes)/1024, 2) KBUsed
               FROM dba_segments
              GROUP BY tablespace_name ) dba_segments,
           ( SELECT dba_data_files.tablespace_name, 
                    ROUND(SUM(maxbytes)/1024, 2)                  MaxKBytes,
                    ROUND(SUM(user_free_space.bytes)/1024, 2)     FreeKBytes
               FROM dba_data_files
               LEFT OUTER JOIN
                 ( SELECT tablespace_name, bytes
                     FROM dba_free_space ) user_free_space                ON
                          dba_data_files.tablespace_name  = 
                          user_free_space.tablespace_name
              GROUP BY dba_data_files.tablespace_name ) dba_data_files
     WHERE dba_tablespaces.tablespace_name = dba_segments.tablespace_name AND
           dba_tablespaces.tablespace_name = dba_data_files.tablespace_name
     ORDER BY dba_tablespaces.tablespace_name;
    
    SELECT all_tables.table_name                   "Table Name",
           all_tables.tablespace_name              "TS Name",
           all_tables.pct_free                     "% Free",
    		 all_tables.pct_used                     "% Used",
           init_extents.InitKB                     "Init (KB)",
    		 all_tables.next_extent                  "Next (KB)",
    		 dba_extents.KBUsed                      "KB Used",
           dba_extents.KBFree                      "KB Free",
    		 dba_extents.TotalExtents                "Total Extents",
           all_tables.max_extents                  "Max Extents"
      FROM all_tables,
        ( SELECT segment_name, ROUND(SUM(bytes)/1024, 2) InitKB
            FROM dba_extents
           WHERE segment_type = 'TABLE'                           AND
                 owner        = 'FIN'                             AND
                 (segment_name, extent_id)                        IN 
                 (SELECT segment_name, MIN(extent_id)
                    FROM dba_extents
                   WHERE segment_type = 'TABLE'                   AND
                         owner = 'FIN'
                   GROUP BY segment_name)
           GROUP BY segment_name ) init_extents, 
        ( SELECT segment_name, 
                 ROUND(SUM(dba_extents.bytes)/1024, 2)     KBUsed,
                 ROUND(SUM(user_free_space.bytes)/1024, 2) KBFree,
                 COUNT(*) TotalExtents
            FROM dba_extents
            LEFT OUTER JOIN
             ( SELECT block_id, bytes
                 FROM dba_free_space ) user_free_space            ON
                      dba_extents.block_id      = 
                      user_free_space.block_id
                WHERE segment_type = 'TABLE'                      AND
                      owner        = 'FIN'
                GROUP BY dba_extents.segment_name ) dba_extents
       WHERE all_tables.table_name = init_extents.segment_name    AND
             all_tables.table_name = dba_extents.segment_name;
    this space intentionally left blank

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Thumbs up

    Try this link


    PS: You still could have OEM access if you are granted the SELECT_CATALOG_ROLE.
    Last edited by LKBrwn_DBA; 11-12-2004 at 01:10 PM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It depends on what you want!

    One thing I calculate for tables is a "data space required" as AVG_ROW_LEN*NUM_ROWS/(1 - PCTFREE/100) and compare this to the total size to give a "wasted space" value. Big values here pointed me to some probs with Auto Seg Space Mgmnt only using 10% of each block.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    Originally posted by LKBrwn_DBA
    Try this link


    PS: You still could have OEM access if you are granted the SELECT_CATALOG_ROLE.
    Thanks!
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    Originally posted by DaPi
    It depends on what you want!

    One thing I calculate for tables is a "data space required" as AVG_ROW_LEN*NUM_ROWS/(1 - PCTFREE/100) and compare this to the total size to give a "wasted space" value. Big values here pointed me to some probs with Auto Seg Space Mgmnt only using 10% of each block.
    That is an interesting formula, but unfortunately I need the dba's to compute stats to make use of it.
    this space intentionally left blank

Posting Permissions

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



Click Here to Expand Forum to Full Width