-- Do not forget about the Database recycle bin --

Hello Everybody

Just a quick post reminder to keep in mind that starting on Oracle 10g when you drop objects they still recide in your recycle bin. So if you wonder why after dropping all those tables you do not see the space back, you got your answer. This is specially important if you have some DW like environment where you drop and recreate tables every day part of your ETL.

Every schema in the database has its own recyclebin, if you want to know what is the content of the entire database recycle bin you can do this query.

If you only want to query your schema recycle bin then you execute the following.

In order to be able to purge an object from the recycle bin you need to either own the object or you need to have the "drop any" system privilege assigned to you. 

If you want to purge objects in the recyclebin for the entire database, you need to have the sysdba privilege, be connected as sysdba and execute the following.

SQL> purge dba_recyclebin;

If you want to purge your own recycle bin you do the following.

SQL> purge recyclebin;

If you want to purge objects in the recycle bin stored in an specific tablespace you execute the following:

SQL> purge tablespace ;

If you want to be more specific and purge objects in a specific tablespace for a specific user you execute the following.

SQL> purge tablespace user ;

Last but no least if you want to purge the recycle bin for a table you own and you just dropped you execute the following.

SQL> purge table ;

If that table has indexes associated you execute the following.

SQL> purge index ;

Enjoy. 

-- A Quick handy guide to generating SQL Trace --

We all have done this a time or another, and then we have to do it again and we have to go back to our notes to remember the steps, this is well-known information, but I encourage you to bookmark it and have it handy as you will eventually need to use it as well.

Tracing the execution of a SQL

alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12';

/* now you execute the sql that you want to trace */

exit;

The trace file will be located in your diagnostic destination and will have the number 10046 as part of the file_name, you want to go there to format it using tkprof.  

tkprof input_file output_file waits=yes explain=username/password

Note: you can use the sort clause to sort the output in many ways, for a complete description of options please check the following resources:

- A tkprof guide.

- A Guide to tune poor performing SQLs.

- More about the 10046 event

- How to interpret the Trace

Cheers!!!!

-- Oracle 11g Optimizer and Incremental Statistics --

As I was doing some research about the 11g optimizer I found out that in 11g you can now gather Incremental statistics for partitioned tables. The idea behind this feature is to be able to capture global statistics on large partitioned tables at a lower resource cost by minimizing the time required to collect statistics.

This makes complete sense, think about a large range partitioned table, for example, SALES_DATA. Let's assume that your SALES_DATA table is partitioned by month, it is very unlikely that old sales data will change, especially data that correspond to previous years. As you know Oracle keeps statistics at the partition level and overall statistics for the table,  Oracle monitors DML operations at the table and subpartition levels, normally statistics are gathered only for those partitions that changed (> than 10%), however, global statistics are gathered by scanning the entire table and that makes it a very expensive operation.

In order to do that, you need to first set the table preferences to incremental.

SQL> exec dbms_stats.set_table_prefs('TPM','SALES_DATA','INCREMENTAL','TRUE');

Next we gather the global statistics

SQL> exec dbms_stats.gather_table_stats('TPM','SALES_DATA', GRANULARITY=> 'GLOBAL');

If you query the last analyzed column from users_table you will see that the timestamp is updated, however when you query the same column on user_tab_partitions you will see that not all the partitions got updated, furthermore you should see a different on the time it took to gather the statistics.

-- Oracle 12c Grid Infrastructure Management Repository MGMTDB --

After installing Oracle 12c Grid Infrastructure on an ODA X3-2 we started getting a warning from Enterprise Manager alerting us that the target MGMTDB Database SYSMGMTDATA tablespace is 85% full. As you can imagine we were surprised as we did not recognize that database. We reached out to few people before and realized that this must be something new.

Starting 12.1.0.2 Oracle creates a new mandatory database called MGMTDB, Oracle creates on it Cluster Health Monitoring (CHM) Data among others, this database gets failover to a surviving node in case one node goes down, the database needs to be up and running, it starts up automatically.

Please note: There is a minus sign in from of the database name

Database name: -MGMTDB
ORACLE_HOME: Same as the GRID_HOME

I am not sure about the password for SYS or SYSTEM on it, but you can connect as sysdba

There are 3 tablespaces on it (UNDO, SYSTEM and SYSAUX) , you do not have to worry about space management since the datafiles are auto-extensible.

MOS 2065175.1 contains information about how to use MDBUtil tool, this tool allows you to perform a variety of operations with the management repository. The best article I found with information related to this topic is from Amit Bansai and you can access it on this link.