How to list partitions and their sizes

Using an inline view with a correlated subquery:

set lines 200
col partition_name format a40
SELECT P.PARTITION_NAME, (  SELECT ROUND(SUM(BYTES)/1024/1024/1024)
                            FROM DBA_SEGMENTS S
                            WHERE S.PARTITION_NAME = P.PARTITION_NAME
                            AND SEGMENT_NAME='&&TABLE_NAME') "size GB"
FROM DBA_TAB_PARTITIONS P
WHERE P.TABLE_NAME = '&&TABLE_NAME'
ORDER BY P.PARTITION_POSITION ASC;

Example output:

sqlplus / as sysdba @get_size.sql
Enter value for table_name: ARCHIVED_DOCUMENTS


PARTITION_NAME                      size GB
------------------------------   ----------
DOKARCHIVE1                           2.875
DOKARCHIVE2                               3
DOKARCHIVE3                               3
DOKARCHIVE4                               3
DOKARCHIVE5                               3
DOKARCHIVE6                          2.8125
DOKARCHIVE7                            2.75

Or, limit the output to specific partitions:

select s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB"
from dba_segments S join dba_tab_partitions p
on (s.partition_name = p.partition_name)
where s.segment_name='SALES_ARCHIVE'
and   s.owner='SH'
and s.partition_name in ( 'P_2015_01','P_2016_01','P_2017_01')
group by s.partition_name,s.tablespace_name,p.compression,p.num_rows
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	COMPRESSION	NUM_ROWS	GB
P_2015_01	SALES_2015	ENABLED	22077874	2,3
P_2016_01	SALES_2016	ENABLED	394694450	42,3
P_2017_01	SALES_2017	ENABLED	481708328	48,9

If your partitions involve lob columns, make sure you fetch the sizes of the lob partitions, too.

How to list table sizes and their LOB segment sizes

Sometimes a quick sum of the used bytes in DBA_SEGMENTS is not enough to find the total size of a table.
If you have Large Object type columns defined in the table, the total table size can potentially be far greater than what you'd think.

Let's look at an example.

I have two tables in two differen schemas, with the same name. The tables are identical, and they both contain a LOB column:

SQL> desc livedocs.documents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(19)
 VERSION                                            NUMBER(19)
 DOC_ID                                             NOT NULL NUMBER(19)
 TYPE                                               NOT NULL VARCHAR2(255 CHAR)
 NUM_PAGES                                          NUMBER(15)
 CREATED                                            TIMESTAMP(6)
 LAST_CHANGED                                       TIMESTAMP(6)
 CONTENTS                                           NOT NULL BLOB

Check the tables' sizes:

SELECT OWNER,SEGMENT_NAME,SUM(BYTES)/1024/1024/1024 "table size (GB)" 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('DOCUMENTS') 
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
GROUP BY OWNER,SEGMENT_NAME;

Result:

OWNER	SEGMENT_NAME	table size (GB)
LIVEDOCS	DOCUMENTS	2,5
ARCHIVEDDOCS	DOCUMENTS	1,875

Let's find the name of the LOB segments and their corresponding indexes:
(The LOB indexes are internal data structures only. See this post.)

SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME 
FROM DBA_LOBS 
WHERE TABLE_NAME='DOCUMENTS' 
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS');

OWNER	TABLE_NAME	SEGMENT_NAME	COLUMN_NAME	INDEX_NAME
ARCHIVEDDOCS	DOCUMENTS	ARCHIVED_DOCS_LOB	CONTENT	SYS_IL0000123181C00008$$
LIVEDOCS	DOCUMENTS	LIVE_DOCS_LOB	CONTENT	SYS_IL0000194213C00009$$

How much space have been allocated to these LOB segments?

SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) "LOB size (GB)" 
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN 
    (
    SELECT SEGMENT_NAME 
    FROM DBA_LOBS WHERE TABLE_NAME='DOCUMENTS'
    AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
    )
GROUP BY OWNER,SEGMENT_NAME 
; 

OWNER	SEGMENT_NAME	LOB size (GB)
LIVEDOCS	LIVE_DOCS_LOB	1256
ARCHIVEDDOCS	ARCHIVED_DOCS_LOB	3642

Putting it together, you can list both the table and its dependent LOB segments like this:

SELECT S.OWNER "Owner",NVL(S.SEGMENT_NAME, 'TABLE TOTAL SIZE') "Segment name",ROUND(SUM(S.BYTES)/1024/1024/1024,1) "Segment size (GB)"
FROM  DBA_SEGMENTS S 
WHERE S.SEGMENT_NAME IN ('DOCUMENTS')
AND   S.OWNER IN ('LIVEDOCS','ARCHIVEDOCS')
OR    S.SEGMENT_NAME IN (
                    (
                        SELECT L.SEGMENT_NAME FROM DBA_LOBS L 
                        WHERE L.TABLE_NAME = 'DOCUMENTS' 
                        AND L.OWNER = S.OWNER 
                        AND L.OWNER in ('LIVEDOCS','ARCHIVEDOCS')
                    )
)
GROUP BY S.OWNER,ROLLUP(S.SEGMENT_NAME)
ORDER BY 1,2,3; 

The resulting listing is:

Owner	Segment name	Segment size (GB)
ARCHIVEDDOCS	ARCHIVED_DOCS_LOB	3642,4
ARCHIVEDDOCS	DOCUMENTS	1,9
ARCHIVEDDOCS	TABLE TOTAL SIZE	3644,3
LIVEDOCS	DOCUMENTS	2,5
LIVEDOCS	LIVE_DOCS_LOB	1255,9
LIVEDOCS	TABLE TOTAL SIZE	1258,4

How to add minutes, seconds, hours or days to a datetime value

Simply add the desired interval to your timestamp value:

select current_timestamp,
       current_timestamp + interval '3' minute "current_timestamp+3min" 
from dual;


CURRENT_TIMESTAMP                                 current_timestamp+3min
----------------------------------------          ---------------------------------------------------------------------------
24.08.2016 19.44.32,808781 +2:00                  24.08.2016 19.47.32,808781000 +2:00

There are numerous possibilities to add very granular time intervals to an existing datetime value.

Check the examples of Interval literals in the official documentation.

Example on how to use WITH CHECK OPTION in an INSERT statement

The table "ALBUM" has the following columns:

Name         Null     Type                        
------------ -------- --------------------------- 
ALBUM_ID     NOT NULL NUMBER                      
ALBUM_TITLE  NOT NULL VARCHAR2(50 CHAR)           
ARTIST_ID             NUMBER                      
RELEASE_DATE          TIMESTAMP(6) WITH TIME ZONE 
GENRE                 VARCHAR2(30)                
NUM_SOLD              NUMBER                      
COLLECTION            CHAR(1)        

Extract all albums by the band Kiss:

SELECT ARTIST_ID, ALBUM_TITLE
 FROM ALBUM
 WHERE ARTIST_ID = (SELECT ARTIST_ID FROM ARTIST WHERE ARTIST_NAME='Kiss');

 ARTIST_ID ALBUM_TITLE                                      
---------- ------------
        13 Dynasty            

Add another entry to the "ALBUM" table:

SQL> INSERT INTO
  2  (
  3   SELECT album_id, album_title, artist_id,release_date, genre, num_sold
  4   FROM album
  5   WHERE num_sold BETWEEN 10000 AND 50000
  6   AND artist_id=13 WITH CHECK OPTION
  7   )
  8   VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 );
Enter value for album_title: Love Gun
Enter value for artist_id: 13
Enter value for release_date: 30.06.1977
old   8:  VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 )
new   8:  VALUES( album_seq.nextval, 'Love Gun', 13, to_date('30.06.1977','dd.mm.yyyy'), 'HARD ROCK', 20000 )

1 row inserted.

COMMIT;

Run the SELECT above once more to confirm the row has been entred:

ARTIST_ID ALBUM_TITLE                                      
---------- -----------------------
        13 Love Gun                             
        13 Dynasty            

Any attempt to enter values that is not included in the subquery would cause an

ORA-01402: view WITH CHECK OPTION where-clause violation

to be raised.
For example, try entering 15 as the artist_id:

Enter value for album_title: Creatures of the Night
Enter value for artist_id: 15
Enter value for release_date: 13.10.1982
old   8:  VALUES( album_seq.nextval, '&album_title', &artist_id, to_date('&release_date','dd.mm.yyyy'), 'HARD ROCK', 20000 )
new   8:  VALUES( album_seq.nextval, 'Creatures of the Night', 15, to_date('13.10.1982','dd.mm.yyyy'), 'HARD ROCK', 20000 )
 VALUES( album_seq.nextval, 'Creatures of the Night', 15, to_date('13.10.1982','dd.mm.yyyy'), 'HARD ROCK', 20000 )
         *
ERROR at line 8:
ORA-01402: view WITH CHECK OPTION where-clause violation

Note that the subquery, when used in an INSERT statement as above, replaces the table name, as well as the column list.

Source: Oracle documentation

How to check for active usage of an UNDO tablespace

SELECT a.name,n.status
FROM   v$rollname a inner join v$rollstat n
USING(usn)
WHERE a.name IN (
                  SELECT segment_name
                  FROM dba_segments
                  WHERE tablespace_name = 'UNDOTBS1'
)
;

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE

Status can be either ONLINE, OFFLINE, PENDING OFFLINE or FULL.

* ONLINE means that the undo segments in the UNDO tablespace is being actively used
* OFFLINE means that the undo segments are not accessible for usage
* FULL means that the tablespace in which the undo segments reside has reached its limit, and you will most likely have seen the error

ORA-30036: unable to extend segment by  in undo tablespace 'UNDOTBS1'

at this point

* PENDING OFFLINE means there are still uncommitted transactions depending on the information in the undo segments in this particular UNDO tablespace.

How to find the default trace file for the session

Use the v$diag_info view, which "describes the state of Automatic Diagnostic Repository (ADR) functionality"

SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

Example output:

VALUE
/u01/oracle/diag/rdbms/proddb01/proddb01/trace/proddb01_ora_45082.trc

A real easy way to start a trace of your current session is

exec dbms_monitor.session_trace_enable;

Then execute your statements

When you're done, stop tracing with

exec dbms_monitor.session_trace_disable;

How to rename or move a datafile in oracle.

The basic steps in relocating a datafile is as follows


1. Take the tablespace offline:

alter tablespace data offline normal;

2. Move the datafile physically, using an os command like cp:

cp data/oracle/proddb01/tmp/datafiles/data01.dbf /data/oracle/proddb01/datafile/data01.dbf

3. Inform the controlfile about your change:

alter database rename file '/data/oracle/proddb01/tmp/datafiles/data01.dbf' to '/data/oracle/proddb01/datafile/data01.dbf';

4. Make the tablespace available for usage again:

alter tablespace data online;