UNDO Data for a LOB Segment is Kept within the LOB Segment Space

Never realized this before. See metalink note:
How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM [ID 386341.1] for details.

Using command to free the space:
alter table <tablename> modify lob(<lob_column>) (shrink space [cascade]);

Upgrade from Oracle Database 10.2.0.4 to 11.2.0.1

Just upgraded one of our databases from 10.2.0.4 to 11.2.0.1. The following outlines the major steps:

1. Install Oracle 11.2.0.1 software. Choose 'install database software only' option. Specify a location for Oracle Base that is required for Oracle 11g diagnostic framework        


2. Perform complete pre-upgrade checklist according to metalink note 870814.1, including:
1) Identify invalid objects and recompile them.  2) Resolve TIMESTAMP WITH TIMEZONE Datatype. Metalink note 815679.1 provides excellent explanation and solution on this. In my case I don't need to perform any action. 3) Update optimizer statistics, including dictonary and SYS schema 4) Run utlu112i.sql to identify issues and correct them, including free space in the tablespaces, size of some of the pools
3. !!Backup the database!!
 
4. Perform the upgrade with dbua
On the first try, I had issue and the upgrade aborted with ORA-0431 error and had to restore the database from the backup. The database uses AMM. However after increasing the sga_target from 700M to 1G, I was able to upgrade to the database successfully on the second try. 


5. Perform post-upgrade, including
1) Update some of the init.ora parameters, such as compatible, using memory_target, etc. 2) Update all the scripts, such as backup, start/stop scripts to reflect the new Oracle home  

Search, Drill-down History sessions, SQL Text and Execution Plan with Oracle EM Grid Control

As an DBA who has worked with many database administration tools, I often need to be able to find queries that users ran recently, for being able to view the queries and their execution plans, and tune them. Oracle 10g onwards provide views such as  DBA_HIST_SQLTEXT, DBA_HIST_SQL_PLAN that can be queried to find out the queries and their plans.

Oracle 10g EM Grid onwards provide intuitive GUI interface to search and drill-down history sessions, sql text and execution plan.

Go to the database target -> Performance tab -> Search SQL link at the bottom. It can search from Cursor Cache, AWR Snapshots, AWR Baseline, SQL Tuning Set. Note in the search box for SQL Text LIKE, the wildchar % or ? needs to be used, and it is not case-sensitive.

Run a sql statement from sql*plus
SQL> select * from testniej;


Searching with the wildchar returns the query and its SQL ID, click the SQL ID we can drill down to the query: its statistics, plan, etc.

Searching without the wildchar returns nothing

Datapump Job Finished without Doing Anything

Helped another DBA to figure out the problem. The datapump finished without doing anything, with a full trace TRACE=1FF0300, there was nothing specific in the trace file.
-------------------------------------
......
KUPM: 13:55:39.631: dropping master since job never started
KUPM: 13:55:39.631: *******OUT DISPATCH  , request type=1004   response type =2041
KUPC: 13:55:39.632: Before ENQ: Sending Type: 2041 ID: RP,KUPC$C_1_20100915135538,MCP,KUPC$A_1_20100915135539,10,Y
KUPM: 13:55:39.632: Exited main loop...
KUPV: 13:55:39.633: Update request for job: SYS.SYS_IMPORT_FULL_01, func: 1
KUPM: 13:55:39.634: Entered state: UNDEFINED
KUPM: 13:55:39.634: In RESPOND_TO_START
KUPM: 13:55:39.634: In check_workers...
KUPM: 13:55:39.634: Job is completing
.......
-------------------------------------------

Searched the alert.log file found an error: ORA-25153: Temporary Tablespace is Empty

Found the temporaory tablespace didn't have any temporary datafile, which was not added back when the DBA recreated the controlfile. After adding the temporary datafile back, the problem was resolved.