Announcement

Collapse
No announcement yet.

[INSTRUCTIONS] Managing Oracle Tablespaces - Quick Commands

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [INSTRUCTIONS] Managing Oracle Tablespaces - Quick Commands

    Hello!

    I've got a friend who is learning how to install OTM and isn't a very strong DBA. He's been asking about commands for modifying and creating tablespaces within an Oracle database.

    While these are readily available on the internet, I feel they're applicable enough to OTM to warrant a post here.

    Resize a datafile:
    Code:
    alter database datafile '/u01/oradata/otmdb/INDX01.dbf' resize 2000M;
    Create a tablespace and associated datafile:
    Code:
    create tablespace BPL_DAY7
      logging
      datafile '/u01/oradata/otmdb/BPL_DAY701.dbf' 
      size 100M
      autoextend on
      next 100M maxsize 2000M
      extent management local;
    Create a temporary tablespace and associated datafile:
    Code:
    create temporary tablespace TEMP 
      tempfile '/u01/oradata/otmdb/TEMP01.dbf' 
      size 1000m 
      autoextend on 
      next 100m maxsize 2000m
      extent management local;
    Add a datafile to an existing tablespace:
    Code:
    alter tablespace INDX
      add datafile '/u01/oradata/otmdb/INDX02.dbf' size 2000M autoextend on next 100m maxsize 4000M;
    Add a datafile to an existing temporary tablespace:
    Code:
    ALTER TABLESPACE TEMP
        add tempfile '/u01/oradata/otmdb/TEMP02.dbf' size 1000M autoextend on next 100m maxsize 2000M;
    In addition, here are some great sites for getting more detailed commands and info:Hope this helps!

    --Chris

  • #2
    Re: [INSTRUCTIONS] Managing Oracle Tablespaces - Quick Commands

    Some more commands that I've found useful recently for managing Oracle tablespaces.

    Keep in mind that I'm not a DBA, so there may be better ways to achieve

    To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:
    Code:
    SELECT TABLESPACE_NAME "TABLESPACE",
       INITIAL_EXTENT "INITIAL_EXT",
       NEXT_EXTENT "NEXT_EXT",
       MIN_EXTENTS "MIN_EXT",
       MAX_EXTENTS "MAX_EXT",
       PCT_INCREASE
       FROM DBA_TABLESPACES;

    To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:
    Code:
    SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
       FROM DBA_DATA_FILES;


    To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:
    Code:
    SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
       COUNT(*)    "PIECES",
       MAX(blocks) "MAXIMUM",
       MIN(blocks) "MINIMUM",
       AVG(blocks) "AVERAGE",
       SUM(blocks) "TOTAL"
       FROM DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME, FILE_ID;
    To Analyze single tables:
    Code:
    EXEC dbms_stats.gather_table_stats('GLOGOWNER','SHIPMENT',cascade=>TRUE);
    EXEC dbms_stats.gather_table_stats('GLOGOWNER','S_EQUIPMENT',cascade=>TRUE);
    EXEC dbms_stats.gather_table_stats('GLOGOWNER','SHIPMENT_S_EQUIPMENT_JOIN',cascade=>TRUE);
    
    EXEC dbms_stats.gather_schema_stats( OWNNAME => 'GLOGOWNER',
        ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
        OPTIONS => 'GATHER STALE',
        CASCADE => TRUE,
        DEGREE => 4);
    To see per-user usage of the TEMP temporary tablespace:
    Code:
    column tablespace format a12
    column username   format a12
    
    break on username nodup skip 1
    
    select   se.username
            ,se.sid
            ,su.extents
            ,su.blocks * to_number(rtrim(p.value)) as Space
            ,tablespace
            ,segtype
    from     v$sort_usage su
            ,v$parameter  p
            ,v$session    se
    where    p.name          = 'db_block_size'
    and      su.session_addr = se.saddr
    order by se.username, se.sid
    /
    Hope this helps!!

    Thanks,
    Chris
    Last edited by chrisplough; September 18, 2007, 06:48.

    Comment

    Working...
    X