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 \<1600\" The default value for QUERY parameter is NULL. Note: If there are multiple tables in TABLES parameter, this QUERY will be applied to all those tables.
You can also use ROWID for exporting, for example:
exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAAgAAB'\" file=test.dmp log=test.log
In Windows it is not required to put escape character for single quote character wheras you need to put escape character if you are using Linux or its variants.
exp scott/tiger@slspnc1 tables=emp query=\"where ROWID=\'AAAMgzAAEAAAAAgAAB\'\" file=test.dmp log=test.log
Restrictions When Using the QUERY Parameter:
The QUERY parameter cannot be specified for full, user, or tablespace-mode exports.
The QUERY parameter must be applicable to all specified tables.
The QUERY parameter cannot be specified in a direct path Export (DIRECT=y)
The QUERY parameter cannot be specified for tables with inner nested tables.
You cannot determine from the contents of the export file whether the data is the result of a QUERY export.
Showing posts with label DB Administration. Show all posts
Showing posts with label DB Administration. Show all posts
How to Pin objects to shared Pool?
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 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
pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */
end; /* EXECUTING THE PACKAGE. */
2.Then you must pin the package. Here is an example:
execute dbms_shared_pool.keep(owner.pk1);
Pinning stored procedure/function to shared pool
You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag. Refer to dbmspool.sql for more documentation.
Here is an example:
execute dbms_shared_pool.keep(owner.trigger, 'R');
What objects should I pin?
You can check the x$ksmlru fixed table. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.
Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis.
describe x$ksmlru
Table or View x$ksmlru
Name Null? Type
------------------------------- -------- -------------- ADDR RAW(4)
INDX NUMBER
KSMLRCOM VARCHAR2(20)
KSMLRSIZ NUMBER
KSMLRNUM NUMBER
KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated)
We do not need the other columns.
Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:
select * from x$ksmlru where ksmlrsiz > 5000;
In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.
SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database.
ORA-04031: shared memory error
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.
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-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.
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-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
pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */
end; /* EXECUTING THE PACKAGE. */
2.Then you must pin the package. Here is an example:
execute dbms_shared_pool.keep(owner.pk1);
Pinning stored procedure/function to shared pool
You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag. Refer to dbmspool.sql for more documentation.
Here is an example:
execute dbms_shared_pool.keep(owner.trigger, 'R');
What objects should I pin?
You can check the x$ksmlru fixed table. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.
Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis.
describe x$ksmlru
Table or View x$ksmlru
Name Null? Type
------------------------------- -------- -------------- ADDR RAW(4)
INDX NUMBER
KSMLRCOM VARCHAR2(20)
KSMLRSIZ NUMBER
KSMLRNUM NUMBER
KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated)
We do not need the other columns.
Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:
select * from x$ksmlru where ksmlrsiz > 5000;
In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.
SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database.
ORA-25153-Temporary Tablespace is Empty-Error solved
*** SOLVED ***
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.
What is PRESENTATION=RO in tnsnames.ora file
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 libraries (DLLs).
PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.
The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO
TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.
Information obtained from: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/architec.htm#1007271
JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.
It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution
RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.
The other layers will be discussed later.
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 libraries (DLLs).
PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.
The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO
TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.
Information obtained from: http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/architec.htm#1007271
JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.
It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution
RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.
The other layers will be discussed later.
Switch to another Oracle User and back
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" command to put the original password back.
The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords. This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back. Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.
spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off
spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;
spool off
Generated Script setpw.sql
alter user SYS identified by whatever;
alter user SYSTEM identified by whatever;
alter user DES identified by whatever;
alter user ELAN identified by whatever;
Generated Script resetpw.sql
alter user SYS identified by values '5638228DAF52805F';
alter user SYSTEM identified by values 'D4DF7931AB130E37';
alter user DES identified by values 'ABFEC5AC2274E54D';
alter user ELAN identified by values 'B53CE8493EC6FB92';
Source & Credits: www.akadia.com
How to switch Oracle in Single User-Multi Session Mode
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 lock on the table.
Lock Duration
Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
Oracle in Single User Mode
You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.
RESTRICTED SESSION - Specifies whether logon to Oracle is restricted
ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.
$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;
As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:
sqlplus scott/tiger
ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege
After the migration to not to forget, to disable the RESTRICTED SESSION system privilege
svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Source: www dot akadia dot com
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 lock on the table.
Lock Duration
Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
Oracle in Single User Mode
You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.
RESTRICTED SESSION - Specifies whether logon to Oracle is restricted
ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.
$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;
As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:
sqlplus scott/tiger
ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege
After the migration to not to forget, to disable the RESTRICTED SESSION system privilege
svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Source: www dot akadia dot com
Running out of space? Want to move Oracle Datafiles?
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 /nolog
Connect as SYSDBA to the database to an idle instance.
Issue startup mount command to bring up the database.
Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.
Now finally open the database.
The above mentioned are the brief steps by which we can achieve our goal. Now to demonstrate see the following commands as well so that it serves as a live example:
Step 1
oracle@astrn10: sqlplus /nolog
SQL> conn /as sysdba
SQL> shutdown immediate;
Troubleshooting: If you are not able to get the SQL prompt, check your ORACLE_HOME, and ORACLE_SID.
Step 2
SQL> exit;
oracle@astrn10: mv /oracle/oradata/mydb/system01.dbf /oracle/hdb1/oradata/mydb/
Now check whether the file have been moved or not by issuing the following command:
oracle@astrn10: ls /oracle/hdb1/oradata/mydb/
total 429924352
-rw-r----- 1 oracle oinstall 429924352 Feb 12 11:00 system01.dbf
Now we are ready for the next step.
Step 3
oracle@astrn10: sqlplus /nolog
SQL>
Step 4
SQL> conn /as sysdba
Connected to idle instance.
Step 5
SQL> startup mount;
Database mounted.
Step 6
SQL> alter database rename file '/oracle/oradata/mydb/system01.dbf' to '/oracle/hdb1/oradata/mydb/system01.dbf';
Database altered.
Step 7
SQL> alter database open;
Database opened.
That's all. We are done with our agenda for moving data files from one drive to another. If this where Windows/any other operating system, then copy files as per your operating system commands/interface in Step 2.
In order to copy more files (in this demonstration we have moved only one file), then repeat Step #2 and Step # 6 for each file.
#End of tip