Tuesday, November 26, 2019

How to list the databases in a PostgreSQL instance


Connect to the default database:
psql

List your databases:
select oid as database_id,
       datname as database_name,
       datallowconn as allow_connect,
       datconnlimit as connection_limit
from pg_database
order by oid;

Example output:
database_id | database_name | allow_connect | connection_limit
-------------+---------------+---------------+------------------
           1 | template1     | t             |               -1
       13880 | template0     | f             |               -1
       13881 | postgres      | t             |               -1
       16426 | proddb01      | t             |               -1
(4 rows)

Or use the psql meta-command "list+" (or "\l+") to list the available databases;
prod-# \l+
                                                                      List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges       |  Size   | Tablespace |                Description
-----------+----------+----------+------------+------------+------------------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres                +| 9269 kB | pg_default | default administrative connection database
           |          |          |            |            | postgres=CTc/postgres       +|         |            |
           |          |          |            |            | postgres_exporter=c/postgres |         |            |
 proddb01  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres       +| 25 GB   | pg_default |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +| 7965 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres        |         |            |
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +| 8221 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres        |         |            |
(4 rows)
Once again, thanks to Bart Gawrych for providing some very useful listings on his site.

Thursday, November 14, 2019

How to move XML LOB segments in a partitioned table to a new tablespace



The examples used in this article is based on Oracle 18c.

When moving a partition with XMLTYPE columns to a new tablespace, the LOB objects that was created by the XMLTYPE objects will follow along with the partition when you move it. Only the LOB partitions based on data types CLOB (and I assume also BLOB) will have to be moved explicitly.

The table looks as follows. Note the column types of CLOB and XMLTYPE:
CREATE TABLE CS_DOCUMENTS
(
  ENTRY_ID                 VARCHAR2(100 CHAR),
  BATCH_ID                 NUMBER(28),
  DOC_ID                   VARCHAR2(100 CHAR),
  DOC_TYPE                 VARCHAR2(100 CHAR),
  DOC_NAME                 VARCHAR2(4000 CHAR),
  STATUS                   VARCHAR2(4000 CHAR),
  PUBLISHED                TIMESTAMP(6),
  CREATED                  TIMESTAMP(6),
  RAW_DATA                 CLOB,
  DOKCOUNT                 INTEGER,
  REVISION                 INTEGER,
  XML_P_HEADER             SYS.XMLTYPE,
  XML_P_HEADER_FORMAT      VARCHAR2(4000 CHAR),
  XML_P_DATA               SYS.XMLTYPE,
  XML_P_DATA_FORMAT        VARCHAR2(4000 CHAR),
  XML_P_EXTENSION          SYS.XMLTYPE,
  XML_P_EXTENSION_FORMAT   VARCHAR2(4000 CHAR)
)
-- CLOB
LOB (RAW_DATA) STORE AS SECUREFILE (
  TABLESPACE  DATA1
)
-- XMLTYPE
XMLTYPE XML_P_HEADER STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
  )
-- XMLTYPE
XMLTYPE XML_P_DATA STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
  )
-- XMLTYPE
XMLTYPE XML_P_EXTENSION STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
)
TABLESPACE MOTTAK_DATA
PARTITION BY RANGE (BATCH_ID)
INTERVAL(1000)
(  
  PARTITION P_INIT VALUES LESS THAN (1001)
    NOCOMPRESS 
    TABLESPACE DATA1
    LOB (RAW_DATA) STORE AS SECUREFILE (
      TABLESPACE  DATA1
    )
 )
;
The tablespace DATA1 is filling up, and there is a need to move some partitions to another tablespace, DATA2.
In this example, I am moving the latest added partitions first, and working my way backwards.

Let's look at most recently added partition:
SELECT /*+ result_cache */ LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME,ROUND(SUM(S.BYTES)/1024/1024/1024,2) "GB"
 FROM DBA_LOB_PARTITIONS LP join DBA_TAB_PARTITIONS TP
 ON (LP.PARTITION_NAME = TP.PARTITION_NAME) JOIN DBA_SEGMENTS S
    ON (LP.LOB_PARTITION_NAME = S.PARTITION_NAME)
 WHERE  TP.TABLE_NAME='CS_DOCUMENTS'
 AND     TP.PARTITION_NAME = (
             SELECT PARTITION_NAME
            FROM DBA_TAB_PARTITIONS 
            WHERE TABLE_NAME='CS_DOCUMENTS' 
            AND partition_position=(SELECT MAX(PARTITION_POSITION) FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENTS' AND TABLESPACE_NAME != 'DATA2'
            )
            )
 GROUP BY LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.LOB_INDPART_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME
 ORDER BY PARTITION_NAME DESC;

Output:
TABLE_NAME COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME GB
CS_DOCUMENTS RAW_DATA SYS_LOB0044210973C00012$$ SYS_P6533446
SYS_LOB_P6533447
62
DATA1
31,24
CS_DOCUMENTS SYS_NC00017$ SYS_LOB0044210973C00017$$ SYS_P6533446 SYS_LOB_P6533449
62
DATA1
0,01
CS_DOCUMENTS SYS_NC00020$ SYS_LOB0044210973C00020$$ SYS_P6533446 SYS_LOB_P6533451
62
DATA1
0,01
CS_DOCUMENTS SYS_NC00023$ SYS_LOB0044210973C00023$$ SYS_P6533446 SYS_LOB_P6533453
62
DATA1
0,01

The LOB partition SYS_LOB_P6533447 is occupying 31GB of space.

Move the partition to another tablespace. Notice how I specify tablespace for the table partition and the LOB object, but none of the XMLType objects:

Take the opportunity to compress the LOB objects at the same time. Use the ONLINE clause to allow DML against the table during the move-operation:
set timing on
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>'move_objects');
exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'move_objects', action_name=>NULL);
exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'move_lobs');
ALTER TABLE CS_DOCUMENTS
 MOVE PARTITION SYS_P6533446
    ROW STORE COMPRESS ADVANCED
    TABLESPACE DATA2
    LOB (ENTRY_RAW) STORE AS SECUREFILE (
      TABLESPACE  DATA2
      COMPRESS    MEDIUM
      )
 ONLINE;

The situation after the move:
SELECT /*+ result_cache */ LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME,SUM(S.BYTES)/1024/1024/1024 "GB"
 FROM DBA_LOB_PARTITIONS LP join DBA_TAB_PARTITIONS TP
 ON (LP.PARTITION_NAME = TP.PARTITION_NAME) JOIN DBA_SEGMENTS S
    ON (LP.LOB_PARTITION_NAME = S.PARTITION_NAME)
 WHERE  TP.TABLE_NAME='CS_DOCUMENTS'
 AND     TP.PARTITION_NAME = (
             SELECT PARTITION_NAME
            FROM DBA_TAB_PARTITIONS 
            WHERE TABLE_NAME='CS_DOCUMENTS' 
            AND partition_position=(SELECT MIN(PARTITION_POSITION) FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENTS' AND TABLESPACE_NAME = 'DATA2'
            )
            )
 GROUP BY LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.LOB_INDPART_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME
 ORDER BY PARTITION_NAME DESC;

Output:
TABLE_NAME COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME GB
CS_DOCUMENTS RAW_DATA SYS_LOB0044210973C00012$$ SYS_P6533446 SYS_LOB_P6533447
62
DATA2
0,0078125
CS_DOCUMENTS SYS_NC00017$ SYS_LOB0044210973C00017$$ SYS_P6533446 SYS_LOB_P6533449
62
DATA2
0,0078125
CS_DOCUMENTS SYS_NC00020$ SYS_LOB0044210973C00020$$ SYS_P6533446 SYS_LOB_P6533451
62
DATA2
0,0078125
CS_DOCUMENTS SYS_NC00023$ SYS_LOB0044210973C00023$$ SYS_P6533446 SYS_LOB_P6533453
62
DATA2
0,0078125

ALTER TABLE .... MOVE PARTITION statments can be generated on a partition-by-partition basis with:
SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || PARTITION_NAME || ' ROW STORE COMPRESS ADVANCED TABLESPACE DATA2 lob (' || COLUMN_NAME || ') store as SECUREFILE (tablespace DATA2 COMPRESS MEDIUM) online update indexes;' 
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'CS'
and table_name='CS_DOCUMENT'
AND PARTITION_POSITION = (SELECT MAX(PARTITION_POSITION) FROM DBA_LOB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENT'  AND TABLESPACE_NAME != 'DATA2')
AND COLUMN_NAME = (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='CS_DOCUMENTS' AND DATA_TYPE LIKE ('%LOB%'));

Tuesday, November 12, 2019

How to convert a non-partitioned table into a partitioned table in Oracle version 12.2 and onwards




With Oracle 12.2 and higher versions, it is really simple to convert a non-partitioned table to a partitioned table: you can now use the "ALTER TABLE .... MODIFY" syntax.



Below I am showing how I used this feature for one of my tables.

First, find some basic info about the table as it is right now:

SELECT TABLE_NAME, PARTITIONED,NUM_ROWS,COMPRESSION
FROM  DBA_TABLES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

Output:
TABLE_NAME PARTITIONED NUM_ROWS COMPRESSION
SEGMENT_SIZE_SAMPLES NO
948401
DISABLED


Alter the table. I am taking the opportunity to compress the table at the same time as I am getting it partitioned. I am also using the ONLINE keyword, allowing DML against the table during the operation:
ALTER TABLE SEGMENT_SIZE_SAMPLES MODIFY 
PARTITION BY RANGE (SAMPLE_DATE)
INTERVAL
(
   NUMTOYMINTERVAL(1,'MONTH')
)
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2014-01','YYYY-MM') )
)
ROW STORE COMPRESS ADVANCED
ONLINE;


Analyze the table:
exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'DBDRIFT',TabName => 'SEGMENT_SIZE_SAMPLES');

Check the table properties again:
SELECT TABLE_NAME, PARTITIONED,NUM_ROWS,COMPRESSION
FROM  DBA_TABLES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

Output:
TABLE_NAME PARTITIONED NUM_ROWS COMPRESSION
SEGMENT_SIZE_SAMPLES YES
1000719
 

How about compression?

Check the DBA_TAB_PARTITIONS (I am only showing the first 5 rows for brevity):
SELECT TABLE_NAME, PARTITION_NAME,NUM_ROWS,COMPRESSION, COMPRESS_FOR
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES'
FETCH FIRST 5 ROWS ONLY;

Output:
TABLE_NAME PARTITION_NAME NUM_ROWS COMPRESSION COMPRESS_FOR
SEGMENT_SIZE_SAMPLES P_INIT
0
ENABLED ADVANCED
SEGMENT_SIZE_SAMPLES SYS_P17074
19320
ENABLED ADVANCED
SEGMENT_SIZE_SAMPLES SYS_P17075
24955
ENABLED ADVANCED
SEGMENT_SIZE_SAMPLES SYS_P17076
24150
ENABLED ADVANCED
SEGMENT_SIZE_SAMPLES SYS_P17077
24934
ENABLED ADVANCED


How about existing indexes?

By default, existing non-unique indexes are also partitioned during the alteration of the table.

There are two indexes on my table, one unique index supporting the primary key constraint, and one non-unique index.

Before the table was altered, both were unpartitioned:
SELECT INDEX_NAME,UNIQUENESS, PARTITIONED 
FROM DBA_INDEXES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

INDEX_NAME UNIQUENESS PARTITIONED
SEGMENT_SIZE_SAMPLES_IDX1 UNIQUE NO
SEGMENT_SIZE_SAMPLES_IDX2 NONUNIQUE NO

After the table was partitioned, the non-unique index was automatically partitioned, too:
INDEX_NAME UNIQUENESS PARTITIONED
SEGMENT_SIZE_SAMPLES_IDX1 UNIQUE NO
SEGMENT_SIZE_SAMPLES_IDX2 NONUNIQUE YES

Update 18.03.2020:

Another example using LIST partitioning:
alter table MUSIC.ALBUM modify
partition by list (GENRE)
(
    partition P_ROCK values (( 'ROCK')),
    partition P_POP values (( 'POP')),
    partition P_CLASSICAL values (( 'CLASSICAL')),
    partition P_MISC values (default)
)
online;

Update 12.03.2021:
An example using subpartitions:
-- First, give the LOB the desired attributes, if you wish to change any of them:
alter TABLE STOCKS MOVE
LOB (DOC)
STORE AS SECUREFILE(
  TABLESPACE  DATA1
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  COMPRESS    MEDIUM
);

-- Alter the table
alter TABLE STOCKS MODIFY
PARTITION BY LIST (stockname)
SUBPARTITION BY RANGE (LASTUPDATED)
(   -- First partition is called a_name
    PARTITION a_name VALUES('a-abc')
    (
    -- older values
    SUBPARTITION SP_a_name_1_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA1 ),
    -- 2016
    subpartition SP_a_name_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),

...continue with one subpartition per month per year...

...contine with  partitions b_name, c_name etc

-- Final partition is called z_name
 PARTITION z_name VALUES(default)
  (
    -- 2016
    SUBPARTITION SP_sp_z_name_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1,
    subpartition SP_sp_z_name_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),

...continue with one subpartition per month per year...

 LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2022 ),
    subpartition SP_z_name_202212 VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2022
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2022 ),
    SUBPARTITION SP_z_name_DEFAULT VALUES LESS THAN (MAXVALUE) TABLESPACE DATA1
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA1)
    )
)
ONLINE
UPDATE INDEXES(
  IDX_1 LOCAL,
  IDX_2 LOCAL
);
  

Note that UPDATE INDEXES converts IDX_1 and IDX_2 and convert them to LOCAL indexes, on the fly.
Source: Oracle Documentation
I also recommend to look at Oracle-base.com for further reading.

Friday, November 8, 2019

How to list subpartitions and their LOB segment sizes




The query below will list the lob segment names, the lob subpartition names, the table subpartition names and sizing information for any subpartitioned tables using LOB columns:

SELECT  ts.table_owner "table owner",
        ts.table_name "table name",
        s.SEGMENT_NAME "segment name",
        s.SEGMENT_TYPE "segment type",
        s.SEGMENT_SUBTYPE "lob type",
        s.PARTITION_NAME "lob subpart name",
        lsp.column_name "lob name",
        lsp.compression "lob compression",
        lsp.subpartition_name "table subpartition name",
        ts.num_rows "num rows",
        lsp.tablespace_name "tablespace",
        ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_subpartitions lsp
ON (s.partition_name = lsp.lob_subpartition_name)  
   JOIN DBA_TAB_SUBPARTITIONS ts
        ON (TS.SUBPARTITION_NAME = lsp.SUBPARTITION_NAME) 
WHERE lsp.table_name='DOCUMENTS'
AND   ts.table_name='DOCUMENTS'
-- To limit the output to a specific tablespace, uncomment line below
-- AND   s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lsp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name 
                        FROM dba_lob_partitions lpt 
                        WHERE lpt.table_name IN ( 'DOCUMENTS' ) )

GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lsp.COMPRESSION,lsp.subpartition_name, lsp.column_name,ts.num_rows,lsp.tablespace_name
/*
To limit output to lob subpartitions with a specific size, 
uncomment the restriction above and change the operator to suit your needs ( <, >, =) 
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY lsp.subpartition_name DESC;


Example output:


TABLE_NAME segment name segment type lob type lob subpart name lob compression table subpartition name num rows tablespace size GB
MYTABLE MYTABLE_LOB7 LOB SUBPARTITION SECUREFILE KLM_DOCS_2018_01 MEDIUM KLM_SP_2018_01
164497
DOCS2018
235
MYTABLE MYTABLE_LOB7 LOB SUBPARTITION SECUREFILE VISA_DOCS_2018_01 MEDIUM VISA_SP_2018_01
72405
DOCS2018
76
MYTABLE MYTABLE_LOB7 LOB SUBPARTITION SECUREFILE KLM_DOCS_2017_10 MEDIUM KLM_SP_2017_10
16256
DOCS2017
19
MYTABLE MYTABLE_SMALL_LOB7 LOB SUBPARTITION SECUREFILE KLM_DOCS_SMALL_2018_01 MEDIUM KLM_SP_2018_01
164497
DOCS2018
18
MYTABLE MYTABLE_SMALL_LOB7 LOB SUBPARTITION SECUREFILE VISA_DOCS_SMALL_2018_01 MEDIUM VISA_SP_2018_01
72405
DOCS2018
8

Monday, November 4, 2019

What do do when an upgrade from 12cR1 to 18c is seemingly frozen in Phase #:50



During an upgrade from 12.1 to 18.0 my upgrade process seemed to be completely frozen at stage 50:
Restart  Phase #:47   [proddb01] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [proddb01] Files:1    Time: 4s
Restart  Phase #:49   [proddb01] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [proddb01] Files:1

The catupgrd0.log file located in the $ORACLE_HOME/cfgtoollogs/proddb01/upgrade20191103120250 showed that the last entry was
12:23:40 SQL> update tabpart$
12:23:40   2    set hiboundval='4294967296', bhiboundval = '06C52B5F614961'
12:23:40   3    where obj# in (select t1.obj# as part_obj_no
12:23:40   4      from sys.tabpart$ t1
12:23:40   5        left join sys.tabpart$ tmax
12:23:40   6          on (t1.bo#=tmax.bo# and t1.part# < tmax.part#)
12:23:40   7        join sys.tab$ t3 on (t1.bo#=t3.obj#)
12:23:40   8      where tmax.part# is null and t1.hiboundval is not null
12:23:40   9        and t1.hiboundlen = 10 and bitand(t3.property/power(2, 75), 1) = 1);
This may happen when your database have tables with lots and lots of partitions. A check against the database proves this:
select table_owner,count(*)
from dba_tab_partitions
where table_owner <> 'SYS'
group by table_owner
order by 2 desc;
TABLE_OWNER COUNT(*)
USER1
3497525
USER2
569471
USER3
288664
USER4
59857
USER5
28032
USER6
7239
USER7
1481

In our case, the grant total upgrade time was 10 hours and 11 minutes.
The "update tabpart$" statement above counted for 9,5 hours of this time.

The team responsible for application design later revealed that they have been meaning to redesign the tables to cut down on the number of partitions, but not yet come around to it.

Lession learned....

;-)

Friday, October 11, 2019

New security feature in Oracle 12.2: INACTIVE_ACCOUNT_TIME



A profile in Oracle 12.2 can now be configured with the setting INACTIVE_ACCOUNT_TIME, which specifies the maximum number of days an account can remain unused. Unless a new login occur within the specified number of days, the account will be automatically locked.

If not set, any custom-made profile will inherit the setting of the DEFAULT profile, which is UNLIMITED.

Syntax:
CREATE PROFILE my_profile LIMIT
INACTIVE_ACCOUNT_TIME 30;

ALTER PROFILE my_profile LIMIT
INACTIVE_ACCOUNT_TIME 30;

Sources:
Oracle 12.2 New Features guide

Automatically Locking Inactive Database User Accounts


Wednesday, October 9, 2019

Oracle streams being terminated with Oracle 18c


As noted in the "Desupported features" section of the Oracle 18c documentation, Oracle Streams will be terminated in with the release of Oracle 18c.

For replication, Oracle Corporation encourage their customers to use GoldenGate.