Use the following code in order to search in a LONG field:SELECT * FROM sys.trigger$ /* table, not view */ WHERE sys.dbms_metadata_util.long2varchar( 4000 ,'SYS.TRIGGER$','ACTION#', ROWID) LIKE '%rvtrig_upd%';Note, that for the…
Use the following code to create a simple user: CREATE USER nista IDENTIFIED BY kookoo;GRANT "CONNECT" TO nista;--optional:ALTER USER nista DEFAULT TABLESPACE "USERS" QUOTA UNLIMITED ON "USERS";GRANT "SSE_ROLE" TO nista;--for…
With the following command you can get the OS version: SELECT DBMS_UTILITY.PORT_STRING FROM DUAL; e.g. IA64/HPUX 64.0.9.0.1
Oracle Built-In Data Types: Datatype Description VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is…
To create an heterogenous database link (to another database):1) set an odbc connection named testdb and in advanced put username/password (admin/admin)2) put inittestdb.ora in D:\oracle\product\10.2.0\db_1\hs\admin (* see below)3) put tnsnames.ora…
The statement below clears the cache, so you can run benchmarks against SQL execution: alter system flush buffer_cache
SELECT * FROM NLS_SESSION_PARAMETERSALTER SESSION SET NLS_LANGUAGE=Greek;alter session set nls_lang=GREEK_GREECE.EL8MSWIN1253
How to retrieve session information: select S.LOGON_TIME ,sid, serial#, username, command, lockwait, status, osuser, sql_text, machinefrom v$session s, v$sqlarea q where s.sql_hash_value = q.hash_valueand s.sql_address = q.addressand osuser = 'zzz'and…
How to create a pivot query: with t as ( select 1 GRP_NR, 'A' val from dual union allselect 1 ,'B' val from dual union allselect 2 ,'A' val from…