Wednesday, August 31, 2016

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



Wednesday, August 24, 2016

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.

Friday, August 19, 2016

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

Tuesday, August 9, 2016

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.

Monday, August 8, 2016

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;

Tuesday, August 2, 2016

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;

Thursday, July 14, 2016

INTERVAL conversion functions

There are 4 convertions functions that deal with INTERVAL datatypes, as listed below.
The two first converts strings, the later two converts numbers. All functions returns data of datatype INTERVAL.

TO_DSINTERVAL(sql_format)

* Converts a string into a value of the INTERVAL DAY TO SECOND datatype
* sql_format is a character string in the required format, which is 'DAYS HH24:MI:SS.FF'
* For example, the string '2 8:30:15.444' would equal 2 days, 8 hours, 30 minutes, 15.444 seconds.

Output: A value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT TO_DSINTERVAL(INTERVAL '4' HOUR) "HOUR"
FROM DUAL;

HOUR
+00 04:00:00.000000



TO_YMINTERVAL('y-m')
* Converts y and m into the years and months in a format recognized by the INTERVAL YEAR TO MONTH datatype
* y and m are numbers within a string

Output: A value of the INTERVAL YEAR TO MONTH datatype

Example:
SELECT TO_YMINTERVAL(INTERVAL '36' MONTH) "MONTH"
FROM DUAL;

MONTH
+03-00


NUMTOYMINTERVAL(n, u)

* Converts a number into a value that represents the u unit amount of time
n = number
u = 'YEAR' or 'MONTH'
* Output: a value of the INTERVAL YEAR TO MONTH datatype

Example:

SELECT NUMTOYMINTERVAL(4, 'MONTH') "MONTH"
FROM DUAL;

MONTH
+00-04


NUMTODSINTERVAL(n, u)
Converts a number into a value that represents the u unit amount of time
n = number
u = 'DAY', 'HOUR', 'MINUTE', 'SECOND'
Output: a value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT NUMTODSINTERVAL(2,'MINUTE') "MINUTE"
FROM DUAL;

MINUTE
+00 00:02:00.000000