It is a common practice to always shutdown abort the database before restarting and shutting in down immediate. This is because sometimes SHUTDOWN IMMEDIATE takes ages. For instance due to a huge transaction to be rollback. I do not like it. At all. First, chance exists that you won’t be able to start the database […]
Month: July 2011
On deferred segment creation
What’s deferred segment creation? It is a feature that saves lots of time by releases and lots of space if you have a -legacy- application with 10’000 tables and most of them are empty. When was it introduced ? Partly in 11.2.0.1 and partly in 11.2.0.2, depending on the object type. What’s the opposite ? […]
Datapump : table like ‘FOO%’ or like ‘BAR%’
Today I tried to put two like condition in an INCLUDE clause of datapump. I have the following tables SQL> select table_name from user_tables order by 1; TABLE_NAME —————————— AAA BAR1 *** BAR2 *** BLA FOO *** FOO1 *** GOZ and I want tables like BAR% and tables likes FOO% First try : $ expdp […]
On star transformation
How to configure a schema to support a star transformation query? This is one of the topic I am currently preparing for the OCM DBA 11g upgrade exam for 9i OCM’s. Let’s create a star schema with a fact table T1 and two dimension tables T2 and T3 : > create table t2(y number constraint […]
On implicit commit
An explicit commit is when you issue a COMMIT statement SQL> create table t(x number); Table created. SQL> insert into t values(1); 1 row created. SQL> commit; Commit complete. An implicit commit is when a commit is issued without your approval. ex: AUTOCOMMIT (default is OFF) SQL> set autoc on SQL> insert into t values(1); […]
sqlplus -prelim
If you cannot login to the database, for instance due to ORA-00020 maximum number of processes exceeded, then chance exists that you could use the -prelim option. Documented in note 121779.1 for sqlplus version 10.1 and later : In some cases, no connections are allowed on the instance (in some ORA-20 situations for example). As […]