kwatog

I only know two ways of getting the Oracle DB version you are currently connected to. They are as follows:


SELECT *
FROM product_component_version



SELECT *
FROM v$version


This is quite handy if your program has SQL commands that are not applicable for earlier versions of Oracle Database.

SYNTAX

alter table
table_name
rename to
new_table_name;


Example

alter table
employee
rename to
employee_bak;


I got tangled with a problem on the datatype LONG in Oracle which is still being used in a number of applications. Siebel, is actually one of them. When I was tasked to extract some data from one of the tables in EIM using C++, I run into this problem.

I googled for answer and here’s what I got.

CREATE OR REPLACE FUNCTION LONG_TO_CHAR( in_rowid rowid,in_owner
varchar,in_table_name varchar,in_column varchar2)
RETURN varchar AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--
begin
sql_cur := 'select '||in_column||' from
'||in_owner||'.'||in_table_name||' where rowid =
'||chr(39)||in_rowid||chr(39);
dbms_output.put_line (sql_cur);
execute immediate sql_cur into text_c1;

text_c1 := substr(text_c1, 1, 5000);
RETURN TEXT_C1;
END;


It’s pretty simple but unfortunately, we can’t do this without using function.

Example use:

SELECT MI_F_LONG_TO_CHAR(ROWID, 'SIEBEL','EIM_ORDER','ORD_X_COMMENTS')
FROM SIEBEL.EIM_ORDER


credit to Oracle Mag for this entry.

In case you only have SQL*Plus as your editor, you can use the following codes to show the execution plan of your query.

First Step : create plan
EXPLAIN PLAN FOR
SELECT field1, field2
FROM table;

Second Step: show results

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


You will need the necessary privileges to execute this. You’ll also need to have the PLAN table. If not, you’ll have to create it as that’s the placeholder where execution plan is written.

This technique is important especially when you don’t have a GUI-based SQL editor like PL/SQL Developer or TOAD.

To see DB processes running on your Oracle DB, the code below can be used. Specially handy if you want to check which part of your package or stored procedure is taking time to execute. Indispensable for performance tuning.

SELECT sess.sid, sess.serial#, sess.sql_child_number, sess.sql_exec_id,
sess.process, sess.status, sess.username, sess.osuser, sess.program,
sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
--AND sess.osuser = 'yourwindowslogin';


Equivalent in SQL Server/Sybase

The equivalent command in Sybase or SQL Server is SP_WHO. However, the Oracle version provides more information.

For now, just this. I’ll provide additional details later. For additional fields, try to check the schema of v$session.