utlrp.sql – How to decrease resource consumption with utlprp.sql

 utlrp.sql - How to decrease resource consumption with utlprp.sqlJust being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before.

One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql.… [Read More]

Other people’s thoughts: “Should you upgrade to 12c?”

You still don’t believe me yet when I say: You can’t seriously wait for the so called 2nd release of Oracle Database 12c as it will be an entirely new release again? And you haven’t upgraded yet to Oracle Database 12.1.0.2 for various reasons?

Then sometimes it’s good to listen to other people’s opinions – and I’m happy to share this 6 minute video by Tim Hall (very well known for his great page oracle-base).… [Read More]

The MAGIC Questions

Almost every week Roy, Carol and I receive one or more emails in the following style:

“Hey, we (or my customer) plan(s) an upgrade to Oracle 11g. We (or the customer) wants zero downtime. Currently we (or they) are on AIX with Oracle 10g (and someold  9i) databases. Can we get an advice please?”

or another one here …

“Upgrade from 8i to 11g.

[Read More]

Will gathering fixed object stats reduce recompilation time post upgrade?

Interesting question, isn’t it?
Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered?

First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job].… [Read More]

Move to Locally Managed Tablespaces

As I’ve got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here’s some additional information and an example.

To find out if a tablespace is dictionary or locally managed you’d use this query:

SQL> select
TABLESPACE_NAME, EXTENT_MANAGEMENT from DBA_TABLESPACES;

The procedure to migrate to locally managed tablespace is:

SQL> EXECUTE
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘tbs’);

And the whole migration procedure would look like this:

STARTUP RESTRICT
EXCLUSIVE;
EXEC
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘TBS’);
— do this for all tablespaces except SYSTEM, TEMP and SYSAUX
DROP TABLESPACE
TEMP;
— necessary if there’s no “real” temp tablespace definded yet – see Note:160426.1
CREATE TEMPORARY
TABLESPACE TEMP TEMPFILE ‘f’ SIZE 10M;
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER TABLESPACE
SYSAUX OFFLINE;
ALTER TABLESPACE
USERS READ ONLY;
— for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
EXEC
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘SYSTEM’);
ALTER TABLESPACE
SYSAUX ONLINE;
ALTER TABLESPACE
USERS READ WRITE;
— for all tablespaces except SYSTEM, RBS, TEMP and SYSAUX
SHUTDOWN IMMEDIATE

STARTUP

[Read More]