Skip navigation.

David Lozano Lucas's blog

Progress status of an index rebuild or creation, or other long operations.

David Lozano Lucas's picture

When we launch a long operation, such as a RMAN backup or rebuild of a large index, we can come to despair of not having an estimate of the time it may take. We can even come to think that is doing nothing.

For the progress of a long operation we can query the view V$SESSION_LONGOPS, first obtaining the process ID from V$SESSION. In the case of DBA, we know exactly which user is rebuilding the index, so we can simplify it into a single query.

The following example shows the progress of the reconstruction of a partition of an index.

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

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]
# =======================================================================