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

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....

;-)

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

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.

How to work around ORA-03206 when running the RCU (Repository Creation Utility) used by ODI

During installation you may encounter the error

ORA-03206: maximum file size of (13107200) blocks in AUTOEXTEND clause is out of range

In my case, the RCU attempted to create a temporary tablespace with autoextend of 100G. This is too much for a smallfile tablespace, and the error is thrown.
I checked the database creation scripts, and it is indeed created with smallfile as the default tablespace type.

To solve the problem, alter your database default setting:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

and retry the operation.

Documentation for Oracle 12.2 can be found here

How to create a proxy authenticated user in Oracle

Create a general user for authentication:

CREATE USER APP_POOL
IDENTIFIED BY app_pool_users123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO APP_POOL;

To illustrate how proxy authentication can be used efficiently, I create two roles:

create role app_pool_role1 not identified;
create role app_pool_role2 not identified;

Grant object privileges on two different tables to the two new roles:

grant select on SCOTT.DEPT to app_pool_role1;
grant select on SCOTT.EMP to app_pool_role2;

Create a user:

CREATE USER VEGARD
IDENTIFIED BY vegard123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:

GRANT CREATE SESSION TO VEGARD;
GRANT APP_POOL_ROLE1 TO VEGARD;
GRANT APP_POOL_ROLE2 TO VEGARD;
ALTER USER VEGARD DEFAULT ROLE ALL;

Change the user so it connects through a proxy user.
Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:

ALTER USER VEGARD
GRANT CONNECT THROUGH APP_POOL
WITH ROLE APP_POOL_ROLE1;

Let's connect to the database:

sqlplus app_pool[vegard]/app_pool_users123@pdb01

Verify the connection and session settings by using sys_context:

select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
       decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
       sys_context('USERENV','CURRENT_SCHEMA') "current schema",
       sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
       decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
       sys_context('USERENV','PROXY_USER') "proxy user"
from dual;

Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:

AuthIdentity used	auth method	current schema	grantee of privileges used	identification type	proxy user
VEGARD	proxy	VEGARD	VEGARD	Password	APP_POOL

Which roles are enabled?

select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
from dual
union
select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
from dual
;

Result shows that only APP_POOL_ROLE1 is enabled:

Role granted?
APP_POOL_ROLE1: TRUE
APP_POOL_ROLE2: FALSE

Let's verify that the roles are actually working.
Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:

VEGARD@pdb01 SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


VEGARD@pdb01 SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

There is nothing preventing the user VEGARD from connecting directly to the database:

sqlplus vegard/vegard123@pdb01
VEGARD@pdb01 SQL> show user
USER is "VEGARD"

When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:

VEGARD@vegdb01 SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

Some advantages with using proxy connections:

* No more password administration for individual users
* Proxy authentication allows for role based access control
* Identity preservation of the real user behind the proxy user is automatically set up

Keep in mind that you still have to create and provision the end user accounts.

How to change location for the redo log files in physical standby database

Short background:
After a cold restore of the database files from standbyserver1 to standbyserver2, the redo logfiles where incorrectly registered in the database's control file.
This resulted in an error about the missing file every time the database opened or mounted:

Errors in file /u01/oracle/diag/rdbms/prodbb01_stby2/proddb01/trace/proddb01_m000_38117.trc:
ORA-00312: online log 3 in thread 1: /fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Since this is a standby database it is not stopping the database from functioning as intended, but it was annoying nevertheless.
Here is how I fixed the problem:

Shutdown the database, cancel managed recovery and open it in mounted mode:

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
startup mount

Allow file renaming on the stadby by temporarily setting STANDBY_FILE_MANAGEMENT to MANUAL.

alter system set STANDBY_FILE_MANAGEMENT=manual scope=memory;

If this is not done, Oracle will throw error

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

Rename the file(s):

alter database rename file '/fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log' to '/fra/PRODDB01_STBY2/onlinelog/o1_mf_3__df26ddnv_.log';

Depending on your configuration, start managed reply in mounted state, or open the database in real-time query mode. I am doing the latter:

alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;