Skip navigation.

RDBMS Server

Oracle RDBMS Server Articles

Hints on OCA/OCP exam technique

Part of my job is teaching for Oracle University, and I'm often asked about OCP exam technique. Here are a few hints. The OCM exam is very different, and the confidentiality rules forbid me from discussing it, so please don't ask.

11.2 Grid Infrastructure networking enhancements

This note is an attempt to summarize some of the 11gR2 facilities that allow for greater virtualization of resources, reducing reliance on networking facilities supplied by the operating system. All corrections welcome.

Quick overview of the state of an Oracle database.

David Lozano Lucas's picture

One common problem: You are allocated to a new project and find that nothing is documented.
By running this script, you get four important points of information: Tablespace status, installed products and its versions, oracle parameters different to default and status of tables:

[code]
SET LINESIZE 1000
SET PAGESIZE 1000
spool report.txt
prompt You can leave this script copied at $ORACLE_HOME\rdbms\admin\
prompt - Remember to use also statspack o bstats depending on the Oracle version
prompt - Review alert.log
prompt
prompt
prompt TABLESPACE STATUS
Select t.tablespace_name "Table

Install Oracle on Windows 64 Bit and migrate Oracle Windows from 32 bit to 64 bit

maheritc's picture

Installing Oracle 11g 64 bit on Windows Server 2008
When installing Oracle 11.1.0.7 on Windows Server 2008 64 bit, the installer gave a an error saying the Operating System ( OS ) was not supporting. In looking at the install log on c:\program files\oracle, I noticed the installer thinks the OS is windows vista.

To work around this error, I selected the check box for user verified and continued with the install.

Use of the SQL Tuning Advisor

David Lozano Lucas's picture

Use the SQL Tuning Advisor for tuning SQL statements. Typically, you can run this advisor in response to an ADDM performance finding that recommends its use.

Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a user-defined SQL workload.

To run the SQL Tuning Advisor do the following:

Moving multiple tables and indexes between tablespaces at once

David Lozano Lucas's picture

Here I present a simple query to use when we want to move the tables and indexes of several users at once.

To make it one by one:

ALTER TABLE xxxxxx MOVE TABLESPACE TEST;
ALTER INDEX xxxxxx REBUILD TABLESPACE TEST;

To move data from multiple owners.

Saluting. Start - Stop script

David Lozano Lucas's picture

It is an English word that sounds very funny in Spanish, almost unreal.
Well, just wanted to brand the blog with a first entry to introduce myself.
I work as an Oracle and SQL Server DBA (mixed profile, they call it -
concerns twice for the same pay, on me).
Here I will write "how to" articles as they emerge in my day to day.
If you can read Spanish, check out the blog I maintain for a little more time http://blog.davidlozanolucas.com/.

Here is a script to start/stop Oracle databases running in Unix:

[code]
# =======================================================================

The ever expanding UNDO tablespace.

During a quite evening of my last on-call bout I was alerted from our monitors that the UNDO tablespace was running out of free space. Thought of adding of a new data file and be done with it; When I checked the current allocation for this tablespace it was already at 40G - couldn't believe what I was seeing. The undo_retention was set to 7200 and max query length in v$undostat was not that high. One column that did caught my eye was the tuned_undoretention, its value was way very high.

Performing a quick and efficient Performance Tuning audit

Checks to be performed at the machine level (note the example is Red Hat Linux)

run queue should be ideally not more than the number of CPU’s on the machine

At the maximum it should never be more than twice the number of CPU’s.

This is denoted by the column ‘r’ in the vmstat output shown below

vmstat – 5

face:/opt/oracle>vmstat 5
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 1 488700 245704 178276 12513572 0 1 10 17 48 1365 40 12 43 5

How to import table with custom type

When importing table that contained custom type, you will encounter an oracle error as shown below:
Import of table containing object type(s) fails with IMP-00061 IMP-00063

cause:
When you import a table that references a type, but a type of that name
already exists in the database. Import attempts to verify that the pre-existing
type is in fact the type used by the table(rather than a different type that
just happens to have the same name).