Oracle notes

Regulaer experssions = Tips

2006-12-17T12:44:00.000+02:00

You can do wonderful stuff with regular expressions , here are some useful items : select regexp_substr('ORA-06512: at "ECTEL_ADMIN.ERR", line 20 ORA-06512: at "ECTEL_ADMIN.ERR", line 89 ORA-06512: at "FRAUD_OWNER.RULES_MGR", line 1932 ORA-06512: at "FRAUD_OWNER.ALERT_MGR", line 611 ','[^'||chr(10)||']+\Z',1,1) from dual;This get the last line , it look for all lines string with chr(10) - which

Look in a list of values with Like

2006-10-23T14:48:00.000+02:00

Let say , you've a table of values , and you want to a list of records which answer those records and like (support of wildcard) .The sql to do it is like the follow : select dt.tablespace_name from dba_tablespaces dt where not exists (select 1 from br_content bc where dt.tablespace_name like bc.component_name and job_id = 1 )Note the order of the tablespace_name and component_name.

INSERT with select example

2006-09-06T15:16:00.000+03:00

SQL> insert into giy values((select 900 from dual),(select 902 from actions)); 1 row inserted -- Must be single row query SQL> insert into giy values((select 900 from dual),(select 902 from users));insert into giy values((select 900 from dual),(select 902 from users)) ORA-01427: single-row subquery returns more than one row SQL> commit; Commit complete

How to change database charsets

2006-08-07T09:05:00.000+03:00

In order to the change database charset do the following:Please note this procdure may corrupt database which is not supported by the new charset. Login as sys to the oracle :SHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER SYSTEM ENABLE RESTRICTED SESSION;ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;ALTER SYSTEM SET AQ_TM_PROCESSES=0;ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET INTERNAL_USE ;SHUTDOWN

How to create new file system in sun solaries

2006-07-20T15:32:00.000+03:00

STEP 1 -unix>formatFORMAT MENU: disk - select a disk type - select (define) a disk type partition - select (define) a partition table current - describe the current disk format - format and analyze the disk repair - repair a defective sector label - write label to the disk analyze - surface analysis

Waiting on enq:JI

2006-06-22T05:45:00.000+03:00

I got an installation which got the following problem:the user has done the following :update all_cases set desc='aaa' where id=2;commit <--- this statment got hang for about 5 mintunes.The reason for that is because we have a materialize view on commit on the this table.This materialized view is doing a full refresh due to some wrong declaration. This cause the wait above.

How to build databases on \\netstore3\db

2006-06-18T13:36:00.000+03:00

We’ve storage to keep all the database files. The only account which is permitted to access this account is ECTEL\dbaIt order for oracle database to access the files you need to do the following :• Login with local administrator to the db machine – add user ectel\dba to administrator group (need to be done only once )• Login with this user(ectel\dba) to machine which the database software

Using DBCA - Part I

2006-05-25T08:29:00.000+03:00

DBCA - Database Configuration AssistantEveryone know about the GUI interface , here i will try to show how to use the command line interface.Deleting databasedbca -progressOnly -deleteDatabase -sourceDB -sysDBAPassword This will start the deletion of the database , It will ask for confirmation.

Switching To Archivelog in 10g

2006-05-16T09:35:00.000+03:00

Show parameter parallel_max_servers (write down old value)alter system set parallel_max_servers=0 scope=both;shutdown immediatestartup restrict mountAlter database archicvelog ; (no need to use archive log start)alter system set parallel_max_servers= scope=both;shutdown immediatestartup

2006-04-02T09:57:00.000+03:00

Application securiny - Part ISecure the oracle password :You can use wallet to stone encrypted passwords without any application code changes.Here are the steps to implement wallet connections:1. Create the wallet directory and objectsmkstore -wrl -create2. Insert a new database alias to be usedmkstore -wrl -createCredential 3. Configure the sqlnet.ora (oracle client ) to use the wallet

2006-03-07T17:11:00.000+02:00

ORACLE sort comp : NLS_SORT nls_comp v$nls_parameters binary The sort and comp (like between) migth work differently. -- Two examples: -- First example: DROP TABLE TEST; CREATE TABLE TEST ( entity VARCHAR2(4)); INSERT INTO TEST VALUES('a'); INSERT INTO TEST VALUES('0'); INSERT INTO TEST VALUES('A'); INSERT INTO TEST VALUES('1'); COMMIT; -- sort and comp are the same: ALTER SESSION SET

2006-03-02T13:49:00.000+02:00

FAQ - in FraudView 8.0How to roll partitions --> Login as ectel_admin --> exec dbms_scheduler.run_job('ECT_DBM_PARTITION_CLEANUP_ J')

2006-02-02T08:49:00.000+02:00

Analtic functions one of my friend ask how to get the lower value of partition :SELECT TABLE_OWNER,TABLE_NAME,PARTITION_POSITION,PARTITION_NAME, LAG(PARTITION_NAME,1) OVER (ORDER BY PARTITION_NAME) BEFORE_PARTITION_NANE, LAG(PARTITION_POSITION,1) OVER (ORDER BY PARTITION_POSITION) BEFORE_PARTITION FROM all_tab_partitions WHERE table_name='UR_PROBE' AND table_owner='DBWR'

2006-01-26T08:31:00.000+02:00

Working with XMLSUpdating XMLupdate im_config set current_data = updateXML(current_data, '/a/*[@x=2]text()', 'two') where ID='TEST';(using clob) declare newURL varchar2(300):='http://ServerName/businessobjects/enterprise115/desktoplaunch/InfoView/logon/logon.do';beginupdate ui_settings a set a.xml = updateXML(xmltype(a.xml),'/MENUS/BUTTON/MENU[@TITLE=''Reports'']/@URL',newURL).getclobval(

2006-01-25T14:45:00.000+02:00

Enable flashback database One Time Setting shutdown immediatestartup mountalter database archivelog;alter system set db_recovery_File_dest_Size=5G scope=both;alter system set db_recovery_File_dest='E:\oracle\recovery' scope=both;alter database flashback on;alter database open;Before applying changes/tests scripts :CREATE RESTORE POINT ZeroBaseData GUARANTEE FLASHBACK DATABASE;Do the changes

2006-01-23T20:35:00.000+02:00

Insert all with sequencecreate table yy ( a number, b number) ; create table yy2 ( a number, b number) ; insert all into yy(a) values (PS_ALERT_NUMBER_SEQ.NEXTVAL) into yy2(a,b) values(PS_ALERT_NUMBER_SEQ.NEXTVAL,aaa) select 1 aaa from dual

2006-01-23T20:31:00.000+02:00

Example of using interval select count(*) from user_scheduler_jobs where upper(JOB_action) = 'PS4_MAINTENANCE.BEGIN_();' and ENABLED = 'TRUE'and (sysdate - LAST_START_DATE) <> Interval '3' hour;this query return jobs the doesn't run for every 3 hours

2006-01-16T10:40:00.000+02:00

How to execute Oracle stored-procedure with XMLTYPE output parameterpublic static string GetHotlistsByAlertID(long alertID){Log.WriteStartFunction("GUIData", "Hotlists", MethodInfo.GetCurrentMethod()); OracleDataReader dataReader = null; string retValue = String.Empty; OracleParameter[] commandParameters = new OracleParameter[] { new OracleParameter("

2006-01-15T15:41:00.000+02:00

ORACLE 10g merge example with delete create table yy1 ( a number, b number) ; create table yy2 ( a number, b number) ; insert into yy1 values (1,1); insert into yy1 values (2,2); insert into yy2 values (2,4); insert into yy2 values (3,4); commit; SQL> select * from yy1 ; A B ---------- ---------- 1 1 2 2 SQL> select * from

2006-01-09T08:25:00.000+02:00

Problem creating database using DBCA"[Thread-9] [19:10:30:151] [CloneRmanRestoreStep.executeImpl:320] starting with pfile=D:\oracle\product\10.2.0\admin\trp\pfile\init.ora[Thread-7] [19:10:35:198] [BasicStep.handleNonIgnorableError:430] oracle.sysman.assistants.util.UIMessageHandler@169a50b:messageHandler[Thread-7] [19:10:35:198] [BasicStep.handleNonIgnorableError:431] ORA-24324: service

2006-01-05T10:46:00.000+02:00

Constaraints typesc - checkp - primary keyu - uniquer - references (fkey)v - view with check optiono - read only on a view

2006-01-05T08:27:00.000+02:00

Example of function index in oracle CREATE UNIQUE INDEX FRAUD_OWNER.UNIQUE_ALERT_COLUMN ON FRAUD_OWNER.STONES(NVL2("ALERT_COLUMN","SOURCE_ID",NULL), ALERT_COLUMN)

2006-01-03T19:53:00.001+02:00

Refcursor examplesvariable in_object_name varchar2(100) ;variable refcursor1 refcursor ;variable Number_out number ;CREATE OR REPLACE procedure sys_refcursor_demo (in0 IN varchar2,out1 OUT sys_refcursor,out2 OUT number)isbeginopen out1 forselect to_char(line,'0999') || ' : ' line , textfrom user_sourcewhere name = in0order by line ;select count(*) into out2from user_sourcewhere name = in0 ;end

2006-01-03T19:53:00.000+02:00

variable in_object_name varchar2(100) ;variable refcursor1 refcursor ;variable Number_out number ; CREATE OR REPLACE procedure sys_refcursor_demo ( in0 IN varchar2, out1 OUT sys_refcursor, out2 OUT number) isbegin open out1 for select to_char(line,'0999') || ' : ' line , text from user_source where name = in0

2006-01-01T16:47:00.000+02:00

Export using queryexp ectel_admin/ectadm@jerusalem/im1 file=im_config.dmp log=a.log query=\" where ip='172.16.10.63'\" tables=im_configusing import as regular

2006-01-01T14:29:00.000+02:00

Using dynamic sql , when you don't know nothing (method 4)drop table test1 cascade constraints;create table test1 (A number,b varchar2(30));insert into test1 values (1,'aaaa');commit;set serveroutput ondeclare type aaa is table of varchar2(30) index by pls_integer; c number; vdata aaa; vcolname aaa; v_rows number;begin for i in (select rownum id,column_name from user_Tab_Columns where table_name

2005-12-20T13:42:00.000+02:00

SQLPLUS not runningif you run sqlplus and get nothing (sqlplus crashed) , check enviroments variable of PATH , that are some conflicts with sqlplus of instance client and regualer sqlplus

2005-12-14T08:56:00.000+02:00

In oracle 10g we have a lot of warning messages , uneccery to disable one of those error look at330239.1 notethe soluation:alter system set "_kgl_large_heap_warning_threshold"=10485760 scope=spfile /

2005-11-05T20:56:00.000+02:00

תצלום משפחתי מאוחד

2005-10-12T04:07:00.000+02:00

enchament to utplsl I've changes packages utoutputreporter and utplsql to support 10gr2 features:1. remove all code the handle split lines from utoutputreport.pl (we don't need it in 10g2)2. call dbms_utility.format_error_stack to get more data.

2005-09-05T17:19:00.000+03:00

Instance client pointsif you can't use easy connect (//server:port/dB) ,then use tnsnames.ora . You must set tns_admin environment varibable to point to it

2005-07-28T16:54:00.000+03:00

Using sofistciate interval function for oracle jobs :ell, it isn't really about dbms_job, but rather "a function to return a timegiven an input"...I think:casewhen to_number(to_char(sysdate,'hh24')) between 2 and 21then sysdate+10/24/60when to_char(sysdate,'DY') in('MON','TUE','WED',THU')then trunc(sysdate)+1+2/24else next_date(trunc(sysdate),'MON')+2/24endif the hours are between 2am and 9pm,

2005-06-30T18:22:00.000+03:00

--Compare packageCREATE OR REPLACE PACKAGE CompareUtils AUTHID CURRENT_USER IS FUNCTION getDDL(object_type VARCHAR2, object_name VARCHAR2, orig_schema VARCHAR2 DEFAULT NULL, comp_Schema VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; FUNCTION objectddl_fc(input_values SYS_REFCURSOR) RETURN objectddl_ty_tb PIPELINED;END CompareUtils;/CREATE OR

2005-06-27T14:17:00.000+03:00

Look at xml data as tableSELECT ROWNUM AS ID,u.Id CDR_TYPE,t.name PACKAGE_NAME,extract(value(e), 'day/@val').getNumberVal() FROM_DAY,'a' FROM_TIME,'a' TO_DAY,'a' TO_TIME,extract(value(d), 'time/@user').getNumberVal() PERIOD_CLASS_IDFROM rating_modules t JOIN md_ur_types u ON t.ur_type_id=u.ID ,table(xmlsequence( -- return serveral rows extract(xmltype(t.period_xml), '//period/time'))) d,-

2005-06-16T13:58:00.000+03:00

Using different Quote sign Instead of problems in ' in string you can change to use other char.syntax is q'x you text x'qfor exampe q'|' RUNNING SELECT T.STATE, T.LAST_START_DATE, T.LAST_RUN_DURATION, T.NEXT_RUN_DATE, T.COMMENTS FROM ALL_SCHEDULER_JOBS T WHERE T.JOB_NAME=p_ID OR (t.owner = substr(p_id, 1, instr(p_id, ' .

2005-06-07T14:09:00.000+03:00

Registering oracle10g agentIn the Remote server agent home /sysman/config directorymake a backup copy of the emd.properties file.edit the emd.properties file.Find the following and change the . to be the OMS ..For example the remote server is foo.foo.com and the OMS server is oms.foo.comREPOSITORY_URL=http://foo.foo.com:4889/em/upload/andemdWalletSrcUrl=http://foo.foo.com:4889/em/wallets/

2005-06-07T13:53:00.000+03:00

Cloning oracle homeTo clone an Oracle Home, lets say the 10g Database, simply copy the Oracle Home at the source to a new location either on the same or a different machine. Next invoke the runInstaller/setup.exe from within the copied Oracle Home in clone mode: ./runInstaller -clone ORACLE_HOME="" ORACLE_HOME_NAME=""(-clone is a new option in 10g)This feature is currently support only for

2005-05-26T17:26:00.000+03:00

Reorg database ... sometime you need to move objects in the database blog.Here is how to move common objects in the database :alter table T move tablespace xxx;alter table T move partition p1 tablespace xxx;alter index I rebuild tablespace xxx;alter table T move lob(Col) store as (tablespace xxx);

2005-05-23T12:26:00.000+03:00

How to disable recycle bin in 10g:ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;from note 265253.1

2005-05-18T01:14:00.002+03:00

database creations script dbms_metadataoracle intrudes a nice package dbms_metadata which can be use to reverse engineer existing database structure and to change it. you define what the script will contain by using dbms_metadata.set_transform_param for example : exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'CONSTRAINTS', FALSE);here are the some samples :

2005-05-18T01:14:00.001+03:00

database creations script dbms_metadataoracle intrudes a nice package dbms_metadata which can be use to reverse engineer existing database structure and to change it. you define what the script will contain by usingdbms_metadata.set_transform_param for example :exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'CONSTRAINTS', FALSE);here are the some samples :

2005-05-18T01:14:00.000+03:00

2005-05-11T10:22:00.000+03:00

Registering database in OID using command lineCreate file a.ldif using this format :dn: cn=prod,cn=OracleContext,dc=ectel,dc=comobjectclass: topobjectclass: orclNetServicecn: prodorclNetDescString: (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sun_server)(PORT =1521))) (CONNECT_DATA = (SID = ORCL)))run the command: ldapadd -D cn=orcladmin -w password -h host -v -f a.ldifTaken

dbca

2005-05-11T09:32:00.000+03:00

Hi , you can use dbca to create a image of database for installation. in 10g you have option to save also database files. the DBCA create a compress file calles Template.dbc which contain the database files compress (he compress 5.5Gb database to 80Mb file) dbca template screen

2005-05-10T04:51:00.000+03:00

two more new features in 10g begin DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'my_first_equivalence', 'select ename from emp' ,'select dname from dept', false);end; This package enable to change select to work on another table . this is a greate option to "fix" sql from 3rd party vendor (i2) that doesn't work well as we need.more information at amis

2005-05-08T21:56:00.001+03:00

difference between order and compare.until 10g compare is always binary , which can cause problem if you change nls_sort setting.In 10g you can set nls_comp to ansi which will use the operator of the nls_sort.for more information click here

2005-05-08T21:56:00.000+03:00

Oracle notes

2005-05-08T07:19:00.000+03:00

dbms_schduler (replace dbms_job in 10g)you can do a lot with this new version.for example - calling external command from oracle:just run this :begin DBMS_SCHEDULER.CREATE_JOB ( job_name =>'create_dir_test', job_type =>'executable', job_action => 'c:\winxp\system32\cmd.exe /c mkdir c:\test\test1', enabled =>true,auto_drop => true);commit;end;/

2005-05-07T23:40:00.000+03:00

hi all,this is my first attaempt if writing blog.i intened to use this blog as a centernal place for oracle knowldage and tips.