Which segments are above the HWM in a tablespace ?

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

Disavantages for enabling row movement on oracle tables

It will necessarily consume processing resources on your machine while running (it will 
read the table, it will delete/insert the rows at the bottom of the table to move them 
up, it will generate redo, it will generate undo).

If you had densely packed blocks with lots of "pretty much empty blocks", and the densely 
packed blocks were organized by index key value -- spraying them about the top of the table could 
hurt the clustering factor.
 
Do you have any application that expects rowid to be constant for a row?  If so - then it is wrong
 
 
  
 
 

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.

Oracle Flashback monitoring scripts

PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
 from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)
 from  v$RECOVERY_FILE_DEST;

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

Oracle Script to get all objects in a datafile

SELECT   DISTINCT segment_name, segment_type,bytes/1024/1024 as "Size (MB)"
  FROM   dba_extents
 WHERE   file_id = (SELECT   file_id
                      FROM   dba_data_files
                     WHERE   FILE_name = '&datafile')
                     order by segment_type

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.

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;