Monday 7 November 2011

oracle tablespaces


Data Dictionary Objects
Tablespaces
ts$
dba_tablespaces
user_tablespaces
Tablespace Quotas
tsq$
dba_ts_quotas
user_ts_quotas
Data Files
dba_data_files
v_$backup_datafile
v_$datafile
v_$datafile_copy
v_$datafile_header
Free Space
dba_free_space
Segments
dba_segments
v_$segment_statistics
Extents
dba_extents
Blocks
v_$database_block_corruption
Groups
dba_tablespace_groups
SYSAUX Tablespace
v_$sysaux_occupants
Temp Tablespace
dba_temp_files
Undo Tablespace
dba_rollback_segs
dba_undo_extents
v_$rollstat
v_$undostat
Transportable Tablespaces
transport_set_violations
Dictionary Management
fet$
uet$

System Privileges
alter tablespace
drop tablespace
unlimited tablespace
create tablespace
manage tablespace

GRANT create tablespace TO uwclass;
GRANT alter tablespace TO uwclass;
GRANT DROP tablespace TO uwclass;
GRANT manage tablespace TO uwclass;
GRANT unlimited tablespace TO uwclass;  
Permanent Tablespace
Permanent Tablespace On A File System Without Auto-extend
CREATE [<BIGFILE | SMALLFILE>] TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T | P | E> [REUSE] AUTOEXTEND <OFF | ON>
BLOCKSIZE <bytes>
[<LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING>]
[FORCE LOGGING]
[ENCRYPTION USING '<encryption_algorithm>'
 IDENTIFIED BY <password> [NO] SALT]
[DEFAULT <COMPRESS [FOR <ALL | DIRECT_LOAD> OPERATIONS] | NOCOMPRESS>]
[<ONLINE | OFFLINE>]
EXTENT MANAGEMENT LOCAL <AUTOALLOCATE | UNIFORM SIZE <extent_size>>
[SEGMENT SPACE MANAGEMENT <AUTO | MANUAL>]
[FLASHBACK <ON | OFF>]; CREATE TABLESPACE uwdata DATAFILE
'c:\oracle\oradata\orabase\uwdata01.dbf' SIZE 150M,
'c:\oracle\oradata\orabase\uwdata02.dbf' SIZE 100M
AUTOEXTEND OFF
BLOCKSIZE 8192
FORCE LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
FLASHBACK ON;

desc dba_tablespaces

set linesize 121
col tablespace_name format a20

SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_management
FROM dba_tablespaces;

desc dba_data_files

col file_name format a45

SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_data_files;

SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM DUAL;

Permanent Tablespace On A Raw Device
CREATE TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T>
BLOCKSIZE <bytes>
AUTOEXTEND OFF
EXTENT MANAGEMENT UNIFORM LOCAL SIZE <extent_size>
SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE tools LOGGING
DATAFILE '/u01/oradata/' SIZE 1024M
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Oracle Managed Permanent Auto-extendable Tablespace CREATE TABLESPACE <tablespace_name>; ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';

CREATE TABLESPACE user_data;
Oracle Managed Permanent Fixed Size Tablespace CREATE TABLESPACE <tablespace_name>
DATAFILE AUTOEXTEND OFF; ALTER SYSTEM
SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';

CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;

Set default tablespace type
ALTER DATABASE SET DEFAULT <tablespace_type> TABLESPACE; ALTER DATABASE DEFAULT BIGFILE TABLESPACE;

set linesize 121
col value$ format a20
col comment$ format a60

SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY name;

Set tablespace as the default
ALTER DATABASE DEFAULT TABLESPACE <tablespace_name>; ALTER DATABASE DEFAULT TABLESPACE uwdata;

set linesize 121
col value$ format a20
col comment$ format a60

SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY name;  
SYSAUX Tablespace
Create SYSAUX Tablespace
SELECT tablespace_name
FROM dba_tablespaces;

CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Move SYSAUX Contents
-- Move must be done using the indicated procedure

col occupant_name format a30
col schema_name format a30
col move_procedure format a50

SELECT occupant_name, schema_name, move_procedure
FROM v_$sysaux_occupants
ORDER BY 1;  
Undo Tablespace
Create An UNDO Tablespace
CREATE UNDO TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T>
AUTOEXTEND <ON | OFF>
RETENTION <GUARANTEE | NOGUARANTEE>; CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/undotbs02.dbf
SIZE 50000M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;

desc dba_undo_extents

SELECT segment_name, tablespace_name, status, SUM (bytes)
FROM dba_undo_extents
GROUP BY segment_name, tablespace_name, status;

ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;

Change The Current UNDO Tablespace
-- identify existing tablespaces
SELECT tablespace_name
FROM dba_tablespaces
ORDER BY 1;

-- format SQL*Plus display
col name format a30
col value format a30

-- check current undo related parameters
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';

-- create the new undo tablespace
CREATE UNDO TABLESPACE testundo
DATAFILE '/u03/oradata/testundo.dbf'
SIZE 100M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;

-- note that while it was created it is not being used
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';

-- switch undo tablespaces
ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;

-- verify the change has been performed
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';

-- remove the old file
DROP TABLESPACE <old_tablespace's_name> INCLUDING CONTENTS AND DATAFILES;  
Temporary Tablespaces
Create Temporary Tablespace
CREATE TEMPORARY TABLESPACE <tablespace_name>
TEMPFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T>
AUTOEXTEND <ON | OFF>
TABLESPACE GROUP <group_name>
EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent_size>;

Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace. CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/oradata/tempnew01.dbf'
SIZE 1G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in
FROM dba_tablespaces;

desc dba_temp_files

SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_temp_files;
Add Tempfile ALTER TABLESPACE <tablespace_name>
ADD TEMPFILE '<path_and_file_name>' SIZE <n>M; ALTER TABLESPACE temp_new
ADD TEMPFILE '/u02/oradata/tempnew02.dbf' SIZE 200M;
Resize Tempfile ALTER DATABASE TEMPFILE '<file_name>'
RESIZE <mega_bytes_integer>M;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' RESIZE 250M;
Drop Tempfile ALTER DATABASE TEMPFILE '<file_name>' DROP; ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' DROP; Take Temporary Tablespace Off-line ALTER DATABASE TEMPFILE '<path_and_file_name>' OFFLINE; ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' OFFLINE; Place Temporary Tablespace On-line ALTER DATABASE TEMPFILE '<path_and_file_name>' ONLINE; ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' ONLINE;
Changing the Default Temporary Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace_name>; col property_value format a30
col description format a55

SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

SELECT file_name, tablespace_name
FROM dba_temp_files;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempnew;

DROP TABLESPACE temp;  
Transportable Tablespaces Determine Transportability dbms_tts.transport_set_check(
tablespace_name IN VARCHAR2, TRUE); dbms_tts.transport_set_check('uwdata', TRUE);
View Violations; If Any SELECT * FROM TRANSPORT_SET_VIOLATIONS; Generate A Transportable Set ALTER TABLESPACE <tablespace_name> READ ONLY; ALTER TABLESPACE tools READ ONLY; Export Tablespace

Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported.
EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=(<comma_delimited_list_of_tablespaces>)
TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=<file_name> EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp

Import Tablespace
IMP TRANSPORT_TABLESPACE=<Y | N> FILE=<file_name>
DATAFILES=('<comma_delimited_list_of_data_files>)
TABLESPACES=(<comma_delimited_list_of_tablespaces>)
TTS_OWNERS=(<comma_delimited_list_of_schema_owners>)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams) IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)

Import Parameter File
TRANSPORT_TABLESPACE=y
FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1, sales_2)
TTS_OWNERS=(dcranney, jfee)
FROMUSER=(dcranney, jfee)
TOUSER=(smith, williams)  
Alter Permanent Tablespace
Add Datafile
ALTER TABLESPACE ADD DATAFILE <file_name>,
<integer><K | M | G | T>
[REUSE]
<autoextend> <ON | OFF>
NEXT <integer><K | M | G | T>
MAXSIZE <integer><K | M | G | T | UNLIMITED> ALTER TABLESPACE tools ADD DATAFILE
'/u02/oracle/oradata/uwdata02.dbf' SIZE 25M
AUTOEXTEND OFF;

ALTER TABLESPACE tools ADD ADD DATAFILE
'c:\oracle\product\oradata ools99.xxx' SIZE 10M
AUTOEXTEND ON;
Take Off-line ALTER TABLESPACE <tablespace_name> OFFLINE; ALTER TABLESPACE tools OFFLINE; Place On-line ALTER TABLESPACE <tablespace_name> ONLINE; ALTER TABLESPACE tools ONLINE; Make Read Only ALTER TABLESPACE <tablespace_name> READ ONLY; ALTER TABLESPACE tools READ ONLY;

SELECT tablespace_name, status
FROM dba_tablespaces;

-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.
Make A Tablespace Read Write ALTER TABLESPACE <tablespace_name> READ WRITE; ALTER TABLESPACE tools READ WRITE;

SELECT tablespace_name, status
FROM dba_tablespaces;
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE <tablespace_name> BEGIN BACKUP; ALTER TABLESPACE tools BEGIN BACKUP; End Tablespace Backup ALTER TABLESPACE <tablespace_name> END BACKUP; ALTER TABLESPACE tools END BACKUP;
Rename
ALTER TABLESPACE <tablespace_name> RENAME TO <new_tablespace_name>; SELECT tablespace_name
FROM dba_tablespaces;

SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USERS';

ALTER TABLESPACE users RENAME TO user_data;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USER_DATA';  
Alter Undo Tablespace
Retention Guarantee
ALTER TABLESPACE <tablespace_name> RETENTION <GUARANTEE | NOGUARANTEE>; SELECT tablespace_name, retention
FROM dba_tablespaces;

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

select tablespace_name, retention
from dba_tablespaces;

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

select tablespace_name, retention
from dba_tablespaces;  
Drop Tablespace Drop Tablespace DROP TABLESPACE <tablespace_name>; DROP TABLESPACE tools; Drop Tablespace Including Contents DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS; DROP TABLESPACE tools INCLUDING CONTENTS;
Drop Tablespace Including Contents & Datafiles DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS; DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Drop tablespace after datafile was accidentally dropped
conn / as sysdba

CREATE TABLESPACE badidea
DATAFILE 'c: emp\badidea.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT file_name
FROM dba_data_files;

shutdown immediate;

-- in an operating system window drop the file c: emp\badidea.dbf

startup

-- record the error message

shutdown immediate;

startup mount;

alter database datafile 'c: emp\badidea.dbf' offline drop;

alter database open;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT file_name
FROM dba_data_files;

drop tablespace badidea including contents;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT file_name
FROM dba_data_files;  
Mandatory Tablespaces System (must be named SYSTEM) - all versions
Always named system every Oracle instance must have one, and only one system tablespace. This tablespace contains the Oracle data dictionary tables and views. It is also where Oracle stores SQL programs such as stored procedures, functions, packages, and Java.
Undo (any name: the default is UNDOTSP1) - version 9i or above
Every Oracle instance must have one, and only one UNDO tablespace. The undo tablespace is a single large space into which Oracle stores and manager information for undo (rollback) and multi-versioning for all users and all transactions.
Temporary (any name but usually TEMP)
Every Oracle instance must have at least one temp tablespace and it can have any name. The default name is TEMP. The temp tablespace is used by Oracle to create temporary tables which it uses during processing of a request and for storing information for views and global temporary tables. Examples of transactions in which Oracle uses temp space are sorts and groupings.
One or more tablespaces for tables and indexes.
Every Oracle instance may have at least one and usually many tablespaces reserved for holding tables and indexes. These tablespaces can have any name but the default names are often like DATA01 and USERS.

It is advisable to spread I/O equally across multiple disks. And one way to accomplish this is to create separate tablespaces for tables and indexes and to store them on separate hard-drives if at all possible to improve system performance. In large systems it is usual to find each application stored in a separate tablespace and where tables of vastly different sizes are required to have tablespaces created to hold tables with small, medium, and large extents. By segregating tables by extent size into separate tablespaces it is possible to eliminate tablespace fragmentation which improves system performance and eliminates the wasting of disk space.
  Definitions
The Number Of Extents - Dictionary vs. Locally Managed Tablespaces
The number of extents has never been an issue for a segment. A table couldn't care less whether it is contained in 5 extents or 5000. But what does care is the data dictionary, since 5000 extents means 5000 rows in a data dictionary cluster that's been sized to expect no more than 5. That introduces cluster chaining in the data dictionary, and if the chaining is bad enough, then performance impacts involving dictionary access will be measurable.

Since LMTs don't touch the data dictionary for the purposes of recording extent acquisition, they do not suffer from cluster chaining, and do not experience a resulting performance degradation.

The performance issue also relates to the reading of the extent map in a single I/O instead of multiple I/Os ... since the extent map is stored inside an Oracle block, which is of finite size, too many extents mean that the map has to be stored in multiple blocks ... and any operation that needs to consult the extent map would therefore need multiple I/O operations to do so. You would really need thousands of extents, though, before that became a major issue).

The key advantage of LMTs is that they avoid a potential single point of contention on the data dictionary (but you'd have to have dozens of segments all simultaneously extending before that was an issue in the first place). And (better) they mean fragmentation is a thing of the past. And (the real killer) concern about a reasonable number of extents is wasted concern (reasonable being in the high hundreds to low thousands).

Logging
Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

The force logging clause is used to force logging and to disregard no-logging instructions issued during object creation. You cannot specify FORCE LOGGING for an undo or temporary tablespace.

This clause only affects the logging of object creation and has no effect on logging of DML statements.

Segment Space Management
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.

For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
  Related Queries
List tablespaces, their files, allocated space, free space, and next free extent
clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM (fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

List datafiles, tablespace names, and size in MB
col file_name format a50
col tablespace_name format a10

SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;

List tablespaces, size, free space, and percent free

Thanks to Michael Lehmann for this query
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
      ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
    
(
SELECT tablespace_name, SUM (bytes) FREE_SPACE,
       ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
View For Schema Owner To Monitoring Free Space CREATE OR REPLACE VIEW freespace_view AS
SELECT tablespace_name, SUM (bytes/1024/1024) AVAILABLE
FROM user_free_space
GROUP BY tablespace_name;

Another Statement For
Tablespace Management
set linesize 121

SELECT tablespace_name, ROUND( SUM (total_mb)- SUM (free_mb)) CUR_USE_MB, ROUND( SUM (total_mb)) CUR_SZ_MB,
ROUND(( SUM (total_mb)- SUM (free_mb))/ SUM (total_mb)*100) CUR_PCT_FULL, ROUND( SUM (max_mb) - ( SUM (total_mb)- SUM (free_mb))) FREE_SPACE_MB,
ROUND( SUM (max_mb)) MAX_SZ_MB, ROUND(( SUM (total_mb)- SUM (free_mb))/ SUM (max_mb)*100) PCT_FULL
FROM (
  SELECT tablespace_name, SUM (bytes)/1024/1024 FREE_MB,
  0 TOTAL_MB, 0 MAX_MB
  FROM dba_free_space
  GROUP BY tablespace_name
 
UNION
  SELECT tablespace_name, 0 CURRENT_MB,
  SUM (bytes)/1024/1024 TOTAL_MB,
  SUM ( DECODE (maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
  FROM dba_data_files
  GROUP BY tablespace_name)

GROUP BY tablespace_name;
Yet Another Statement For
Tablespace Management
col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999

SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
 
SELECT a.tablespace_name, SUM (b.bytes)/1024/1024
  DATAFILE_SZ, a.initial_extent, a.next_extent
  FROM dba_tablespaces a, dba_data_files b
  WHERE a.tablespace_name = b.tablespace_name
  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent
) A,
    (
  SELECT a.tablespace_name, SUM (c.bytes)/1024/1024
  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
  FROM dba_tablespaces a, dba_segments c
  WHERE a.tablespace_name = c.tablespace_name
  GROUP BY a.tablespace_name
) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;

And yet another statement for Tablespace Management
SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
 
SELECT a.tablespace_name,
  SUM (a.bytes)/1024/1024 TOTAL_FREE,
  COUNT(a.bytes) TOTAL_CHUNKS,
  MAX(a.bytes)/1024/1024 LARGEST_CHUNK
  FROM dba_free_space a
  GROUP BY a.tablespace_name
) dfs,
    (
 
SELECT b.tablespace_name,
  SUM (b.bytes)/1024/1024 TOTAL_SIZE
  FROM dba_data_files b
  GROUP BY b.tablespace_name
) ddf
WHERE
dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;

Calculation Of Minimum Tablespace Size (this takes a long time to run)
SELECT SUBSTR(f.file_name,1,70) FILENAME,
MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
GROUP BY f.file_name;

Schemas In The SYSAUX Tablespace
col occupant_name format a25
col schema_name format a20
col move_procedure format a30
col move_procedure_desc format a40
set linesize 131

SELECT occupant_name, schema_name, move_procedure, move_procedure_desc
FROM v_$sysaux_occupants;

Contiguous Space
create table t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;

CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM (blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM (bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;

DECLARE
 CURSOR query IS
 SELECT *
 FROM dba_free_space
 ORDER BY tablespace_name, file_id, block_id;

 this_row     query%ROWTYPE;
 previous_row query%ROWTYPE;
 old_file_id  PLS_INTEGER;
 old_block_id PLS_INTEGER;
BEGIN
  OPEN query;
  FETCH query INTO this_row;
  previous_row := this_row;
  old_file_id := previous_row.file_id;
  old_block_id := previous_row.block_id;

  WHILE query%FOUND LOOP
    IF this_row.file_id = previous_row.file_id AND
      this_row.block_id = previous_row.block_id+previous_row.blocks
    THEN
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (previous_row.tablespace_name, previous_row.file_id,
       this_row.block_id, old_file_id, old_block_id, this_row.blocks,
       this_row.bytes);
    ELSE
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (this_row.tablespace_name, this_row.file_id,
       this_row.block_id, this_row.file_id, this_row.block_id,
       this_row.blocks, this_row.bytes);

      old_file_id := this_row.file_id;
      old_block_id := this_row.block_id;
    END IF ;
    previous_row := this_row;
    FETCH query INTO this_row;
  END LOOP;
  COMMIT;
END;
/

col tablespace_name format a20
col sum_mb format 999.999

SELECT * FROM v_contig_space;

No comments:

Post a Comment