Follow-up On Cool Features Webinar – Jan 2021

Wow! Mike and I gave a webinar yesterday: Cool Features – not only for DBAs. We showed a lot of cool features – and the audience recognised that by asking really cool questions. We had more than 100 questions to answer live, and below you can find the answer to those questions that we had to investigate further.

But first a few practical remarks:

Virtual Classroom Series - Upgrade to Oracle Database 19c

Expression Based Parameters

Ahh – one of my new favourites. A viewer asked whether there is any syntax check when you make an expression based parameter. So lets try. First command should work:

SQL> alter session set resumable_timeout='3000/2';

Session Altered.

Now, let’s make a syntax error and see what happens:

SQL> alter session set resumable_timeout='3000//2';

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2';

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2' scope=both;

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2' scope=memory;

ORA-32005: error while parsing size specification [3000//2]

SQL> alter system set resumable_timeout='3000//2' scope=spfile;

ORA-32005: error while parsing size specification [3000//2]

Conclusion: Your expressions are checked when you issue the ALTER SESSION or ALTER SYSTEM command.

But take care when using environment variables in your expressions. If the environment variable is available when you issue the statement, the command will succeed. However, if the environment variable is not present when the database restarts, then you will have a problem. This could be the case if you forget to add the environment variable to your profile or the environment in Grid Infrastructure (srvctl setenv). If this happens, the startup will error out.

And finally, the expressions you put into a pfile are not checked before the database starts using that pfile. If there are invalid expressions in your pfile, the database startup will error out as well.

Online Table Move

Indexes and LOBs

When you move a table online the indexes remain valid during and after the move. Optionally, you can specify the UPDATE INDEXES clause to change the index storage attributes as well (like moving index to a new tablespace):

SQL> alter table t1 move online tablespace data update indexes(i1 tablespace data);

The initial version of blog post claimed that indexes become unusable during online table move unless you specified the UPDATE INDEXES clause. This is not true! Kudos to Olaf Nowatzki for information me. Thanks!

The LOB segments that are created to support the LOB columns remain in the same tablespace, even if you move the table to a new tablespace. But if you want, you can also move the LOB segment as well:

SQL> alter table t1 move online tablespace data lob(c1) store as (tablespace data);

A viewer was concerned whether he could move a table online and also update indexes because one of the indexes was really wide. Apparently, in a previous version this had been an issue to them. I tested this out with an index on 10 columns defined as VARCHAR2(128 BYTE). And this was not a problem. If you index is even wider; test it yourself – or consider whether you really need such an index.

It is supported move an IOT online as well – but not partitioned IOTs.

The Rest

If the table has unused columns when you move it, it will still have unused columns. They are not affected by a move operation. Actually, Connor McDonald made a really good video where he talks about unused columns.

Also, an attendee wanted to know whether the online command generated more UNDO or TEMP. Let’s see what happens with my 125 MB large table (no indexes):

Metric Online move Regular move
undo change vector size 96504 37988
session pga memory 5750416 4243088

As you can see it does require more UNDO to move the table online. But the numbers might change on a busy system with many indexes and LOBs. TEMP appears to be relative unaffected – it might change if I have unique indexes and must rebuild those. Lesson learned – expect more resource usage – test before trying in production.

And finally – does online table move work on tables with OLS (Label Security) policies? I don’t know – so far I haven’t been able to get confirmation. My immediate answer is NO. Take for instance DBMS_REDEFINITION – it is not supported when the table has OLS policies, so I assume the same applies for online table move.

Online Convert To Partitioned Table

This feature only works on a non-partitioned (or regular table). If your table is already partitioned, and you want to change to a different partitioning method, you must use another method (like DBMS_REDEFINITION).

Online Data File Move

When you move a data file online, the database creates an exact copy of the data file in the new location. It is bit-by-bit identifical.

This also means that any free space in the data file is not reclaimed, nor is the High Water Mark affected.

Standby Database Operations

I demoed how the standby database in 19c can do automatic flashback, when the primary database flashes back. A viewer asked whether you can flash back to the same restore point multiple times without the standby database complaining.

My immediate answer was: yes, you can flash back to the same restore point multiple times. The documentation doesn’t mention such a restriction, so I was eager to try it out.

I made a data guard setup in OCI and I was able to flash back to the same restore point three times – and the standby database followed each time.

Gradual Password Rollover

With gradual password rollover a user can temporarily have two passwords. A viewer asked whether you can identify the sessions that are still connecting with the old password. And you can with unified audit trail provided you are auditing logons. In that case simply look at the AUTHENTICATION_TYPE column of the audit entries for LOGON actions:

SQL> select   authentication_type, event_timestamp 
     from     unified_audit_trail 
     where    action_name='LOGON' and dbusername='APP_USER' 
     order by event_timestamp;

Your result will be something like:


Look at the VERIFIER information. If a user is using the old password in a rollover period, the identifier is suffixed -OLD. Similar, the suffix is -NEW if the new password is used.

This is a very useful addition to gradual password rollover.


Thanks to everyone that participated today. We had so much fun preparing the webinar for you. And really – thanks for all the cool questions. Doing stuff remote is really hard, and you really feel disconnected from the audience. But it helps a lot with good questions.

See you next time.

4 thoughts on “Follow-up On Cool Features Webinar – Jan 2021

  1. Something “annoying” about “Online Data File Move” in PDB is that the behaviour is not the same when you do the “alter move 12” and when you create a new datafile.
    At least in 12.2, for some reason, it will chose different path, or at least when I did a small test..
    Wonder if it was me not having enough coffee though.. 🙂

    — Inside 12.2 PDB “SNGLE_PDB”
    SQL> show con_name


    — PDB db_create_file_dest
    ———————————— ———– ——————————
    db_create_file_dest string /u01/oradata

    — Original SYSTEM Datafile location “chosen by DBCA” is “/u01/oradata/singledb/sngle_pdb/system01.dbf”

    ——- —————————— —————————————————————————————-
    14 P42 /u01/oradata/SINGLEDB/datafile/o1_mf_p42_hlhzpvp1_.dbf
    13 P42 /u01/oradata/SINGLEDB/A844C5E834541B8AE0530B020A0AF6C5/datafile/o1_mf_p42_hgmqb8kq_.dbf
    10 SYSAUX /u01/oradata/singledb/sngle_pdb/sysaux01.dbf

    9 SYSTEM /u01/oradata/singledb/sngle_pdb/system01.dbf

    11 UNDOTBS1 /u01/oradata/singledb/sngle_pdb/undotbs01.dbf
    12 USERS /u01/oradata/singledb/sngle_pdb/users01.dbf

    — Moving datafile and leaving system to chose new location

    SQL> alter database move datafile 9;

    Database altered.

    — Is moved to “/u01/oradata/SINGLEDB/datafile/” and change file_name to “o1_mf_system_j1o3jz47_.dbf” (good OMF works)

    ——- —————————— ——————————————————————————– ——-
    14 P42 /u01/oradata/SINGLEDB/datafile/o1_mf_p42_hlhzpvp1_.dbf
    13 P42 /u01/oradata/SINGLEDB/A844C5E834541B8AE0530B020A0AF6C5/datafile/o1_mf_p42_hgmqb8kq_.dbf
    10 SYSAUX /u01/oradata/singledb/sngle_pdb/sysaux01.dbf

    9 SYSTEM /u01/oradata/SINGLEDB/datafile/o1_mf_system_j1o3jz47_.dbf <<<<<<<<<<<< ALTER TABLESPACE SYSTEM
    SIZE 5M;

    Tablespace altered.

    — File 16 created with PDB GUID path included on it

    ——- —————————— ——————————————————————————————
    15 P42 /u01/oradata/SINGLEDB/A844C5E834541B8AE0530B020A0AF6C5/datafile/o1_mf_p42_j1o3pnxh_.dbf
    14 P42 /u01/oradata/SINGLEDB/datafile/o1_mf_p42_hlhzpvp1_.dbf
    13 P42 /u01/oradata/SINGLEDB/A844C5E834541B8AE0530B020A0AF6C5/datafile/o1_mf_p42_hgmqb8kq_.dbf
    10 SYSAUX /u01/oradata/singledb/sngle_pdb/sysaux01.dbf

    9 SYSTEM /u01/oradata/SINGLEDB/datafile/o1_mf_system_j1o3jz47_.dbf

    16 SYSTEM /u01/oradata/SINGLEDB/A844C5E834541B8AE0530B020A0AF6C5/datafile/o1_mf_system_j1o43st4_.dbf

    11 UNDOTBS1 /u01/oradata/singledb/sngle_pdb/undotbs01.dbf
    12 USERS /u01/oradata/singledb/sngle_pdb/users01.dbf


  2. Hi Victor,
    I agree with you – we need more coffee! Joking aside. That does look like an inconsistency. I would expect the behaviour to be the same, and that the data files are moved into the location that includes the GUID. Have you tried it in a recent version? 12.2 is after all fairly old – and such a simple bug is probably not backported into an RU.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s