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.

QUERY parameter in Export Utility

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.

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:

1. Login to SQL* Plus and shutdown the database

2. Logout from SQL* Plus and move the files from the source directory to destination directory.

3. Login to SQL* Plus as /nolog

4. Connect as SYSDBA to the database to an idle instance.

5. Issue startup mount command to bring up the database.

6. Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.

7. 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.

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';