[INSTRUCTIONS] Managing Oracle Tablespaces - Quick Commands

[chrisplough]

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:

alter database datafile '/u01/oradata/otmdb/INDX01.dbf' resize 2000M;

Create a tablespace and associated datafile:

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:

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:

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:

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:

• Oracle Tablespaces
• http://www.adp-gmbh.ch/ora/sql/create_tablespace.htm

Hope this helps!

--Chris

[chrisplough]

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:

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:

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:

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:

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:

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