Which segments are above the HWM in a tablespace ?

https://jonathanlewis.wordpress.com/tablespace-hwm/

Oracle tablespace from uniform to autoallocate

There is no "alter tablespace" syntax for changing it

You must re-define the tablespace to change the extent management:

• Backup the tablespace
• Export the tablespace data
• Drop and re-allocate the tablespace
• Import the tablespace

How to move lobsegment and lobindex to a different Tablespace

You can move a LOBSEGMENT with the

ALTER TABLE owner.table_name MOVE LOB (column_name) STORE AS (tablespace_name)

command.
You cannot specify a tablespace for the LOBINDEX -- it is automatically created and moved with the LOBSEGMENT.

The mapping between a Table's LOB column and it's LOBSEGMENT (or vice versa , if you start with a LOBSEGMENT and want to know which Table it belongs to) is ALL/DBA/USER_LOBS where
table_name and column_name are available with segment_name. You can even identify the LOBINDEX from index_name in the same view.

Note : "small" LOBs stored inline (ie in the row itself) are not in a seperate LOBSEGMENT at all. That is called STORAGE IN ROW and is the default for LOBs of 4000bytes or less.

Script to get the amount of free space in your tablespaces

select  tbs.tablespace_name, 
        tot.bytes/1024 total, 
        tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used, 
        sum(nvl(fre.bytes,0))/1024 free, 
        (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
        decode(
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1  ;

Oracle tablespace usage script

SELECT /* + RULE */  
df.tablespace_name AS "Tablespace",
df.bytes / (1024 * 1024 * 1024) AS "Size (GB)",
Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"       
FROM 
(
SELECT 
tablespace_name,
Sum(bytes) AS bytes 
FROM 
dba_free_space
GROUP BY
tablespace_name
) fs, 
( 
SELECT 
tablespace_name,
SUM(bytes) AS bytes
FROM 
dba_data_files
GROUP BY 
tablespace_name

) df
WHERE 
fs.tablespace_name = df.tablespace_name
ORDER BY 3 desc

Why you should use Oracle datapump

• Similar look and feel to the old exp/imp
• Can filter on the full range of object types
• Can re-map datafiles and or tablespaces on import
• Parallelizable
• Significantly faster than the traditional exp/imp
• PL/SQL interface - programmable
• Can import through a network link
• Tracking in V$session_longops
• Interruptible and restartable

Why you should use Automatic segment space management (ASSM)

Automatic segment space management (ASSM) 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 freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.  ASSM is not for every database, especially those with super-high DML rates:

Varying row sizes: ASSM is better than a static pctused. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length.

Reducing buffer busy waits: ASSM will remove buffer busy waits better than using multiple freelists. When a table has multiple freelists, all purges must be parallelized to reload the freelists evenly, and ASSM has no such limitation.

Great for Real Application Clusters: The bitmap freelists remove the need to define multiple freelists groups for RAC and provide overall improved freelist management over traditional freelists.

Shell script to calculate Tablespaces sizes in Oracle

This shell take an ORACLE_SID as parameter and calculate in MB the sizes of tablespaces

Steps to move Oracle datafiles to another file system

• Take the tablespace offline

If you want to know in wich tablespace a datafile belong

select TABLESPACE_NAME from dba_data_files where FILE_NAME = 'file_name';

To take a tablespace offline

alter tablespace tablespace_name offline

• Physicaly move the datafiles at OS level

mv 'old_file_name' 'new_file_name' (on Linux / Unix)

• Rename the datafiles at database level

alter tablespace tablespace_name rename datafile 'old_datafile_name' to 'new_data_file_name';

• Bring the tablespace back online

alter tablespace tablespace_name online;

Script to resize Oracle Datafiles

Over-allocation of space at the file level affects the backup/recovery window, file checking times and, most  painfully, limits the potential allocation of space to a tablespace that needs the extra room. A simpler solution would be to review the evolution of the script, which lets you know which files can and cannot be resized to create more space.

It's possible to release space from data files but only down to the first block of data. This is done with the 'alter database' command.

The following script allows to calculate the amount of space used by each tablespace

SELECT   tablespace_name, SUM (bytes) bytes_full
    FROM   dba_extents
GROUP BY   tablespace_name;

The following scripts allows to calculate the total space available for each tablespace

SELECT   tablespace_name, SUM (bytes) bytes_total
    FROM   dba_data_files
GROUP BY   tablespace_name;

The following script allows to find the last data block that has been inserted for each file

SELECT   tablespace_name, file_id, MAX (block_id) max_data_block_id
    FROM   dba_extents
GROUP BY   tablespace_name, file_id;

The following allows to find the free space in each file above the last data block inserted

SELECT   a.tablespace_name, a.file_id, b.bytes bytes_free
  FROM   (  SELECT   tablespace_name, file_id, MAX (block_id) max_data_block_id
              FROM   dba_extents
          GROUP BY   tablespace_name, file_id) a, dba_free_space b
 WHERE       a.tablespace_name = b.tablespace_name
         AND a.file_id = b.file_id
         AND b.block_id > a.max_data_block_id;

Finally the following will allow to generate alter statements to resize your datafiles

SELECT      'alter database '
         || a.name
         || ' datafile '''
         || b.file_name
         || ''''
         || ' resize '
         || GREATEST (TRUNC (bytes_full / .7), (bytes_total - bytes_free))
  FROM   v$database a,
         dba_data_files b,
         (  SELECT   tablespace_name, SUM (bytes) bytes_full
              FROM   dba_extents
          GROUP BY   tablespace_name) c,
         (  SELECT   tablespace_name, SUM (bytes) bytes_total
              FROM   dba_data_files
          GROUP BY   tablespace_name) d,
         (SELECT   a.tablespace_name, a.file_id, b.bytes bytes_free
            FROM   (  SELECT   tablespace_name,
                               file_id,
                               MAX (block_id) max_data_block_id
                        FROM   dba_extents
                    GROUP BY   tablespace_name, file_id) a,
                   dba_free_space b
           WHERE       a.tablespace_name = b.tablespace_name
                   AND a.file_id = b.file_id
                   AND b.block_id > a.max_data_block_id) e
 WHERE       b.tablespace_name = c.tablespace_name
         AND b.tablespace_name = d.tablespace_name
         AND bytes_full / bytes_total < .7
         AND b.tablespace_name = e.tablespace_name
         AND b.file_id = e.file_id;