OraCsdP: Miscellanous : SQL and More

1] Unix

1.1 Mailx send CC

I had a very hard time sending CC copies with Unix mailx.

I needed to change Oracle login account for around 600 users ; hence, I wrote a KSH script that took care of anything ( application that are impacted by this change and needs to modify a Users table / target username already created for another application, and so on … ). Once done, users were notified with ‘mailx’ as follow.

$ cat ${warn_i2u_gen} | mailx -s "${msg_tit}" "${usr_mail}"

The file contains explanation , new user / password , and is sent to enduser and application managers.

$ cat chng_i2u_<example>.txt

~c < first @ >,< others cc @ >

Bonjour,

…

1.2 KSH Generate a random password

This simple function returns a random character string ; the only argument is the string length

( adapted from http://dfrench.hypermart.net/fancyIndex/Tools/Scripts/Korn/Functions/mkrandpwd_k93.html )

# func, arg = # car

function gen_psw_rand {

#set -A CHARS a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9 \; \: \. \~ \! \@ \# \$ \% \^ \& \* - + = \?

set -A CHARS a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9

typeset CNUM="${#CHARS[*]}"

typeset MAXLEN=$1

RANDSTR="" ; POSCNT=0

while (( POSCNT < MAXLEN )) ; do

RANDSTR="${RANDSTR}${CHARS[${RANDOM}%${CNUM}]}"

(( POSCNT += 1 ))

done

print ${RANDSTR}

}

Call

if [[ ${ENV} = 'PRO' ]] ; then

PSW=$( gen_psw_rand 5 )

else

PSW=${USc}

New version : the first password character is Alpha Not Numeric for Oracle convenience

# func, arg = # car

# first password character is Alpha Not Numeric for Oracle convenience

function gen_psw_rand {

#set -A CHARS a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9 \; \: \. \~ \! \@ \# \$ \% \^

\& \* - + = \?

set -A CHARS a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9

set -A CHAR1 a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

typeset CNUM="${#CHARS[*]}" ; typeset CNU1="${#CHAR1[*]}"

typeset MAXLEN=$1

RANDSTR="${RANDSTR}${CHAR1[${RANDOM}%${CNU1}]}" ; POSCNT=1

while (( POSCNT < MAXLEN )) ; do

RANDSTR="${RANDSTR}${CHARS[${RANDOM}%${CNUM}]}"

(( POSCNT += 1 ))

done

print ${RANDSTR}

}

1.3 The right options to mount NFS on Netapp for Oracle

filer4-1:/vol/vol_oracle_utils_conf_41/qt_oracle_utils_conf_41 on /PATH type nfs (rw,bg,hard,rsize=32768,wsize=32768,nfsvers=3,nointr,timeo=600,proto=tcp,addr=192.168.11.223)

1.4 Dump File error

DUMP FILE SIZE IS LIMITED TO 5242880 BYTES

SQL> sho parameter dump

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size string 10240

SQL> -- 256 M = 262144 KB -> 524288 1/2 KB blocks

SQL> alter system set max_dump_file_size = 524288 ;

System altered.

2] Oracle Directories

2.1 How I test if a Directory is valid

When facing a ORA-29280: invalid directory path error

SQL> create or replace directory APP_DIR as '/work/hdmdev/oratmp' ;

Directory created.

SQL> grant read, write on directory APP_DIR to APP ;

Grant succeeded.

SQL> conn hdm/hdm

Connected.

declare

VI_Hdl_Fic UTL_FILE.File_Type;

begin

VI_Hdl_Fic := UTL_FILE.FOpen('APP_DIR','test.txt','W',2048);

end;

PL/SQL procedure successfully completed.

/work/hdmdev/oratmp > ls -l test.txt

-rw-r--r-- 1 oracle devun 0 Sep 8 11:53 test.txt

2.2 How to check Permissions on Directories

select directory_name , grantee , privilege from dba_tab_privs a , all_directories b where table_name = directory_name order by 1,2

3A] SQL

3A.1 Working with Dates

3A.1.1 Count Business Days excluding weekends and holidays

May 2012 : Holidays = 01 08 17 18 28 -> 18 business days

$ cat /tmp/count_business_days.sql

select count(*)*24 "business_days" from (

select rownum rnum from all_objects where rownum <= to_date('&1') - to_date('&2')+1

)

where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT' , 'SUN' )

and trunc(to_date('&2')+rnum-1) not in ('01/05/2012','08/05/2012','17/05/2012','18/05/2012','28/05/2012')

$ sqlplus scott/tiger @/tmp/count_business_days.sql 31/05/2012 01/05/2012

old 2: select rownum rnum from all_objects where rownum <= to_date('&1') - to_date('&2')+1

new 2: select rownum rnum from all_objects where rownum <= to_date('31/05/2012') - to_date('01/05/2012')+1

old 4: where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT' , 'SUN' )

new 4: where to_char( to_date('01/05/2012')+rnum-1, 'DY' ) not in ( 'SAT' , 'SUN' )

old 5: and trunc(to_date('&2')+rnum-1) not in ('01/05/2012','08/05/2012','17/05/2012','18/05/2012','28/05/2012')

new 5: and trunc(to_date('01/05/2012')+rnum-1) not in ('01/05/2012','08/05/2012','17/05/2012','18/05/2012','28/05/2012')

business_days

--------------

Note :

-- add one day

select DATFIN_AFF , to_char((trunc(DATFIN_AFF)+1),

'YYYY-MM-DD')||'T'||to_char(IUS_DATFIN_AFF,'HH24:MI:SS') from t1

3A.1.2 Compare dates to Hour only

Extract the Hour from the date to get the nightly runs only : between 7PM to 7AM :

select username , login_date , module , service_name , machine , osuser from (

select username , login_date , module , service_name , machine , osuser ,

ROW_NUMBER() OVER(PARTITION BY username , module , service_name , machine , osuser order by login_date desc ) rn

from app.users_mon where module != 'sqlplus@srv-13 (TNS V1-V3)' and service_name = 'SYS$USERS' and username != 'SYSTEM'

-- night batch period 19H - 07H

and extract(hour from cast(to_char(login_date, 'DD-MON-YYYY HH24:MI:SS') as timestamp)) not between '07' and '19'

) where rn=1

order by login_date desc

3A.2 Regular Expressions

Get Oracle Version Major Number

select substr(REGEXP_REPLACE(banner, '([a-zA-Z ])', ''),1,2) from v$version where banner like '%Database%' ;

select first 4 digits starting with "20" ( year )

select regexp_replace('ex-app 2008 ra=ac 2045 ans', '[^2]*20([[:digit:]]{2}).*$', '20\1') from dual;

ð 2008

Extract the string between 2 « : » with the regular Expression

create table t ( a varchar2(32) )

select a from t

-- abc:la chaine:

select REGEXP_REPLACE(a, '(\:[a-zA-Z ]\:)', '\1') from t

select REGEXP_SUBSTR(a, '\:.*\:') from t

-- select REGEXP_SUBSTR(a, '\:.*\:', 1, 1, 'i', 1) from t

select replace(REGEXP_SUBSTR(a, '\:.*\:'),':','') from t

-- la chaine

Count the number of occurrence of a Character in a Column

create table T_AUC ( A varchar2(32) ) ;

insert into T_AUC values ('AAABBCCDDEEAAFFAAGG') ;

commit ;

-- 11G

select A , REGEXP_COUNT( A, 'A' ) as pattern_count from T_AUC

-- 10G

select A , NVL( LENGTH( REGEXP_REPLACE( A, '[^A]', NULL ) ), 0 ) as pattern_count from T_AUC ;

drop table T_AUC ;

Find rows with '%SnnEnn%' n: 0 to 9

create table auc ( a varchar2(16));

insert into auc values ('ES43E45TK') ;

insert into auc values ('AUREL') ;

commit ;

SELECT * FROM AUC WHERE REGEXP_LIKE(a, '*S[0-9][0-9]E[0-9][0-9]*');

3A.3 Minus removes duplicate rows

drop table t1 ;

drop table t2 ;

create table t1 ( a int ) ;

alter table t1 add ( b varchar2(10) not null ) ;

create table t2 ( a int , b varchar2(10) ) ;

insert into t1 values ('10','dix');

insert into t1 values ('20','vingt');

insert into t1 values ('30','trente' );

insert into t2 values ('10','dix2');

insert into t2 values ('20','vingt2');

insert into t1 values ('10','dix');

commit ;

Commit complete.

SQL> select * from t1 ;

A B

---------- ----------

10 dix

20 vingt

30 trente

10 dix

SQL> select * from t2 ;

A B

---------- ----------

10 dix2

20 vingt2

SQL> create table t1b as select * from t1 where A='20' ;

Table created.

SQL> select * from t1b ;

A B

---------- ----------

20 vingt

create table t1c as select * from t1 minus select * from t1b ;

Table created.

SQL> select * from t1c ;

A B

---------- ----------

10 dix

30 trente

3A.4 Merge gives ORA-30926, then seek for Duplicate rows

Original Merge

MERGE INTO "<SCHEMA>".AM_EMPLOYEE USING <SCHEMA>.V_<SCHEMA>_TABLE_A ON ( "<SCHEMA>".AM_EMPLOYEE.LOGIN = to_char(<SCHEMA>.V_<SCHEMA>_TABLE_A.COL_01) )

WHEN MATCHED THEN

UPDATE SET

"<SCHEMA>".AM_EMPLOYEE.E_NOM_INSTRUCTEUR = <SCHEMA>.V_<SCHEMA>_TABLE_A.VAA_NOM_PRE_ACTEUR,

"<SCHEMA>".AM_EMPLOYEE.E_SECTEUR_INSTRUCTEUR = <SCHEMA>.V_<SCHEMA>_TABLE_A.VAA_LOC_LIB,

…

"<SCHEMA>".AM_EMPLOYEE.E_AID_PCH_ADULTE = <SCHEMA>.V_<SCHEMA>_TABLE_A.vaa_pch,

"<SCHEMA>".AM_EMPLOYEE.E_AID_PCH_ENFANT = <SCHEMA>.V_<SCHEMA>_TABLE_A.vaa_pche

WHEN NOT MATCHED

THEN

INSERT

( "<SCHEMA>".AM_EMPLOYEE.LOGIN,

"<SCHEMA>".AM_EMPLOYEE.E_NOM_INSTRUCTEUR,

…

"<SCHEMA>".AM_EMPLOYEE.E_AID_PCH_ADULTE,

"<SCHEMA>".AM_EMPLOYEE.E_AID_PCH_ENFANT )

VALUES

( <SCHEMA>.V_<SCHEMA>_TABLE_A.COL_01,

<SCHEMA>.V_<SCHEMA>_TABLE_A.VAA_NOM_PRE_ACTEUR,

…

<SCHEMA>.V_<SCHEMA>_TABLE_A.vaa_pch,

<SCHEMA>.V_<SCHEMA>_TABLE_A.vaa_pche ) ;

MERGE INTO "<SCHEMA>".AM_EMPLOYEE USING <SCHEMA>.V_<SCHEMA>_TABLE_A ON ( "<SCHEMA>".AM_EMPLOYEE.LOGIN = to_char(<SCHEMA>.V_<SCHEMA>_TABLE_A.COL_01) )

ERROR at line 1:

ORA-30926: unable to get a stable set of rows in the source tables

Find duplicate rows

select COL_01 , count(*) from <SCHEMA>.V_<SCHEMA>_TABLE_A

group by COL_01

having count(*) > 1

3A.5 UPDATE

3.5.1 INSERT Equivalent to MERGE

Example to modify the Preference Table of Oracle / Designer : in DESIGNER9I based on DESIGNER6I Table

UPDATE DESIGNER9I.QMS_USER_OPTIONS a SET

( USER_NAME ,

"LANGUAGE" ,

MENU_CALL_METHOD ,

ENABLE_CS_IND ,

HIGHLIGHT_QI_IND ,

HIGHLIGHT_RI_IND ,

VA_QUERYABLE_ITEM ,

VA_REQUIRED_ITEM ,

VA_READONLY_ITEM ,

VA_SELECTED_RECORD,

RAISE_FIND_WINDOW,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE ) = ( select

USER_NAME ,

"LANGUAGE" , 'NF' ,

ENABLE_CS_IND ,

HIGHLIGHT_QI_IND ,

HIGHLIGHT_RI_IND ,

VA_QUERYABLE_ITEM ,

VA_REQUIRED_ITEM ,

VA_READONLY_ITEM ,

VA_SELECTED_RECORD,

RAISE_FIND_WINDOW,

CREATED_BY,

CREATION_DATE, 'SYSTEM', sysdate

from DESIGNER6I.QMS_USER_OPTIONS b where a.user_name = b.user_name )

where exists ( select '1' from DESIGNER6I.QMS_USER_OPTIONS b where a.user_name = b.user_name )

insert into DESIGNER9I.QMS_USER_OPTIONS

select

USER_NAME , LANGUAGE , 'NF' , ENABLE_CS_IND , HIGHLIGHT_QI_IND , HIGHLIGHT_RI_IND , VA_QUERYABLE_ITEM , VA_REQUIRED_ITEM , VA_READONLY_ITEM ,

VA_SELECTED_RECORD, RAISE_FIND_WINDOW, CREATED_BY, CREATION_DATE, 'SYSTEM' , sysdate

from DESIGNER6I.QMS_USER_OPTIONS a

where not exists ( select '1' from DESIGNER9I.QMS_USER_OPTIONS b where a.user_name = b.user_name )

Bonus : Insert missing users

insert into DESIGNER9I.QMS_USER_OPTIONS

select user_name , 'FRE','NF','Y','Y','Y','QMS$BLACK_ON_YELLOW','QMS$WHITE_ON_DARKRED','QMS$BLACK_ON_GRAY','QMS$WHITE_ON_DARKBLUE','QUERY ONLY','SYSTEM',sysdate,'SYSTEM',sysdate

from

( select grantee user_name from dba_role_privs where granted_role = 'ICR_UTIL'

minus

select user_name from DESIGNER9I.QMS_USER_OPTIONS

order by 1 )

3.5.2 update target table row from another table if it exists

UPDATE table1 t1

SET (name, desc) = (SELECT t2.name, t2.desc

FROM table2 t2

WHERE t1.id = t2.id)

WHERE EXISTS (

SELECT 1

FROM table2 t2

WHERE t1.id = t2.id )

3.5.3 avoid ampersand substitution variable

update gds.association a set nom ='ASSOCIATION CULTURELLE&SPORTIVE' where a.nom = 'ASSOCIATION CULTURELLE ET SPORTIVE';

update gds.association a set nom ='ASSOCIATION CULTURELLE'||chr(38)||'SPORTIVE' where a.nom = 'ASSOCIATION CULTURELLE ET SPORTIVE';

3A.6 Analytical Functions

To get preceding , following line values ; in this example, we want a single row when there are 2 on source table.

select * from TABLE_1 order by 1

ID_LIGNE_FACTURE ID_FACTURE DDV_PRODUIT

101026 99843

146734 145122 5761

251306 248505 5774

251308 248506 5774

251310 248507 5775

379125 248507 5787

379596 373856 5787

with IDF AS (

select ID_LIGNE_FACTURE , ID_FACTURE , DDV_PRODUIT ,

lag(ID_FACTURE,1,0) over(order by ID_LIGNE_FACTURE) ID_FACTURE_preceding ,

lead(ID_FACTURE,1,0) over(order by ID_LIGNE_FACTURE) ID_FACTURE_following ,

lag(DDV_PRODUIT,1,0) over(order by ID_LIGNE_FACTURE) DDV_PRODUIT_preceding

from TABLE_1 order by 1,2

)

select ID_LIGNE_FACTURE , ID_FACTURE , DDV_PRODUIT , NULL DDV_PRODUIT_2 from IDF where ID_FACTURE != ID_FACTURE_preceding and ID_FACTURE != ID_FACTURE_following

union

select ID_LIGNE_FACTURE , ID_FACTURE , DDV_PRODUIT , DDV_PRODUIT_preceding from IDF where ID_FACTURE = ID_FACTURE_preceding

ID_LIGNE_FACTURE ID_FACTURE DDV_PRODUIT DDV_PRODUIT_2

101026 99843

146734 145122 5761

251306 248505 5774

251308 248506 5774

379125 248507 5787 5775

379596 373856 5787

3A.7 Hierarchical Query

create table owner.tab_auc as select * from owner.tab where 1=2

insert into owner.tab_auc select * from owner.tab where num_type_comp_father = 2502

insert into owner.tab_auc select * from owner.tab where num_type_comp in ('2502','2482','7')

commit ;

select * from owner.tab_auc order by num_type_comp_father

NUM_TYPE_COMP	NUM_TYPE_COMP_PERE	NUM_NIVEAU	TYPE_NAME	DUREE_VIE	FLAG_MODIF	ROOT_NUM_TYPE	TYPE_BASE
7	0	6			1	7	EQU
2482	7	6			1	7	EQU
2502	2482	6			1	7	EQU
2505	2502	6			1	7	EQU
2504	2502	6			1	7	EQU
2503	2502	6			1	7	EQU
2506	2502	6			1	7	EQU

Query through the hierarchy.

SELECT num_type_comp,num_type_comp_father,level,CONNECT_BY_ROOT num_type_comp AS ROOT_ID

FROM owner.tab_auc

START WITH num_type_comp_father = 7

CONNECT BY num_type_comp_father = PRIOR num_type_comp

ORDER SIBLINGS BY num_type_comp;

NUM_TYPE_COMP	NUM_TYPE_COMP_PERE	LEVEL	ROOT_ID
2482	7	1	2482
2502	2482	2	2482
2503	2502	3	2482
2504	2502	3	2482
2505	2502	3	2482
2506	2502	3	2482

3B] PL/SQL

3B.1 Call a procedure

declare

num number ;

begin

PR_WEB_VERS_DSS(num) ;

dbms_output.put_line('RET_COD = '||num) ;

end;

RET_COD = 0

PL/SQL procedure successfully completed.

pl/sql loop through a list of predefined varchar2 values

set serveroutput on

declare

type nt_type is table of varchar2(16);

nt nt_type := nt_type('DB1','DB2');

-- type nt_type is table of number;

-- nt nt_type := nt_type(1,2);

begin

for i in 1..nt.count loop

dbms_output.put_line(nt(i));

end loop;

end;

DB1

DB2

PL/SQL procedure successfully completed.

3B.2 DB_LOGON PL with Username to set Current_schema

CREATE OR REPLACE TRIGGER DB_LOGON_REP

after logon on database

DECLARE

-- username VARCHAR2 (32) ;

appli VARCHAR2 (16) ;

begin

-- E/ BO alter session évite les Synonymes

if ( user in ('SCH_REP') ) then

-- username := sys_context('USERENV','SESSION_USER') ;

select replace(user,'_REP','') into appli from dual ;

EXECUTE IMMEDIATE 'alter session set current_schema='||appli||'';

end if;

end;

3B.3 DB_LOGON PL : Terminate a session through the logon trigger

Accept every session from SVC service name connexion except UTIL01 User

select * from users_mon where service_name like 'SVC%'

CREATE OR REPLACE TRIGGER DB_LOGON_SVC

after logon on database

DECLARE

session_id number;

service VARCHAR2 (64) := sys_context('USERENV', 'SERVICE_NAME');

OSUS VARCHAR2 (30) := sys_context('USERENV', 'OS_USER');

e_nologon EXCEPTION;

begin

select sys_context('USERENV','SESSIONID') into session_id from dual;

IF session_id != 0 --ignore internal connections

then

if ( service = 'SVC.cg13.fr' ) then

if ( OSUS != 'UTIL01' ) then

-- terminate

RAISE e_nologon;

end if;

END IF;

EXCEPTION

WHEN e_nologon THEN

RAISE_APPLICATION_ERROR(-20001,'SVC unavailable, thank you');

end;

$ sqlplus UTIL01 @SVC_PRO

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 23 08:47:28 2016

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: SVC unavailable, thank you

ORA-06512: at line 20

Enter user-name:

3B.4 SQL over Database Links

Ø How to insert the result of a remote procedure execution and avoid ORA-02018: database link of same name has an open connection

CN INTEGER; CT INTEGER; IG NUMBER; CHAINE VARCHAR2(2024);

NB_APP number ;

CHAINE :='BEGIN SYSTEM.TYPE_APP_COUNT@LN(''TOTAL'' , :a) ; END; ' ;

EXECUTE IMMEDIATE CHAINE USING OUT NB_APP ;

-- dbms_output.put_line('NB_APP = '||NB_APP) ;

CHAINE :='insert into SCHEMA.STAT_MM_DBS_PRO_APP_COUNT select trunc(sysdate,''MONTH'') , name , ''TOTAL'' , :a from v$database@ln' ;

CT:= DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(CT,CHAINE,dbms_sql.native); -- supply binds

dbms_sql.bind_variable (CT, ':a', NB_APP);

IG := DBMS_SQL.EXECUTE(CT); DBMS_SQL.CLOSE_CURSOR(CT);

create database link "DBL_BASE" connect to <USR> identified by "psw" using 'BASE';

set serveroutput on

declare

nb_app number ;

begin

SCHEMA.TYPE_APP_COUNT@DBL_BASE('TOTAL',nb_app) ;

dbms_output.put_line('NB_APP = '||nb_app) ;

insert into SCHEMA.STAT_MM_DBS_PRO_APP_COUNT select sysdate , name , 'TOTAL' , nb_app from v$database@DBL_BASE ;

commit ;

end;

NB_APP = 141

CHAINE :='BEGIN SCHEMA.TYPE_APP_COUNT@ln(''TOTAL'' , :a) ; END; ' ;

EXECUTE IMMEDIATE CHAINE USING OUT NB_APP ;

CHAINE :='insert into SCHEMA.STAT_MM_DBS_PRO_APP_COUNT select sysdate , name , ''TOTAL'' , :a from v$database@ln' ;

EXECUTE IMMEDIATE CHAINE USING IN NB_APP ;

Ø ORA-04062 over db_link

The compiled code of the local procedure holds a timestamp copy of the timestamp at which the remote procedure was compiled.

On execution of the local procedure this timestamp copy is compared to the timestamp of the remote procedure.

If the comparison fails, the error you mention is thrown.

EXECUTE IMMEDIATE 'alter session set remote_dependencies_mode = ''SIGNATURE'' ' ;

3B.5 get every code using « utl_file » in a database

select distinct owner from dba_source where lower(text) like '%utl_file%' and owner not in ('SYS','SYSTEM') order by 1

4] SQLPLUS

Avoid any default setup :

SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF

4.1 : Get an HTML Output

sqlplus -s -MARKUP "HTML ON" system/${system_psw}@${BDc} @${REP_SCRIPTS}/awr_top_sql_buffers_report.sql ${wind} ${BGs}

4.2 SP2-0027: Input is too long (> 2499 characters) - line ignored

[This is a SQL*Plus limitation. The command line length is limited to 2500 characters]

- either we break the line with a Carriage Return

- or we rewrite

INSERT INTO ZECG1 select * from SCH01.ZECG1@INSTANCE.CORP.FR ;

5] Play with SYSDATE

Like in the Interstellar movie, go to the future or in the past !

One may want to modify the SYSDATE for testing purpose, here it is :

SQL> grant alter system to USR ;

SQL> Conn USR

SQL> ALTER SYSTEM SET fixed_date = '2014-03-24-10:00:00' ;

System altered.

SQL> select sysdate from dual ;

SYSDATE

----------

24/03/2014

SQL> ALTER SYSTEM SET FIXED_DATE=NONE;

System altered.

SQL> select sysdate from dual ;

SYSDATE

----------

07/11/2014

6] Unicity

6.1 Add a Unique Constraint / find duplicate

ALTER TABLE APP.TABLE01 ADD PRIMARY KEY (NOM_BASE,NOM_COMPTE,PERIODICITE_SAUVE) ;

-- find duplicate rows

select NOM_BASE,NOM_COMPTE,PERIODICITE_SAUVE, count(*) from APP.TABLE01

group by NOM_BASE,NOM_COMPTE,PERIODICITE_SAUVE

having count(*) > 1

order by 1,2,3 ;

6.2 Delete duplicate rows

create table auc_1 ( a int , b int ) ;

insert into auc_1 values ('10','200') ;

insert into auc_1 values ('20','300') ;

insert into auc_1 values ('10','400') ;

insert into auc_1 values ('30','500') ;

insert into auc_1 values ('10','600') ;

commit ;

-- keep only one ‘10’ record :

delete from auc_1 t1

where rowid < (

select max(rowid)

from auc_1 t2

where t2.a = t1.a);

select * from auc_1 order by 1 ;

A B

10 400

20 300

30 500

commit ;

drop table auc_1 ;

6.3 ORA-02270 on CONSTRAINT FOREIGN KEY even when Unique Index exists

Indeed, we have to create a Unique Constraint, in addition to the Unique Index, in the Parent Table :

create table t11 ( a int , b int ) ;

create table t12 ( a int , b int ) ;

ALTER TABLE t11 ADD (CONSTRAINT t12_FK FOREIGN KEY (b) REFERENCES t12 (b)) -- KO ORA-02270

create unique index t12_i1 on t12 (b) ;

ALTER TABLE t11 ADD (CONSTRAINT t12_FK FOREIGN KEY (b) REFERENCES t12 (b)) -- KO same

ALTER TABLE t12 ADD (CONSTRAINT t12_UK UNIQUE (b))

ALTER TABLE t11 ADD (CONSTRAINT t12_FK FOREIGN KEY (b) REFERENCES t12 (b)) -- OK

7] Others

7.1 Character Set

select VALUE$ from PROPS$ where name='NLS_CHARACTERSET';

SQL> select VALUE$ from PROPS$ where name='NLS_CHARACTERSET';

VALUE$

--------------------------------------------------------------------------------

WE8MSWIN1252

7.2 SQLLDR

Example

$ sqlldr SCH control=STAT_MM_SQL_INS_ALL_VOL.ctl

Password:

SQL*Loader: Release 10.2.0.5.0 - Production on Wed Mar 26 16:55:06 2014

Commit point reached - logical record count 37

$ cat STAT_MM_SQL_INS_ALL_VOL.ctl

load data

infile STAT_MM_SQL_INS_ALL_VOL.csv

append

into table STAT_MM_SQL_INS_ALL_VOL

fields terminated by ';'

(NAME,MONTH,TOTAL_DBF_GB)

Sqlldr produces Error Variable length field exceeds maximum length

For the character field longer than 255, we need to set explicitely the the field size in the loader controlfile [1061271.6 Metalink].

Cause

The default maximum length for delimited datatypes in SQL*Loader is 255 characters.

SQL*Loader reads the field information in the control file to determine the maximum space needed for each field's data.

$ cat inscription.ctl

LOAD DATA

INFILE 'export-csv.csv'

truncate

INTO TABLE formulaire_csv

FIELDS TERMINATED BY ';' trailing nullcols

(

N_DOSSIER,

…

LIEU_POLE,

PALMARES CHAR(500),

…

Sqlldr produces ORA-01722: invalid number

cat APP.ctl

LOAD DATA infile 'Tickets 2013 2014_04.txt' APPEND

INTO TABLE TICKETS_TABLE FIELDS TERMINATED BY "~"

TRAILING NULLCOLS

(

DIRECTION CHAR,

COUT_FACTURE DECIMAL EXTERNAL,

…

NO_POSTE CHAR

)

Record 1: Rejected - Error on table TICKETS_TABLE, column "COUT_FACTURE".

ORA-01722: invalid number

$ export NLS_LANG=FRENCH

=> ',' separator

Table TICKETS_TABLE:

1052994 Rows successfully loaded.

4 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 247680 bytes(64 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1052998

Total logical records rejected: 4

Total logical records discarded: 0

7.3 Search a string in every "char-like" column

Adapted from http://stackoverflow.com/questions/6389666/oracle-search-all-tables-all-columns-for-string.

Run as SYSTEM :

set serveroutput on

DECLARE

match_count integer;

v_search_str varchar2(4000) := '%app_pro%' ;

BEGIN

dbms_output.disable;

dbms_output.enable(1000000);

FOR t IN (SELECT a.owner,a.table_name,a.column_name FROM dba_tab_columns a , dba_tables b

WHERE data_type in ('CHAR', 'VARCHAR2') -- , 'NCHAR', 'NVARCHAR2', 'CLOB', 'NCLOB')

and a.owner in ('<schema_list>') -- and rownum < 200

and a.table_name = b.table_name and a.owner = b.owner )

LOOP

BEGIN

-- dbms_output.put_line('SELECT COUNT(1) FROM "'||t.owner ||'".'|| t.table_name||' WHERE "'||t.column_name||'" like ''%YYY_PRO%'' ') ;

EXECUTE IMMEDIATE

'SELECT COUNT(1) FROM "'||t.owner||'".'||t.table_name||' WHERE lower("'||t.column_name||'") like :1 ' INTO match_count USING v_search_str ;

IF match_count > 0 THEN

dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );

END IF;

EXCEPTION

WHEN others THEN

dbms_output.put_line( 'Error encountered trying to read ' ||

t.column_name || ' from ' ||

t.owner || '.' || t.table_name );

END;

END LOOP;

END;

APP.A_PARAMETERS PARAM_VALUE 2

PL/SQL procedure successfully completed.

Run as a user :

-- Find a string in every tables

-- Gives table / field and row count of the string

set serveroutput on

DECLARE

match_count integer;

v_search_str varchar2(4000) := '%Marc%' ;

BEGIN

dbms_output.disable;

dbms_output.enable(1000000);

FOR t IN (SELECT a.table_name,a.column_name FROM user_tab_columns a , user_tables b

WHERE data_type in ('CHAR', 'VARCHAR2') -- , 'NCHAR', 'NVARCHAR2', 'CLOB', 'NCLOB')

-- and a.owner in ('<schema_list>') -- and rownum < 200

and a.table_name = b.table_name )

LOOP

BEGIN

-- dbms_output.put_line('SELECT COUNT(1) FROM "'||t.owner ||'".'|| t.table_name||' WHERE "'||t.column_name||'" like ''%YYY_PRO%'' ') ;

EXECUTE IMMEDIATE

'SELECT COUNT(1) FROM '||t.table_name||' WHERE "'||t.column_name||'" like :1 ' INTO match_count USING v_search_str ;

IF match_count > 0 THEN

dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );

END IF;

EXCEPTION

WHEN others THEN

dbms_output.put_line( 'Error encountered trying to read ' ||

t.column_name || ' from ' ||

t.table_name );

END;

END LOOP;

END;

7.4 How to select a string in View or MView code ?

Views

CREATE TABLE views_source ( view_name VARCHAR2(30), text CLOB );

BEGIN

FOR v IN (SELECT view_name, text FROM user_views) LOOP

INSERT INTO views_source VALUES (v.view_name, v.text);

END LOOP;

COMMIT;

END;

SELECT * FROM views_source WHERE instr(upper(text), 'EMPLOYEE') > 0;

Thanks to http://przemyslawkruglej.com/ :

"And that’s it! The trick here is this: in PL/SQL context, Oracle automatically converts for us a LONG value to a CLOB."

MViews

drop TABLE mviews_source ;

CREATE TABLE mviews_source ( owner varchar2(32) , view_name VARCHAR2(30), text CLOB ) ;

-- truncate TABLE mviews_source ;

BEGIN

FOR v IN (SELECT owner,mview_name, query FROM dba_mviews) LOOP

INSERT INTO mviews_source VALUES (v.owner,v.mview_name, v.query);

END LOOP;

COMMIT;

END;

SELECT * FROM mviews_source WHERE instr(upper(text), 'HR.') > 0;

8] Library Cache Lock

Finding Lib Cache Pin Lock from Bobak, Mark.

col OBJECT_NAME for a30

select /*+ ordered */

decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,'CLUSTER',

4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

11, 'PACKAGE BODY', 12, 'TRIGGER',

13, 'TYPE', 14, 'TYPE BODY',

19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21,'LOB',

22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',

28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',

32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION',

35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',

42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT',

46, 'RULE SET', 47, 'RESOURCEPLAN', 48, 'CONSUMER GROUP',

51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA',

57, 'SECURITY PROFILE', 59, 'RULE', 62, 'EVALUATION CONTEXT',

'UNDEFINED') object_type,

lob.KGLNAOBJ object_name, pn.KGLPNMOD lock_mode_held, pn.KGLPNREQ lock_mode_requested,

ses.sid, ses.serial#, ses.username

FROM

v$session_wait vsw, x$kglob lob, x$kglpn pn, v$session ses

WHERE

pn.KGLPNUSE = ses.saddr and pn.KGLPNHDL = lob.KGLHDADR

and lob.kglhdadr = vsw.p1raw and vsw.event = 'library cache pin'

order by lock_mode_held desc

SQL> set lines 150 pages 5000

SQL> /

OBJECT_TYPE OBJECT_NAME LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SERIAL# USERNAME

------------------ ------------------------------ -------------- ------------------- ---------- ---------- ------------------------------

PACKAGE ODI_COR_DM_DIM_MARCHE 2 0 367 1617 IOW_OIN_DWH

PACKAGE ODI_COR_DM_DIM_MARCHE 0 3 17 621 IOW_ODI_DTM

9] Get Application Dependencies

Synonym pointing to
Object linked to another application
Grant towards object of

-- search for dependencies between applications

select 'Synonym '||owner||'.'||synonym_name||' -> '||table_owner||'.'||table_name||'' from dba_synonyms

where owner in ('APP01','APP02','APP03','APP04','APP05') and table_owner in ('APP01','APP02','APP03','APP04','APP05') and owner != table_owner order by 1

select ''||type||' '||owner||'.'||name||' -> '||referenced_owner||'.'||referenced_name||''

from dba_dependencies where owner != referenced_owner and

owner in ('APP01','APP02','APP03','APP04','APP05') and referenced_owner in ('APP01','APP02','APP03','APP04','APP05') order by 1

select 'Grant '||grantee||' '||privilege||' -> '||owner||'.'||table_name||' with Grantable '||GRANTABLE||'' from dba_tab_privs

where grantee in ('APP01','APP02','APP03','APP04','APP05') and owner in ('APP01','APP02','APP03','APP04','APP05') and grantee != owner order by 1

MATERIALIZED VIEW APP_01.V_FACTURES -> APP_02.FACE

SYNONYM APP_01.S_APP_02_ANAL -> APP_02.ANAL

Grant APP_02 UPDATE -> APP_01.IEAP with Grantable NO

Grant APP_02 UPDATE -> APP_01.IECP with Grantable NO

-- search for all applications linked to &USR

select ''||type||' '||owner||'.'||name||' -> '||referenced_owner||'.'||referenced_name||'' LINK_DESC , owner , type

from dba_dependencies where owner != referenced_owner and owner not like '&USR%' and referenced_owner in ('&USR')

union

select 'Grant '||grantee||' '||privilege||' -> '||owner||'.'||table_name||'' , grantee owner , privilege type

from dba_tab_privs where grantee not like '&USR%' and owner in ('&USR') and grantee != owner order by 2

LINK_DESC OWNER TYPE

Grant APP01 SELECT -> APP.CORP_INSTI_UTILISATEUR APP01 SELECT

SYNONYM APP01.CORP_INSTI_UTILISATEUR -> APP.CORP_INSTI_UTILISATEUR APP01 SYNONYM

Grant APP01 EXECUTE -> APP.CORP_INSTI_PK_PARAMETRES_UTIL APP01 EXECUTE

-- search for all applications linked from SCH01

select distinct to_owner , type , count(1) over ( partition by to_owner , type ) nb_obj from (

select ''||type||' '||owner||'.'||name||' -> '||referenced_owner||'.'||referenced_name||'' , referenced_owner to_owner , type

from dba_dependencies where owner != referenced_owner and owner = 'SCH01'

union

select 'Grant '||grantee||' '||privilege||' -> '||owner||'.'||table_name||'' , owner to_owner , privilege type

from dba_tab_privs where grantee = 'SCH01' and grantee != owner order by 2

) order by 1,2

Beware :

"We don't add dependency whenever the MV has a base table which is owned by SYS.

In fact, if the MV is created with enable query rewrite and it has any SYS table reference, the MV creation will fail with an error.

If the MV is created with disable query rewrite and it has any SYS table reference, the MV creation will succeed but no dependency is registered."

Another one, get every objects that points to a schema ( example HR )

-- direct link

select ''||type||' '||owner||'.'||name||' -> '||referenced_owner||'.'||referenced_name||'' LINK_DESC -- , owner , type

from dba_dependencies where owner != referenced_owner and owner not like '&USR%' and referenced_owner in ('&USR')

and type != 'SYNONYM'

order by 1

-- link by synonyms

select ''||type||' '||owner||'.'||name||' -> '||referenced_owner||'.'||referenced_name||'' LINK_DESC from (

with A AS (

select owner,name , referenced_owner,referenced_name

from dba_dependencies where owner != referenced_owner and owner not like '&USR%' and referenced_owner in ('&USR')

and type = 'SYNONYM' )

select B.TYPE,B.owner,B.name , A.referenced_owner,A.referenced_name from A , dba_dependencies B where A.owner = B.referenced_owner and A.name = B.referenced_name

order by 1,2

) order by 1

10] Resource Management

10.1 The old way : Profile

select * from dba_profiles ;

alter profile BO_USR LIMIT SESSIONS_PER_USER 5

11] Oracle Sessions

11.1 Copycat an Oracle User !

The Proxy method helps us here to connect and « become » a user without ever knowing its password :

grant connect through su

SQLPLUS as SYSTEM

SQL> create user USER_PROXY identified by USER_PROXY;

SQL> alter user USER grant connect through USER_PROXY ;

SQL> connect USER_PROXY[USER]/USER_PROXY

Connected.

11.2 blog oracle show current_schema in session

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

--------------------------------------------------------------------------------

USERA

Manage Instance

0] Query Hidden Instance Parameters

col CURRENT_VAL for a10

select a.ksppinm hidden_name, b.ksppstvl current_val, b.KSPPSTDF default_val from X$KSPPI a, x$ksppsv b where a.indx = b.indx and a.ksppinm like '%max_services%' order by 1 ;

1] 10g Initialization Parameters

Examples I use as a start.

#########

# Database Identification

#########

db_domain="corp.fr"

db_name=PPRQU

# Identification Data Guard

db_unique_name=PPRQU

instance_name=PPRQU

compatible=10.2.0

# 1/ structure

#########

# Cache and I/O

#########

db_block_size=8192

#########

# File Configuration

#########

control_files=("/rdo1/pprqu/ctl/cntl_1.ctl", "/rdo2/pprqu/ctl/cntl_2.ctl")

#########

# System Managed Undo and Rollback Segments

#########

undo_management=AUTO

undo_tablespace=UNDO

#########

# Redo Log and Recovery

#########

fast_start_mttr_target=300

log_checkpoint_timeout=0

#########

# Archive

#########

log_archive_dest_1='LOCATION=/arch/pprqu/'

log_archive_format='PPRQU%t_%s_%r.arc'

# 2/ logs

#########

# Diagnostics and Statistics

#########

background_dump_dest=/work/oracle/pprqu/bdump

core_dump_dest=/work/oracle/pprqu/cdump

user_dump_dest=/work/oracle/pprqu/udump

timed_statistics=TRUE

max_dump_file_size = 20480

#########

# Security and Auditing

#########

audit_file_dest=/work/oracle/pprqu/adump

remote_login_passwordfile=EXCLUSIVE

os_authent_prefix=''

remote_os_authent=TRUE

# 3/ SGA

#########

# SGA Memory

#########

sga_target=288M

# 4/ sessions

#########

# Processes and Sessions

# For Forms processes, there will be one database session per

# open form, with a minimum of two sessions per Forms user (one

# for the navigator form, and one for the active form.

#########

processes=256

sessions=512

#########

# Cursors and Library Cache

#########

cursor_sharing = EXACT

open_cursors=300

session_cached_cursors = 200

#########

# Sort, Hash Joins, Bitmap Indexes

#########

pga_aggregate_target=64m

# MTS

sort_area_size=16777216

hash_area_size=16777216

#########

# MTS

#########

dispatchers='(protocol=TCP)(disp=2)'

max_dispatchers=4

shared_servers=4

max_shared_servers=8

# 5/ optimiseur

#########

# Optimizer

#########

_b_tree_bitmap_plans=false

optimizer_secure_view_merging=false

# 6/ Divers

#########

# Advanced Queuing (AQ) and Job Queues

# AQ requires the TM process to handle delayed messages. A number

# of Application modules use AQ, including Workflow. Job Queues

# enable advanced queue to submit background jobs.

#########

aq_tm_processes = 1

job_queue_processes=5

#########

# Parallel Execution

# Some of the Applications Concurrent Programs use parallel

# execution including DBI programs and Gathering Statistics.

#########

parallel_max_servers = 8 # Max. value should be 2*CPUs

parallel_min_servers = 0

#########

# AIX

#########

disk_asynch_io=true

filesystemio_options='SetAll'

2] 11g Initialization Parameters

###########################################

# Database Identification

###########################################

db_domain="corp.fr"

db_name="BASE"

compatible=11.2.0.0.0

# 1/ structure

###########################################

# Cache and I/O

###########################################

db_block_size=8192

###########################################

# File Configuration

###########################################

control_files=("/rdo1/base/ctl/cntl_1.ctl", "/rdo2/base/ctl/cntl_2.ctl")

###########################################

# System Managed Undo ( default )

###########################################

undo_tablespace=UNDOTBS1

###########################################

# Archive

###########################################

log_archive_dest_1='LOCATION=/arch/base/'

log_archive_format='BASE%t_%s_%r.arc'

# 2/ logs

###########################################

# Diagnostics , Security and Auditing

###########################################

diagnostic_dest=/work/oracle/base

audit_file_dest=/work/oracle/base/adump

audit_trail=db

remote_login_passwordfile=EXCLUSIVE

os_authent_prefix=''

# 3/ memory

###########################################

# Processes and Sessions , Cursors and Lib Cache

###########################################

memory_target=348M

processes=300

open_cursors=300

###########################################

# Shared Server

###########################################

DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=2)"

# 4/ Divers

###########################################

# AIX

###########################################

disk_asynch_io=true

filesystemio_options='SetAll'

###########################################

# Network Registration

###########################################

#local_listener=LISTENER_BASE

3] 12c Initialization Parameters

4] Change SID and/or DBID with newid

The NID utility allows you to change only the DBNAME, or only the DBID or both DBNAME and DBID in the same command.

4.1 Change Both DBID and DBNAME with « nid »

1. Backup of the database.

$ rman target /

run {

Change Archivelog All Crosscheck ;

backup database format '/rman/db_name/full/db_F_%d_s%s_U%U_%Y%M%D'

plus archivelog format '/rman/db_name/full/ar_F_%d_s%s_U%U_%Y%M%D' tag = 'F' delete input ;

}

2. Shutdown IMMEDIATE of the database

3. STARTUP MOUNT

4. Open one session and run NID with sysdba privileges

$ nid TARGET=/ DBNAME=NEW_DB_NAME

DB_NAME :/home/oracle > nid TARGET=/ DBNAME=NEW_DB_NAME

DBNEWID: Release 12.1.0.2.0 - Production on Tue Nov 8 11:25:24 2016

Connected to database DB_NAME (DBID=157110432)

Connected to server version 12.1.0

Control Files in database:

/rdo1/new_db_name/ctl/cntl_1.ctl

/rdo2/new_db_name/ctl/cntl_2.ctl

Change database ID and database name DB_NAME to NEW_DB_NAME? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 157110432 to 919288100

Changing database name from DB_NAME to NEW_DB_NAME

Control File /rdo1/new_db_name/ctl/cntl_1.ctl - modified

Control File /rdo2/new_db_name/ctl/cntl_2.ctl - modified

Datafile /data/new_db_name/system_01.db - dbid changed, wrote new name

Datafile /data/new_db_name/undo_01.db - dbid changed, wrote new name

…

Datafile /data/new_db_name/ija_662_cms_idx.db - dbid changed, wrote new name

Datafile /data/new_db_name/ija_662_cms_connect_dat.db - dbid changed, wrote new name

Datafile /data/new_db_name/ija_662_cms_connect_idx.db - dbid changed, wrote new name

Datafile /data/new_db_name/temp01.db - dbid changed, wrote new name

Control File /rdo1/new_db_name/ctl/cntl_1.ctl - dbid changed, wrote new name

Control File /rdo2/new_db_name/ctl/cntl_2.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to NEW_DB_NAME.

Modify parameter file and generate a new password file before restarting.

Database ID for database NEW_DB_NAME changed to 919288100.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

DB_NAME :/home/oracle >

5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database

6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.

DB_NAME :/work/oracle/db_name/pfile > strings spfileDB_NAME.ora | grep -i db_name

*.audit_file_dest='/work/oracle/db_name/adump'

*.core_dump_dest='/work/oracle/db_name/cdump'

*.db_name='DB_NAME'

*.db_unique_name='DB_NAME'

*.diagnostic_dest='/work/oracle/db_name'

*.instance_name='DB_NAME'

log_archive_format='DB_NAME%t_%s_%r.arc'

DB_NAME :/work/oracle/db_name/pfile >

SQL> SELECT name FROM v$parameter WHERE isdeprecated ='TRUE' ORDER BY name ;

sec_case_sensitive_logon

The IGNORECASE argument of ORAPWD and the SEC_CASE_SENSITIVE_LOGON system parameter are deprecated in Oracle Database 12c. By default, passwords in Oracle Database 12c are case sensitive.

SQL> create pfile = '/work/oracle/db_name/pfile/initDB_NAME.ora_NID' from spfile = '/work/oracle/db_name/pfile/spfileDB_NAME.ora' ;

DB_NAME :/work/oracle/db_name/pfile > sed -e 's/db_name/new_db_name/g' -e 's/DB_NAME/NEW_DB_NAME/g' initDB_NAME.ora_NID > initNEW_DB_NAME.ora

NEW_DB_NAME :/work/oracle/db_name/pfile > diff initDB_NAME.ora_NID initNEW_DB_NAME.ora

1,10c1,10

< DB_NAME.__data_transfer_cache_size=0

< DB_NAME.__db_cache_size=838860800

< DB_NAME.__java_pool_size=16777216

< DB_NAME.__large_pool_size=150994944

< DB_NAME.__oracle_base='/oracle'#ORACLE_BASE set from environment

< DB_NAME.__pga_aggregate_target=822083584

< DB_NAME.__sga_target=1325400064

< DB_NAME.__shared_io_pool_size=0

< DB_NAME.__shared_pool_size=301989888

< DB_NAME.__streams_pool_size=0

---

> NEW_DB_NAME.__data_transfer_cache_size=0

> NEW_DB_NAME.__db_cache_size=838860800

> NEW_DB_NAME.__java_pool_size=16777216

> NEW_DB_NAME.__large_pool_size=150994944

> NEW_DB_NAME.__oracle_base='/oracle'#ORACLE_BASE set from environment

> NEW_DB_NAME.__pga_aggregate_target=822083584

> NEW_DB_NAME.__sga_target=1325400064

> NEW_DB_NAME.__shared_io_pool_size=0

> NEW_DB_NAME.__shared_pool_size=301989888

> NEW_DB_NAME.__streams_pool_size=0

12c12

< *.audit_file_dest='/work/oracle/db_name/adump'

---

> *.audit_file_dest='/work/oracle/new_db_name/adump'

16c16

< *.core_dump_dest='/work/oracle/db_name/cdump'

---

> *.core_dump_dest='/work/oracle/new_db_name/cdump'

20,22c20,22

< *.db_name='DB_NAME'

< *.db_unique_name='DB_NAME'

< *.diagnostic_dest='/work/oracle/db_name'

---

> *.db_name='NEW_DB_NAME'

> *.db_unique_name='NEW_DB_NAME'

> *.diagnostic_dest='/work/oracle/new_db_name'

28c28

< *.instance_name='DB_NAME'

---

> *.instance_name='NEW_DB_NAME'

31c31

< *.log_archive_format='DB_NAME%t_%s_%r.arc'

---

> *.log_archive_format='NEW_DB_NAME%t_%s_%r.arc'

46d45

< *.sec_case_sensitive_logon=TRUE

57d55

< *.utl_file_dir='/tmp'

NEW_DB_NAME :/work/oracle/db_name/pfile >

$ cd /work/oracle/new_db_name/pfile

NEW_DB_NAME :/work/oracle/new_db_name/pfile > cp -p /work/oracle/db_name/pfile/initNEW_DB_NAME.ora .

NEW_DB_NAME :/work/oracle/new_db_name/pfile > sqlplus / as sysdba

SQL> create spfile = '/work/oracle/new_db_name/pfile/spfileNEW_DB_NAME.ora' from pfile = '/work/oracle/db_name/pfile/initNEW_DB_NAME.ora' ;

NEW_DB_NAME :/oracle/12CDB/dbs > cat initNEW_DB_NAME.ora

spfile='/work/oracle/new_db_name/pfile/spfileNEW_DB_NAME.ora'

7. Create a new password file.

NEW_DB_NAME :/oracle/12CDB/dbs > orapwd file=orapw${ORACLE_SID} password=<psw>pn

8. Startup of the database with open resetlogs

NEW_DB_NAME :/oracle/12CDB/dbs > sqlplus / as sysdba

startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2926472 bytes

Variable Size 1291847800 bytes

Database Buffers 838860800 bytes

Redo Buffers 13848576 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

4.2 Change DBID with « nid »

1. Backup the database

2. SHUTDOWN IMMEDIATE of the database

3. STARTUP MOUNT

4. Open one session and run NID with sysdba privileges

% nid TARGET=/

5. alter database open resetlogs ;

OraCsdP

Categories

samedi 1 janvier 2022

Miscellanous : SQL and More