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:
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:
| authentication_type |
| (TYPE=(DATABASE));(CLIENT ADDRESS=((PROTOCOL=tcp)(HOST=10.0.1.225)(PORT=24974)));(LOGON_INFO=((VERIFIER=12C-NEW) (CLIENT_CAPABILITIES=O5L_NP,O7L_MR,O8L_LI))); |
| (TYPE=(DATABASE));(CLIENT ADDRESS=((PROTOCOL=tcp)(HOST=10.0.1.225)(PORT=24983)));(LOGON_INFO=((VERIFIER=12C-OLD) (CLIENT_CAPABILITIES=O5L_NP,O7L_MR,O8L_LI))); |
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.
Conclusion
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.
Databases Are Fun 