Ask Anantha

I am deeply privileged to have contacted by  Packt Publishing  for reviewing their new book intended to teach recipes for securing Oracle 11g database against a typical hacker. The book is authored by Adrian Neagu. The authors profile is available in the publishers website . A sample chapter is available for free reading in this link  or for download  as PDF file. You may buy either ebook (all formats such as Kindle, PDF, epub are available) or print version which includes ebook if you buy online from this website . The book is meant for mid level DBAs who has basic understanding of security concepts. Its not at all an entry level DBAs book.

From Technologies to Solutions: Oracle 10g/11g Data and Database Management Utilities by Hector R. Madrid PACKT Publishing The book is well within its perimeters. It discusses in detail nearly all database management utilities that an administrator uses in his day-to-day activities. The book will prove to be helpful for DBAs in a junior role as well as for those who are preparing for database administration certification examinations. The language used in this book helps the reader understand the complex tools offered by Oracle even though he may not be aware of all the tools discussed. The authenticity of the topics discussed and extensive examples/screen shots used are a real added advantage in understanding the utilities better. This book can also prove to be a good reference material for those involved in routine tasks not worrying about what all Oracle offers. It deals with normal DBA tools like Data Pump, External tables, SQL Loader, Recovery Manager, Oracle Scheduler,...

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue. For example if you want to query all records of employees for a particular department you will use: SELECT * FROM employees WHERE dept = 10; To export these rows into a file using exp utility you will follow the below syntax: exp scott/tiger TABLES=employees QUERY=\"WHERE dept=10\" Use \ for providing character or special characters like less than or greater than symbol inside the string. Also for operating system keywords you need to place \ as escape character. For example: exp scott/tiger TABLES=employees QUERY=\"WHERE name=\ANANTHA\' and sal \ You can also use ROWID for exporting, for example: exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAA...

How to Pin objects to shared Pool? Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error. You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database. You need to pin objects when any of these happen: 1. If you have encountered the ORA-04031 already and need to resolve it or 2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database. Pinning a package to shared pool 1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL.  Here is an example:    pk1 is a package with a variable called dummy.  Assigning dummy to a value and then executing the package will load it into the shared pool:  Example:-                                                                    begin  ...

How to solve this error? 1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it. 2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file. event = "4031 trace name errorstack level 2" or with 9i and higher and spfiles you can issue alter system set events='4031 trace name errorstack level 2'; What to look for in the trace? The trace contains a dump of state objects, when the error occurs. Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it. How to Pin objects to shared Pool? Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-0403...

*** SOLVED *** We have a linux server in our office, and yesterday the machine got restarted (power failure). We were able to bring up our Oracle database server afterwards but there was some problems with temporary tablespace. We were running a procedure when we encountered this error: ORA-25153: Temporary Tablespace is Empty Once querying dba_temp_files view, there were no temp files being listed. Though the temporary file was residing in the oradata folder and the dba_tablespaces where the tablespace was present.  The Solution: As it was a temporary tablespace, I could add a tempfile to the same and made the procedure working: ALTER TABLESPACE temp ADD tempfile '/fullpath.dbf' SIZE 50M; The error ORA 25153 got solved. NOTE: Do not give the same name as the existing file in oradata folder. Let the file REST IN PEACE, give a new name. For example if you have temp01.dbf existing create with temp02.dbf.

Check the entry in tnsnames.ora file: EXTPROC_CONNECTION_DATA =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))     )     (CONNECT_DATA =       (SID = PLSExtProc)       (PRESENTATION = RO)     )   ) In this article we will be discussing about the PRESENTATION clause in the entry. Little history The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database). The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc. Back to EXTPROC_CONNECTION_DATA But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared librar...

Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is , or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself.  Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose.  Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value.  Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user...

During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this On Table Level using LOCK TABLE On Database Level using RESTRICTED SESSION Exclusive Table Locks (X) An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows: $ sqlplus scott/tiger SQL> GRANT ALL ON emp TO PUBLIC; SQL> LOCK TABLE emo IN EXCLUSIVE MODE; $ sqlplus test/test SQL> SELECT * FROM scott.emp; (This works) SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...) Permitted Operations Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table. Prohibited Operations An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of loc...

Are you running out of space in the file system where database files (data files) are installed? Try out this tip in order to move some of your data files from one drive to another and update the datafile location in your database. The operating system: Oracle Enterprise Linux The Database: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 In our environment we have installed our database's files (data files) in /oracle/oradata/mydb location. The drive is getting up to 99% of utilization. Now we will move the system01.dbf from the above mentioned location to a new location in /oracle/hdb1/oradata/mydb location. /oracle/hdb1 is mounted from /dev/hdb1. The drive is nearly empty, that's why I chose it. Now for the real moving part, we will perform the following steps: Login to SQL* Plus and shutdown the database Logout from SQL* Plus and move the files from the source directory to destination directory. Login to SQL* Plus as /nolo...