TOP-N Schema List

SELECT owner, wsize  "Size in MB"
  FROM ( SELECT owner, sum(bytes/(1024*1024)) wsize, RANK() OVER (ORDER BY sum(bytes/(1024*1024)) DESC) sal_rank
           FROM dba_segments group by owner)
 WHERE sal_rank <= 5;

 SELECT owner, wsize  "Size in MB"
  FROM ( SELECT owner, sum(bytes/(1024*1024)) wsize, DENSE_RANK() OVER (ORDER BY sum(bytes/(1024*1024)) DESC) sal_rank
           FROM dba_segments group by owner)
 WHERE sal_rank <= 5;

Top-N-SQL using RANK() and DENSE_RANK()DENSE_RANK()

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

- using rank()

SELECT segment_name, bytes

  FROM ( SELECT segment_name, bytes, RANK() OVER (ORDER BY bytes DESC) sal_rank

           FROM user_segments )

 WHERE sal_rank <= 5;

 -- using dense_rank()

 SELECT segment_name, bytes

  FROM ( SELECT segment_name, bytes, DENSE_RANK() OVER (ORDER BY bytes DESC) sal_rank

           FROM user_segments )

 WHERE sal_rank <= 5;

Max datafile size in oracle

Max datafile size for SMALL FILE NORMAL TABLESPACE would be:

Database Block Size Maximum Datafile File Size
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB

Tablespace details ( includes datafiles, type, autoextend, maxsize etc)

Summary

1. list datafiles

2. get type, auroextend, management type etc

Tablespace report

Run the following sql to get the space report in Oracle

Missing Tempfiles

Missing Tempfiles

Tempfiles are usually not backed up.

Since Oracle does not record checkpoint information in tempfiles, Oracle can start up a database with a missing tempfile.

Likewise, it is possible to remove (or in this case, not recreate) all tempfiles from a temporary tablespace and keep it empty.

But when a user attempts to sort to the TEMPORARY tablespace, an error is generated.

The solution is to add a new tempfile

--------

tablespace

Space Management in Oracle

-- create system managed tablespace using OFA

     CREATE SMALLFILE TABLESPACE USERS

     LOGGING

     DATAFILE SIZE 2M

     AUTOEXTEND ON

     MAXSIZE UNLIMITED

     EXTENT MANAGEMENT LOCAL

     SEGMENT SPACE MANAGEMENT AUTO;

# to add datafile

alter tablespace USERS add datafile size 10M autoextend on;