Oracle RAC 10G -- Learning to know

Nov 18 – 2006 : The day I came to know that I am going to involve in a new project which is going to on Real Application Clusters, with a project of Asia-Pacific regions to be live on this and targeting a data of 4TB and more. As I am new to this and very enthusiastic to learn more. To start with, I browsed Oracle Documents for Oracle RAC 10g R1 online documentation and few others to learn more. In fact I got a corporate training which helped me a lot to go with.

I found few of the books which helped me a lot,
1. Oracle Online Documenation
2. Oracle 10g Grid & Real Application Clusters

Killing a User’s Session

SQL> SELECT sid, serial# FROM v$session
2* WHERE username='user_name';
SID SERIAL#
----------------
10 32

SQL> ALTER SYSTEM KILL SESSION '10, 32’;
System altered.

If the session you want to kill is involved in a long operation, such as a lengthy rollback, Oracle will inform you that the session is marked for kill and it will be killed after the operation is completed.

When Oracle kills a session, it rolls back all ongoing transactions and releases all session locks.

If the UNIX process of the user is killed, the Oracle session will most likely be killed also, but that isn’t the most graceful way to end a session.

OS Level - If you want to End the section - USE KILL

If you think you must kill a user’s UNIX session, and the Oracle KILL SESSION command isn’t working, or it’s taking a long time, you can terminate the session rather abruptly by using the UNIX kill command as follows.

$ kill -9 345678

Use the SQL Syntax - to find the - USER

SQL> SELECT process,sid,serial# FROM v$session
WHERE username='&user';

Enter value for user: SOMEUSER
old 2: username='&user'
new 2: username='SOMEUSER'
PROCESS SID SERIAL#
-----------------------------------
2920:2836 10 34

How about working with WINDOWS?

ORAKILL - Utility

Oracle user’s session on Windows, can use the ORAKILL utility, which will kill a specific thread under the Oracle .exe process.

How do you find out what that user’s thread is?

SQL> SELECT sid, spid as thread, osuser, s.program
2 FROM v$process p, v$session s
3* WHERE p.addr = s.paddr;

SID THREAD OSUSER PROGRAM
-------------------------------------------------------------
1 1192 SYSTEM ORACLE.EXE
2 1420 SYSTEM ORACLE.EXE
3 1524 SYSTEM ORACLE.EXE
4 1552 SYSTEM ORACLE.EXE
5 1528 SYSTEM ORACLE.EXE
6 1540 SYSTEM ORACLE.EXE
7 1580 SYSTEM ORACLE.EXE
8 1680 SYSTEM ORACLE.EXE
9 2948 NETBSA\user sqlplusw.exe
10 4072 NETBSA\user sqlplusw.exe
10 rows selected.

Find the thread number where you want to kill...

C:> orakill 2948

Unix Commands that always usefull

netstat -na grep 1522
ps -ef grep lsnr
glance -j 1
du -k sort -n
du -sk * sort -nrhead
df -k grep /bkdump
ifconfig -a
du -sh *
ipcs -m grep ora

What is blocking at the DB Level

On web i found this script which helped me a lot :-)
***************************************************
SQL> select s1.username '@' s1.machine
2 ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------------------
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=32 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=36 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=45 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=49 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=72 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=86 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=90 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=99 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=123 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=141 )

10 rows selected.

SQL> alter system kill session '88,24356';
System altered.
SQL> select * from v$lock where block <> 0 or request <> 0;
no rows selected
************************************
Please let me know your experience in handling this issue.

Logging In As a Different User – When you don’t know the Password

You may need to sometimes log in as another DBA to perform certain actions. However, even the Oracle DBA doesn’t have access to users’ passwords, which are stored in an encrypted form. You could use the ALTER USER statement to change the user’s password, but you might not want to inconvenience the user by changing the password for good.

In a case like this, you can change the password of a user temporarily and use the new password to get in as that user. Before you change a user’s password, get the original encrypted password, which you can use to reset the user’s password back after you’re done. Here’s an example:

SQL> SELECT 'alter user tester identified by values 'password';'
2 FROM dba_users
3* WHERE username='TESTER';

'ALTERUSERTESTERIDENTIFIED
---------------------------------------------------------
alter user tester identified by values 1825ACAA229030F1;

SQL>
Now change the password of user tester so you can log in as that user:

SQL> ALTER USER tester IDENTIFIED BY newpassword;
When you’re done using the tester user account, use the ALTER USER statement again to change user tester’s password back to its original value. Make sure you enclose the encrypted password in single quotes.

SQL> ALTER USER tester IDENTIFIED BY VALUES '1825ACAA229030F1';
User altered.

What would be the first thing you would do if an end user complains that performance is poor?

I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.
As for the answer to the question, one should touch upon all these points:
- Check alert log, for any errors and UN even entries
- Check for archive log destination/file system full
- Check for database locks # Click here for a notes on locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates as been done from the Application team.

And few more from Oracle Online Documentation as well
1) Identifying and Gathering Data on Resource-Intensive SQL # Oracle Doc
2) Oracle9i Database Performance Tuning Guide and Reference # Oracle Doc
I recommend reading the documents from Oracle, which give clear understanding of all the above. Please add lines if you I miss something which is useful.

Some SQL Statements - For Day to Day tasks

1) To grep & Drop user objects before Import
select 'drop 'object_type' 'object_namedecode(object_type,'TABLE','
cascade constraints;',';')
from user_objects
where object_type!='INDEX'/
********************************************************************
PCT_FREE, PCT_USED
SQL> select TABLE_NAME, PCT_FREE, PCT_USED, EMPTY_BLOCKS, CHAIN_CNT from dba_tables
2 where CHAIN_CNT > 0;
TABLE_NAME PCT_FREE PCT_USED EMPTY_BLOCKS CHAIN_CNT
------------------------------ ---------- ---------- ------------ ----------
BBN_MAS_LIQ_DET_TSY_TMP 10 40 1977 392
ORD_LEDGER 10 40 1982 6609
************************************************************************
SQL> select object_name,object_type from user_objects where status='INVALID';
******************************************************************************

Oracle Applications DBA (Apps DBA)

Hmm as long from now being a core DBA from past 3 years I want move to Apps. Apps DBA is what Applications DBA is said to be. I want to assimilate my self for Application expertise and as well as with Oracle 10 G Rel 2.

In my searches I found this reliable site for Applications http://www.appsdba.com/ hope this will be very useful for Apps DBA.

I always found of increasing my errands and promoting myself with the market. But what Apps DBA does? Is this the question I always asks myself and hope this is the most common question for a core DBA who wants to upgrade for Apps? Yes! Hope so! :- )
I want to solve this in my blog – hope it helps to know the responsibilities of being an Apps DBA.

What duties, if any, are different for an Apps DBA versus those of an Oracle 8/9i DBA?

Oracle APPS DBA's duties are almost same as of Oracle DBA but your work will be more specific to the Architecture (or I should say Environment) and OFA of Oracle Application. Apart from that there are few more things you should know like applying patches or adadmin utility and some basic functionality of SYSADMIN responsibility of APPS.

An APPs DBA knows all that a regular DBA knows plus the following:

Application fundamentals such as concepts and architecture

Application Installation and configuration

Patching and Upgrades

Patch Management and Version Control

Customization Updates

Cloning requirements.

This can be frequent in an Apps environment- Concurrent Managers - Forms/Report Servers

Web Servers

all the "ad" utilities

all the database restrictions for the applications.

That is so that the installation is still supportable and conforms to all vendor (Oracle) requirements.

All special application related regular and maintenance functions. Such as month end and period end functions.

Understand any backup and recovery constraints. Such as for table imports (NOT) and partial recoveries.

Special requirements and restrictions for tuning, example optimizer and index requirements.

Printer Configuration

User Access AdministrationThis could be a matter for further discussion as it could be argued that all DBA's should be aware of the above or equivalent for any database application (Oracle or otherwise).

These are two core answers I found for being a Apps DBA.

Resolving a Database Failure

Resolving a Database Failure

Phase I: Diagnose the problem
Phase II: Restore appropriate files
Phase III: Recover the database
Phase IV: Back up the database

Phase I: Diagnose the problem

The first phase is to research the nature of the failure. Use V$ views, data dictionary views, trace and log files, basic operating system commands and Oracle Enterprise Manager to diagnose the problem.

Determine if the database instance is available and the database is open.

Attempt to start the instance.

Shutdown the instance if a problem occurs while starting it or when opening the database.

Check the trace files and alert log files.

Determine the appropriate recovery method:

- Closed database recovery
- Open database, offline tablespace recovery
- Open database, offline tablespace, individual datafile recovery
- Cancel-based recovery
- Time-based recovery
- Change-based recovery


Phase II: Restore Appropriate Files

Before you perform a recovery scenario, determine what files to restore and what state the instance and database must be in to perform the recovery. Remember that the objective is to minimize down time and loss of data, so do not restore files if it is not necessary.

Phase III: Recover the Database

Once the appropriate files are restored, initiate your recovery operation. After completing the recovery, note any proactive measures that can be taken to prevent that type of failure in the future.

Phase IV: Back up the Database

Not all recovery operations require a database backup when they are complete. However, determine whether your database needs to backed up and, if so, perform another backup.

Archive Log

Steps in changing database from no archive mode to archive mode:

sql/>shutdown normal/immediate/transactional (You can use any value)

sql/>startup mount

sql/>alter database archivelog

sql/>alter database open

Go for Full Database backup


Keep in mind a user must have ALTER SYSTEM PRIVILEGE to alter the archivelog mode of the database.

Setting the database in Archivelog mode doesnot enable the Archiver (ARCN) process!

AUTOMATIC & MANUAL Archiving

Set the parameter file for LOG_ARCHIVE_START=TRUE

Hmm mean while you can do multiple arcn process

LOG_ARCHIVE_MAX_PROCESSES

And a maximum of ten ARCN processes can be specified and its dynamic

Sql/>alter system set LOG_ARCHIVE_MAX_PROCESSES=3;

Well if you find more about this let me know.

Team Oracle

http://www.oracle.com/cluboracle/teamoracle/index.html

Are you with Oracle Team ???

Flying High with Team Oracle

RMAN Recovery Manager - Oracle 9i

Steps For Configuring Recovery Catalog

Step 1 :
Create A Seperate Database Or Tablespace For Storing RMAN Catalog.
In This Case We Are Storing The Recovery Catalog In The Same Database.

SQL> Create Tablespace Rman_Tablespace Datafile
2 'C:\Sql\Junept\Datafile\Rman01.dbf' Size 20m Autoextend On Next 1m;
Tablespace created.

Step 2 :
Create A Rman Repository Schema.
SQL> Create User Rman_User Identified By Rman Default Tablespace Rman_Tablespace
2 Temporary Tablespace Temp Quota Unlimited On Rman_Tablespace;
User created.

Step 3 :
Grant Privileges To The Rman User Setting The Default Tablespace To The One Create In Step 1
SQL> Grant Connect,Resource To Rman_User;
Grant succeeded.
SQL> Grant Recovery_Catalog_Owner To Rman_User;
Grant succeeded.

Step 4 :
Create The Catalog Using RMAN
Invoke Rman Utility As Below :
$ORACLE_HOME\Bin> RMAN Catalog Rman_User/Rman@Service
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to recovery catalog database
recovery catalog is not installed
RMAN>
Now At The Rman Prompt Create The Catalog Into Tablespace Created In Step 1
RMAN> Create Catalog Tablespace Rman_Tablespace;
recovery catalog created
Note : The Execution Of The Above Command Will Create Several Base Tables, View, Synonyms ,
Packages, Etc .
RMAN> Exit

Step 5 :
Now Register Your Database Into The Recovery Catalog.
RMAN_USER SQL> Select * From DB_Database;
no rows selected
The Above Statement Proves That Your Database Is Not Yet Registered Into The Recovery Catalog.
C:\>Rman Target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: JUNEPT (DBID=1185895820)
RMAN> Connect Catalog Rman_User/Rman@Service
connected to recovery catalog database
RMAN> Register Database;
database registered in recovery catalog (A)
starting full resync of recovery catalog
full resync complete
Note : The Above Command Updates All The Base Tables For Storing The Database Information Which Is Resistered.
To Prove The Above Said Issue The Following Commands :
RMAN_User SQL> Select Name From Rc_Database;
RMAN_User SQL> Select Tablespace_Name,Name From Rc_Datafile;
RMAN_User SQL> Select Name From Rc_Redo_Log;
/* The Configuration Of Recovery Catalog Is Completed. */

Oracle Interview Questions: Level 1

Most often asked:

About Interview Questions on Oracle.

http://get-best.com/IT_Interview_Questions/dbaQA1.htm

http://get-best.com/IT_Interview_Questions/dbaQA2.htm

http://get-best.com/IT_Interview_Questions/dbaQA3.htm

http://get-best.com/IT_Interview_Questions/dbaQA4.htm


For more Technical Questions

http://blogs.ittoolbox.com/database/solutions/archives/006260.asp


Click the above link

Oracle Certification: OCP

Database Administrator: Database Administrators are responsible for administrative activities like installing database, taking backups, managing and fine-tuning performance over the network, and data recovery. They have the knowledge of basic architecture of the Oracle database. Visit Oracle DBA Certification Track page to gain detailed information about the exam, its details, types of questions, and how to prepare for the exam, if you are interested in taking the OCP-DBA certification.

http://www.whizlabs.com/ocp/oracle-dba.html


Oracle 9i DBA Path Details

Oracle Administrator track for Oracle 9i has been divided into the following three levels.

1.Associate Level (2 Exams). You need to clear the following exams:

Introduction to Oracle: SQL and PL/SQL OR
Introduction to Oracle 9i: SQL (online also)
Oracle 9i Database: Fundamentals I
Oracle 9i Database: Fundamentals II
Oracle 9i Database: Performance Tuning

*** To get OCP - Professional Level (2 Exams and one Oracle University hands-on course within the Oracle 9i DBA learning path). You need to clear the following exams:

How to check any deadlock and other locking issues

Normally deadlock generates dump file and automatically is released by oracle system process

1) check v$session

sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null

2) To kill a locked session, first need to find sid, serial and use

sqlplus>alter system kill session 'sid, serial#';

*** you need have dba priviledge to kill sessions

3. To find which SQL has lock wait

sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece

What a Oracle DBA can be?

This is the prime most where I visualized what a DBA is!

"DBA is a Database Administrator who logs onto the multi-user operating system as internal or system with the appropriate password is recognized by the operating system as the rightful owner of resources on the Hard disk drive. This person then becomes the Oracle DBA". --- IVAN BAYROSS

And the second question heave me is what a DBA can be!

For this I did not get right answer when I premeditated to take this line of work, but latter with perceptive of Oracle’s Workgroup I could able to learn that DBA’s are not only do backup, recover & tune the database but they design the database and more deeper I go I learned the depths of being a DBA. 10 months of expertise of mine and certification from Oracle made me to know the depths of being what a DBA holds.This is the first question always comes when we take up a new vocation – what I can, if I took up this line of work?So i put my solutions in this web log with the various works a DBA can handle.

Truly the job of DBA encompasses many roles. We will look at the roles which I learn in recent times.

There are DBAs who focus on logical design and DBAs who focus on physical design; DBAs who specialize in building systems and DBAs who specialize in maintaining and tuning systems; specialty DBAs and general-purpose DBAs.

System DBA:

A system DBA focuses on technical rather than business issues, primarily in the system administration area. Typical tasks center on the physical installation and performance of the DBMS software and can include the followingInstalling new DBMS versions and applying maintenance fixes supplied by the DBMS vendorSetting and tuning system parametersTuning the operating system, network, and transaction processors to work with the DBMSEnsuring appropriate storage for the DBMSEnabling the DBMS to work with storage devices and storage management softwareInterfacing with any other technologies required by database applicationsInstalling third-party DBA toolsSystem DBAs are rarely involved with actual implementation of databases and applications. They might get involved in application tuning when operating system parameters or complex DBMS parameters need to be altered.
Indeed, the job of system DBA usually exists only if the organization does not have an official
system administration or systems programming department.

Database Architect:

Some organizations create a separate position, database architect, for design and implementation of new databases. The database architect is involved in new design and development work only; he is not involved in maintenance, administration, or tuning of established databases and applications. The database architect designs new databases for new or existing applications.

The rationale for creating a separate position is that the skills required for designing new databases are different from the skills required to keep an existing database implementation up and running. A database architect is more likely than a general-purpose DBA to have data administration and modeling expertise.

Typical tasks performed by the database architect include:

Creating a logical data model (if no DA or data modeler position exists)Translating logical data models into physical database designsImplementing efficient databases, including specifying physical characteristics, designing efficient indexes, and mapping database objects to physical storage devicesAnalyzing data access and modification requirements to ensure efficient SQL and optimal database designCreating backup and recovery strategies for new databasesMost organizations do not staff a separate database architect position, instead requiring DBAs to work on both new and established database projects.

Database Analyst:

Another common staff position is the database analyst. There is really no set definition for this position. Sometimes junior DBAs are referred to as database analysts. Sometimes a database analyst performs a role similar to that of the database architect. Sometimes the data administrator is referred to as the database analyst or perhaps as the data analyst. And sometimes a database analyst is just another term used by some companies instead of database administratorData Modeler: A data modeler is usually responsible for a subset of the DA’s responsibilities.

Data modeling tasks include the following:

Collecting data requirements for development projectsAnalyzing the data requirementsDesigning project-based conceptual and logical data modelsCreating and updating a corporate data modelEnsuring that the DBAs have a sound understanding of the data models

Application DBA:

In direct contrast to the system DBA is the application DBA. The application DBA focuses on database design and the ongoing support and administration of databases for a specific application or applications. The application DBA is likely to be an expert at writing and debugging complex SQL and understands the best ways to incorporate database requests into application programs. The application DBA must also be capable of performing database change management, performance tuning, and most of the other roles of the DBA. The difference is the focus of the application DBA—it is on a specific subset of applications rather than the overall DBMS implementation and database environment

Task-oriented DBA

Larger organizations sometimes create very specialized DBAs that focus on a specific DBA task. However, task-oriented DBAs are quite rare outside of very large IT shops. One example of a task-oriented DBA is a backup-and-recovery DBA who devotes his entire day to ensuring the recoverability of the organization’s databases.Most organizations cannot afford this level of specialization, but when possible, task-oriented DBAs can ensure that very knowledgeable specialists tackle very important DBA tasks.

Performance Analyst:

Performance analysts are a specific type of task-oriented DBA. The performance analyst, more common than other task-oriented DBAs, focuses solely on the performance of database applications.

A performance analyst must understand the details and nuances of SQL coding for performance and be able to design databases for performance. A performance analyst will have very detailed technical knowledge of the DBMS so that he can make appropriate changes to DBMS and system parameters when required.

The performance analyst is usually the most skilled, senior member of the DBA staff, a role that he has grown into due to his experience and the respect he has gained in past tuning endeavors.

Data Warehouse Administrator:

Organizations that implement data warehouses for performing in-depth data analysis often staff DBAs specifically to monitor and support the data warehouse environment. Data warehouse administrators must be capable DBAs, but with a thorough understanding of the differences between a database that supports OLTP and a data warehouse.

Data warehouse administration requires experience with the following:

Business intelligence, query, and reporting toolsDatabase design for read-only accessData warehousing design issues such as star schemaData warehousing technologies such as OLAP (including ROLAP, MOLAP, and HOLAP)Data transformation and conversionData quality issuesData formats for loading and unloading of dataMiddleware

Want to Become an Oracle DBA

Often I come up with this query? How can I be an Oracle DBA and I always like to allocate my time on this. I hope the links help you if you are looking to be Oracle DBA and the measures you need to consider being a DBA.

http://www.databasejournal.com/features/

oracle/article.php/3335581

10 way's to keep the DBA Job!

http://searchoracle.techtarget.com/originalContent/

0,289142,sid41_gci918447,00.html

*** cut & paste the URL in your Browser

You can become an Oracle DBA, but here is the truth of the matter: there are practically zero jobs or job postings looking for DBA's with zero years of experience. "Entry level" in the Oracle DBA job world typically means 3-5 years of experience. Your IT certificate from the local community college means practically nothing. Your Oracle Certified Professional certificate may mean even less to some hiring managers (they don't know what it is, they've been burned by the paper MCSE, they value experience over a piece of paper, and so on). In this case, a certificate's true value lies within helping you get an interview with a company that uses Oracle.

File System re-organization: Oracle Internal Error

File System re-organization: Targeted: Internal Error 600 (For Below Post)

Oracle Internal Error:

As a Oracle database administrator, we must keep the file system perfect, to make accessible for end users. Well this augments my issue for the internal error which kept me on toe. So I started to work on this to try with this and almost absolute and reasonable.

Unused columns (Why disorganized & what creates them to drop):- Refer my previous post

SQL> Alter table set unused column
SQL>Alter table drop unused columns
Before SQL Statement sets the columns and the next statement drops the columns, but make sure about the constraints on those columns – well why don’t we use cascade option to do this.
SQL>Alter table drop unused column/columns cascade constraints
And one more important thing must me noted - when you are doing these things, the table is not available to the users.

To avoid this, use continue option

SQL>Alter table drop unused column/columns continue

***And one more thing is to note that when you drop the columns the table will be marked as invalid in general. Because it may have reference to other objects like stored procedures, triggers etc. So, it marks as invalid.

So, if use the CONTINUE option for the drop columns, (even during the dropping before completion, if instance crashes, the table will be marked as invalid in general. But when you use the continue option, u will be get rid of this problem and marked as valid) then the table will be marked as valid upon completion.

You can try this to monitor index usage – may be it will help you
SQL>Alter index index_name monitoring usage;

And to stop this – why to stop – well it causes performance degradation
SQL>Alter index index_name nomonitoring usage;

The check with the view
SQL>Select * from v$object_usage

Well its not an end – I need to check all the Index usage, the unused columns etc etc lots of stuff.

Oracle 9.2 on Microsoft Windows 2003 (32 Bit OS)

Internal Error ORA-600[QXIDMACLOB]

Internal Error ORA-600[QXIDMACLOB]

Export/Import Errors:

We have automated script running daily to do export database for the development server, but to my strange I find Internal Error ORA-600[QXIDMACLOB].

Well this case is repeating from couple of days and I could not able to identify this but latter followed up to metal ink where I could find the correct solution for this


Cause
The error is related to Bug:1805146 and is caused by a data dictionary inconsistency left by the ALTER TABLE SET UNUSED statement.This statement mark/delete entries in the col$ table related to that object but, miss or fail to mark/delete rows in some tables called coltype$, lob$ and partlog$.Causing the inconsistency.

Solution
Use the following workaround:After drop a column, this is marked as UNUSED.In order to avoid this error, customer must drop al unused columns executing the following command:ALTER TABLE DROP UNUSED COLUMNS;If the table is really big, this statement can potentially use a lot of UNDO space.In order to avoid UNDO space errors, customer must use the following statement:ALTER TABLE DROP UNUSED COLUMNS checkpoint 100;Checkpointing cuts down the amount of undo logs accumulated during the drop column operation to avoid running out of rollback segment space.If you do not specify a number (integer) , Oracle sets the default of 512.

*** I tried working on this and could able to solve the problem.

Statspack Installation - Steps

Detailed Steps to Configure Statspack!

Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

Space Requirements for Statspack!

Approximately 64MB is required to install Statspack.
If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
*** If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.


Start SQL*Plus.

Connect as a user with SYSDBA privilege.

For example: SQL> CONNECT / AS SYSDBA

Run the SPCREATE.SQL script.

On UNIX platforms, enter the following:

SQL>@?/rdbms/admin/spcreate
On Windows platforms, enter the following:

SQL>@%ORACLE_HOME%\rdbms\admin\spcreate

Once you run this script it will prompt you enter password for perfstat user.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.

*** Once I learn using Statspack I get back with the analysis report.

PERSONALITY TRAITS OF A DBA

Many times managers concentrate on technical qualities and overlook personality. Virtually every category shown above means a DBA will have to interface with other personnel, vendors, users, developers or managers.

This indicates the a DBA should exhibit the following traits:

Self Confidence
Curiosity
Tenacity
Tactful
Self-starter
Detail oriented