Oracle Tips

Database Backup Strategy
Tom, Because of a situation with one of our production databases we are in the midst of rethinking our entire database backup strategy. It seems that regular, routine test recovery of a backup from tape for all production databases is the only ...

ORA-00001: unique constraint (GIGAGAME.PLAYERINFO_IDX2) violated
Hi Tom I have problem need to help My Company RD TESTING FIND THE PROBLEM ORA-00001: unique constraint (GIGAGAME.PLAYERINFO_IDX2) violated HOW CAN I RESOLVE PROBLEM Tks schema ??? DROP TABLE PlayerInfo; CREATE TABLE PlayerInfo ( CID...

Restoring Stats and Execution Plans
Hey Tom, We recently had a query (using bind variables) whose plan flipped after our nightly stats gathering process due to bind variable peeking. We were hoping that by restoring stats to the previous day (on all the tables included in the query...

oracle is doing full table scans on a table with raw column and the column is primary key
Hi Tom, Thanks for your continuous support. We have a table with blob data type as one column. We are using secure file option as we are using 11g. We kept the blob column in a seperate tablespace. In the main table we have a column called...

Spfile Question
I m a beginner in field of oracle . Consider a spfile , now if i make changes to the parameters in this during the running instance (using alter system set parameter....), will this change be applied in current running instance or will it be applie...

Replace several comma by one comma
Hi , I have string like the following. 1.,Accenture, , ,EDS,Sun,Training Partner 2.United States,,,, 3.Canada, I want to have the string like the following as a result of the query. 1. Accenture,EDS,Sun,Training Partner 2. United States...

Question on CONCAT
Hi Tom, Good Morning... In my Procedure. <code>CONCAT(<SQL_QUERY>,<CASE_STATEMENT>)</code> is not working but <code><SQL_QUER>||<CASE_STATEMENT></code> is working Why??

How to tune access of "fat" tables
Hello Tom, I'm working at a client's site who has a data warehouse with partitioned fact tables with about 400 columns. Performance is not too good and I am trying to optimize the system a bit. The client told me that most of the columns are quer...

A tough status problem needs help
Tom, I believe I did NOT make this thru last time(my computer crashed last time right after submit this question), and I still did NOT receive your response, so I give it another try, please ignore this if you already received this very same question...

Trying to select rows which got certain attributes
Hi I have the following situation. My table got the following data SR_WID activity_type activity_subtype 1 Maintenance Break fix 1 Install startup 2 Install startup 3 Install startup 3...

SQL*Plus IN/LIKE Capability
<code> Tom, In sql*plus we can do an IN... select * from x where animals IN ('DOG', 'CAT', 'RAT') / Or we can do a LIKE... select * from x where animals LIKE ('%OG') But if we want BOTH we have to do this... select * from x wh...

Reference Information Management
Dear Tom, Many times you give link references in the answers. How do you manage the reference/metadata information? e.g. Do you categorize previously answered questions around domain (say analytics) then Oracle key-words/functions and their uti...

security of full export or user export
Tom - a full export is being provided to a new vendor so they may "do something" with it. A concern has been raised that the passwords for all the important schemas will be available in the export - and therefore compromise security of the original ...

multi-pass sort
Dear tom, I have a question, how does multi-pass sort work, with reference to temporary tablespace. the following sizes are included to make the example clear. Suppose the sort_area_size is 1 mb and i have a table t of 1gb. I give the query as ...

RMAN Catalog vs. Control File and Misc RMAN
Tom, What are pros/cons of using an RMAN Catalog vs. Controlfile Repository (for 10-30+ databases)? Can you run reports on RMAN Catalog to show success/failure of all backups? If you have RMAN backup sets for a given database, but you lose y...

Logic behind the DENSE_RANK().
<code>Hi Tom, EVEN THOUGH BOTH THE QUERIES LOOK'S IDENTICAL WHY THE 2nd QUERY FUNCTION's DIFFERENT SELECT DEPTNO,EMPNO, DENSE_RANK () OVER(PARTITION BY DEPTNO ORDER BY EMPNO ) RNK FROM EMP DEPTNO EMPNO RNK 10 7782 1 10 7839 2 10 7...

Is there a guideline to shoeveloper privileges and roles effectively?w how to manage d
Is there a guideline to show how to manage developer privileges and roles effectively?

Index ignored when selecting multiple columns
Hopefully this is an easy one. I would like to know why an index is ignored when the SELECT clause contains multiple columns. It was my understanding that the index points to the position of the row so that whether you select one column or ten should...

Bind Peeking Mechanism in 11g
Hi Tom, I have a question concerning bind peeking in 11g. I read that 11g uses bind-aware cursor matching, but all examples I found are based on a tables with a skewed columnn and the query selects a single value (eg in Performance Tuning Guide, 11....

"unhandled exceptions" a problem?
Hello I have a DBA that is claiming that by allowing a PL/SQL procedure to allow an unhandled exception (i.e. return the exception to the host program) somehow causes a problem. He is claiming that doing this "pushes the error to the database engi...