dikpater

Friday, November 3, 2017

reset password of apex user.

set echo off
set verify off
undefine workspace
undefine newwachtwoord
undefine USERNAAM
prompt geef TRUE of FALSE op bij de CHANGE_PASSWORD_ON_FIRST_USE
begin
    wwv_flow_api.set_security_group_id (p_security_group_id => apex_util.find_security_group_id(upper('&&workspace')));
for user_rec in (
select user_id, user_name
from WWV_FLOW_USERS
where security_group_id = ( select apex_util.find_security_group_id('&&workspace') from dual )
and upper(user_name) = upper('&&USERNAAM')
)
loop
    APEX_UTIL.RESET_PASSWORD(
        p_user_name => upper('&&USERNAAM'),
P_NEW_PASSWORD => '&&newwachtwoord',
P_CHANGE_PASSWORD_ON_FIRST_USE=> &CHANGE_PASSWORD_ON_FIRST_USE);
end loop;
exception when others then raise ;
end ;
/
prompt &&USERNAAM wachtwoord is geworden &&newwachtwoord
prompt geef commit of rollback

throughput of expdp

while true
do
prev=`ls -l *.dmp | awk '{a+=$5}END{printf("%.0f\n",a/1024/1024)}'`
sleep 60
next=`ls -l *.dmp | awk '{a+=$5}END{printf("%.0f\n",a/1024/1024)}'`
doorloop=`expr $next - $prev`
echo $next $prev $doorloop
done

Tuesday, October 17, 2017

EM12c and EM13c datasources report.

SELECT
        *
FROM
        (
                SELECT
                        'CONNECTIONPOOLS',
                        s4.cm_target_name,
                        s4.URL
                FROM
                        CM$MGMT_OC4J_CONNECTIONPOOLS s4

                UNION ALL

                SELECT
                        'MANAGED DATASOURCE',
                        s3.cm_target_name   ,
                        s3.URL
                FROM
                        CM$MGMT_OC4J_DATASOURCES s3

                UNION ALL

                SELECT
                        'NATIVE'         ,
                        s5.cm_target_name,
                        s5.URL
                FROM
                        CM$MGMT_OC4J_NATIVEDATASOURCES s5
        )
        sel

Monday, September 25, 2017

workspace export in sqldeveloper with apex 3.2.1 runtime

From sqldeveloper you can export the workspace in a runtime environment in apex 3.2.
Just did a trace in our DVL environment, this was executed.
In sqldeveloper just put in the code , activate OWA output from view -> owa output :

NOTE replace :WORKSPACE with your workspace.

declare
l_orig_sgid number := wwv_flow_security.g_security_group_id;
begin
for c1 in (select provisioning_company_id
    from wwv_flow_companies
    where short_name = upper(':WORKSPACE')) loop
    wwv_flow_security.g_security_group_id := c1.provisioning_company_id;
end loop;
--
-- htp.init;
wwv_flow.g_page_text_generated := true;
wwv_flow_fnd_user_api.g_mime_shown := true;
owa_util.mime_header('application/x-sql',false);
htp.p('Content-Disposition: attachment; filename='|| wwv_flow_utilities.escape_url(p_url => ':WORKSPACE', p_url_charset => 'utf-8') ||'.sql');
owa_util.http_header_close;
--
wwv_flow_fnd_user_api.export_fnd_users(p_export_format=>'UNIX');
--
wwv_flow.g_page_text_generated := true;
wwv_flow.g_unrecoverable_error := true;
wwv_flow_security.g_security_group_id := l_orig_sgid;
--
exception when others then
wwv_flow_security.g_security_group_id := l_orig_sgid;
raise;
end;
/

Select 'greetings from dik pater ' from dual ;

Monday, April 24, 2017

keepass keeppass slow on opening files

We solved this by View -> disable Show Entries of Subgroups , 2 seconds opening now , instead of minutes !

Monday, January 30, 2017

Regexp replace until space sed regular expression

I asked my collegues how to replace until next space with sed.

They solved it for me.

sed -i 's/password=[^[:space:]]*/password=geenpassword/g' test.txt

I had to blog this excellent answer.

Thanks to Danny and Frank for solving this.

Btw.

I used awk for this.

But that was not the sed solution.

{for (i=1; i <= NF; i++) { if ($i !~ /password/) {printf($i" ") ;}
else {printf("GEHEIM ")}if(i==NF){printf("\n")}}}

Monday, October 10, 2016

oem 13c firefox flash performance tab not working

In the default downloaded image oem 13c from otn Firefox performance page was not working.
I downloaded the latest version of flash libflashplayer.so from adobe http://labs.adobe.com/downloads
I choosed:
https://fpdownload.macromedia.com/pub/labs/flashruntimes/flashplayer/linux64/libflashplayer.so

I copied this file to /usr/lib/mozilla/plugins
after that I installed flash-plugin-11.2.202.635-release.x86-64.rpm.
rpm -ivh flash-plugin-11.2.202.635-release.x86-64.rpm

I restarted Firefox and OEM 13C Performance tab was working !

Thursday, December 24, 2015

brief technical note on images export import to new apex 41 database from hpux 11gr2 to linux 11gr2

We created a database on our new platform, installed apex in it with apexins.sql

Then we made a full export, we imported everything except apex_040100 user

Next we created workspaces and applications in the new environment.

We exported these workspace and applications with scripting from the internet.

the apexexport utility in apex41 gave empty files as a result.

INFO APEXEXport

$ cd ..apex41/apex/utilities
$ export CLASSPATH=.:${ORACLE_HOME}/oui/jlib/classes12.jar
$ read wachtwoord
# exporteer eerst de WORKSPACES middels test_export
$ java oracle.apex.APEXExport -db HOSTNAME:1521:DATABASE_SERVICE -user system -password $wachtwoord -instance

Gave me the export of the applications
But the workspace files were empty, THIS is a bug.

https://apexplained.wordpress.com/2012/03/20/workspace-application-and-page-export-in-plsql/

I used to export the workspaces.
I installed the procedures and then used :

REM desc PRO_EXPORT_WORKSPACE
REM PROCEDURE PRO_EXPORT_WORKSPACE
REM Argument Name Type In/Out Default?
REM ------------------------------ ----------------------- ------ --------
REM P_WORKSPACE_ID NUMBER IN
REM P_INCLUDE_TEAM_DEV BOOLEAN IN
REM P_ORA_DIR VARCHAR2 IN
REM
REM MY_ORA_DIR
REM
col workspace_id format 999999999999999999
declare
cursor c_ws is
select workspace_id from apex_workspaces where workspace_id > 11 ;
r_ws c_ws%rowtype ;
begin
for r_ws in c_ws
loop
PRO_EXPORT_WORKSPACE( r_ws.workspace_id , TRUE,'MY_ORA_DIR') ;
end loop ;
end;
/

We exported the source data with datapump to file dp_EVERYTHING_%U.dmp

expdp full=y DIRECTORY=EXP_DB DUMPFILE=dp_EVERYTHING%u LOGFILE=EXP_DB:dp_EVERYTHING.log FILESIZE=20G PARALLEL=4 FLASHBACK_TIME=SYSTIMESTAMP

We created a database directory called IMP_DB in the target database.

$ cat images_impdp.par

DUMPFILE=dp_EVERYTHING%U.dmp

parallel=4

directory=IMP_DB

table_exists_action=append

tables=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$

$ cat images_impdp_WWV_FLOW_REPOSITORIES_TO_DIKPATER.par

DUMPFILE=dp_EVERYTHING%U.dmp
parallel=4
directory=IMP_DB
tables=APEX_040100.WWV_FLOW_CSS_REPOSITORY,APEX_040100.WWV_FLOW_IMAGE_REPOSITORY,APEX_040100.WWV_FLOW_HTML_REPOSITORY

remap_schema=(APEX_040100:DIKPATER)
remap_tablespace=(SYSAUX:USERS)

SQL> alter table flows_files."WWV_FLOW_FILE_OBJECTS$" disable all triggers ;
$ impdp parfile=images_impdp.par

SQL> alter table flows_files."WWV_FLOW_FILE_OBJECTS$" enable all triggers ;

Now we handle :

apex_040100.WWV_FLOW_IMAGE_REPOSITORY
apex_040100.WWV_FLOW_CSS_REPOSITORY
apex_040100.WWV_FLOW_HTML_REPOSITORY

!!! create a user DIKPATER ( in my case) to hold the 3 REPOSITORY tables temporary.
!!! with tablespace quota ...and create table

$ impdp parfile=images_impdp_WWV_FLOW_REPOSITORIES_TO_DIKPATER.par

SQL> select * from all_objects where lower(object_name)like '%wwv_flow_%repository%'

and owner = 'APEX_040100' and object_type = 'TABLE'

SQL> select * from apex_040100.WWV_FLOW_CSS_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_CSS_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_CSS_REPOSITORY
select * from dikpater.WWV_FLOW_CSS_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_CSS_REPOSITORY
enable all triggers ;

SQL> select * from APEX_040100.WWV_FLOW_IMAGE_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_IMAGE_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_IMAGE_REPOSITORY
select * from dikpater.WWV_FLOW_IMAGE_REPOSITORY
where (flow_id, image_name, security_group_id) not in ( select flow_id, image_name, security_group_id from apex_040100.WWV_FLOW_IMAGE_REPOSITORY) ;
SQL> alter table apex_040100.WWV_FLOW_IMAGE_REPOSITORY
enable all triggers ;

SQL> alter table apex_040100.WWV_FLOW_HTML_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_HTML_REPOSITORY
select * from dikpater.WWV_FLOW_HTML_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_HTML_REPOSITORY
enable all triggers

Now the images are imported and ready to use.
I found many hits on the internet about missing images after import, they suggest just to import WWV_FLOW_FILE_OBJECTS$ alone, but in my opinion it is not enough.
I cannot find if this is a supported method, but maybe this can be helpfull.

Regards.

Dik Pater

EM12c Metric Extension output

I made a Metric Extension called dblink.

Al it does is :

SELECT d.name ||':'

||':' || dbl.owner

||':' || dbl.db_link

||':' || dbl.username
||':' || dbl.host

||':' || dbl.created RIJ

, 'DBLINKINFO:' text

FROM dba_db_links dbl,

v$database d

Next I added rij as the key column and tekst as the value column.

Next Next Finish

Deploy to targets

And now you can see ALL your database links from a single point of view in your EM12C repository database.

select entity_name,metric_key_value, count(*)

from GC_METRIC_STR_VALUES a

where metric_group_label='dblink'

group by entity_name,metric_key_value

Regards

Dik Pater

The Netherlands

Wednesday, June 3, 2015

upgrade apex 41 to apex 425 issue ora-01400 ARP DML automatic row processing

automatic row processing dml not working after upgrade apex 41 to apex 425

After upgrade from apex41 to apex425 we experience that ARP dml is not working properly.
We loose variable information, so the insert fails.

Error Codes : ORA-01400

This was to a bug :

In our case the page item property was Display Only.
We set save session state to YES.
It solved the problem.

Friday, August 30, 2013

SLOW WEBLOGIC STARTUP SLOW SOA PROCESSING JDBC DATASOURCE HANGS WEBLOGIC ORACLE on VIRTUAL LINUX

The FMW Configuration Wizard Is Very Slow On Linux Virtual Environments. The Startup Of WLS Servers Is Also Very Slow. (Doc ID 1344974.1)

We had these kinds of problems.

1. Weblogic starts slow. ( OBIEE, Cloudcontrol and other wls ) approximely 10-15 minuts
2. JDBC Datasources hang in weblogic.
3. Degradation performance soa environments ( high cpu usage)

After executing Solution 1from the note

1) Download and install the following rpm: rng-utils-2.0-1.14.1.fc6.x86_64.rpm . Contact your vendor for download details.

2) Startup the random generator as follows:

rngd -r /dev/urandom -o /dev/random -t 1

All our problems are history ;-)

So Quick startup of weblogic, no jdbc datasource hangs, good soa performance ( less cpu !)

See also

http://www.usn-it.de/index.php/2009/02/20/oracle-11g-jdbc-driver-hangs-blocked-by-devrandom-entropy-pool-empty/ :

Per your recommendation, I installed an older fan right above the server next to a microphone. This is opposed to new Dyson Air Multipliers. We all know that only the wind buffeting caused by the blades will truly engage a random pattern that will allow the entropy daemon to capture the wave patterns properly. By having a bad bearing the noise pattern although repeatable was just random enough to get the entropy daemon garbage cleanup done properly – but occasionally I still lost connections; especially to system 10G. So I then installed a parrot on top of the fan and had it fed my a datacenter troll (DCT – we call him Ed) at random times throughout the day. In effect when the parrot took a poop the random noise of the **** hitting the fan resolved all my connection issues. Including 10G!! You can imagine how happy my manager was to have the end of entrophy issues!! However, every now and then I walk into the data centre and he has the parrot on his shoulder pretending to be Captain Morgan. He’s always striking that silly pose with his foot on the barrel. To resolve this I put a honey badger in a parrot suit on top the fan – sure enough the random feedings stopped the random lost connections again. However, one day my manager came in with cuts all over his face. I asked him what the heck happened? He said he was making sure the **** hit the fan, but when it (the ****) looked different he was concerned for Polly (a fore mentioned parrot). When I told him it was really a honey badger we all had a good laugh, and to date we wonder at how when the **** hits the fan things work properly. Frankly when things hang we still ask “Is the **** hitting the fan?” Around here – most of the time it is. Thanks again for the help. Damn it Ed – what the hell are you doing go feed Polly. Geeze. I need to outsource him – slacker.

Met vriendelijke groeten,
Dik Pater

Saturday, May 4, 2013

High water mark and suspicious large indexes.

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--
-- NAAM: show_hwm_simple_and_index.sql
-- DOEL: HighWaterMark info.
-- TYPERING: select
-- PARAMETERS: -
-- BENODIGDE PRIVILEGES select_catalog_role
-- ANDERE BEPERKINGEN: -
-- OPMERKINGEN: -
--
-- WIJZIGINGSHISTORIE:
--
-- Versie Datum Auteur Omschrijving
-- --------- ---------- --------------- ------------------------------------
-- 1.0 25-03-2002 Dik eerste oplevering
-- 2.0 2013 Dik added index information
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
col table_name format a30
col owner format a12
col percentage_waisted format 999.99
col hwm_MB format 9999999.99
col rows_length_MB format 9999999.99
set verify off
undefine percentage_waisted_allowed
undefine min_number_blocks_to_check_on
set lines 150
prompt '***********************************************************************************'
prompt this is a simple script for looking at suspicious hwm
prompt on ixora there is a sparse_tables_8.sql which takes also initrans etc. into account
prompt BASED ON LAST STATISTICS !!
prompt Dik Pater
prompt '***********************************************************************************'
select d.owner
, d.table_name
-- , d.blocks
-- , d.empty_blocks
, d.blocks*p.value/1024/1024 hwm_MB
, d.num_rows*avg_row_len/1024/1024 rows_length_MB
, decode(
100-100/(
(d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
,0
,d.blocks*p.value
,(d.num_rows*avg_row_len)
)
),0,100,
100-100/(
(d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
,0
,d.blocks*p.value
,(d.num_rows*avg_row_len)
)
)
) percentage_waisted
, decode(to_char(num_rows),'0','!! '||num_rows||' !!!',to_char(num_rows)) records
, sel_index.*
from dba_tables d
, v$parameter p
, ( SELECT i.owner, i.index_name, table_owner, table_name, s1.bytes index_omvang, s2.bytes table_omvang , s2.bytes - s1.bytes table_minus_index
FROM dba_indexes i
, dba_segments s1
, dba_segments s2
WHERE i.table_owner= '&OWNER'
AND i.index_type= 'NORMAL'
and s1.owner = i.owner
and s1.segment_name = i.index_name
and s2.owner = i.table_owner
AND s2.segment_name = i.table_name
AND s1.segment_type <> 'TABLE PARTITION'
AND s2.segment_type <> 'TABLE PARTITION' ) sel_index
where p.name = 'db_block_size'
and decode(
100-100/(
(d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
,0
,d.blocks*p.value
,(d.num_rows*avg_row_len)
)
),0,100,
100-100/(
(d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
,0
,d.blocks*p.value
,(d.num_rows*avg_row_len)
)
)
) > &percentage_waisted_allowed
and d.blocks > &min_number_blocks_to_check_on
AND d.BLOCKS > 1 -- no sense in checking empty tables with no blocks allocated
AND sel_index.table_owner = d.owner
and sel_index.table_name = d.table_name
order by d.blocks*p.value/1024/1024 desc
/

Tuesday, April 30, 2013

King of the Netherlands and Oracle software and holidays.

Today Queens day has become King's day in Holland.

But the traditional date of 30 April is left next year, when King's day is on 27 April, the birthday of our king Willem Alexander.
One thing the king of the Netherlands did not know is that this has impact on our holiday programming in Oracle software land ;-)

Some one asked me to fill a calendar with holidays, I decided to do this with an analytical query.

After 2013 the Queens day ( koninginnedag ) has become King's day, I used a case statement for it.

But when is EasterSunday ? Googled around I found oracle plsql code :
With eastersunday I can calculate Whitsun, Ascension Day.

create or replace

function eastersunday(p_year number default to_number(to_char(sysdate,'RRRR')))

return date is

|| Jan Thuis, April 2004

|| Calculate the date of Easter Sunday

|| Gregorian method (any year since 1583) based on algorithm of Oudin

l_eastersunday date;

l_g integer;

l_c integer;

l_d integer;

l_e integer;

l_h integer;

l_k integer;

l_p integer;

l_q integer;

l_i integer;

l_j integer;

l_x integer;

begin

l_g := mod(p_year,19);

l_c := floor(p_year/100);

l_d := l_c - floor(l_c/4);

l_e := floor((8 * l_c + 13)/25);

l_h := mod(l_d - l_e + 19 * l_g + 15 ,30);

l_k := floor(l_h / 28);

l_p := floor(29/(l_h + 1));

l_q := floor((21 - l_g)/11);

l_i := l_h - l_k * (1 - l_k * l_p * l_q);

l_j := mod((p_year + floor(p_year/4 + l_i + 2 - l_d )),7);

l_x := 28 + l_i - l_j;

l_eastersunday := to_date('0103'||p_year,'DDMMYYYY') + l_x -1;

return l_eastersunday;

END EASTERSUNDAY;

With this code I made this holiday query, simply it does this :
- Is it a workday then eerste_werkdag = datum.
- Is not a workday then eerste_werkdag = the next datum where werkdag = Y.

SELECT DATUM,
Werkdag,
CASE
WHEN TO_CHAR(Datum,'DD-MM') = '31-12'
AND Werkdag = 'N'
Then Datum+2
WHEN TO_CHAR(Datum,'DD-MM') = '30-12'
And Werkdag = 'N'
Then Datum+3
ELSE
CASE
WHEN WERKDAG = 'J'
THEN DATUM
ELSE Lead(Hulp Ignore Nulls) Over (Order By Datum)
END
END Eerste_Werkdag ,
OPMERKING
FROM
(SELECT DATUM,
WERKDAG,
CASE
WHEN werkdag = 'J'
THEN datum
ELSE NULL
END HULP,
opmerking
FROM
(SELECT MAIN.DATUM,
TO_CHAR(MAIN.DATUM,'day'),
CASE
WHEN trim(TO_CHAR(MAIN.DATUM,'day')) IN ('saturday', 'sunday' )
OR feestdag.datum IS NOT NULL
THEN 'N'
ELSE 'J'
END WERKDAG ,
OPMERKING
FROM
(SELECT TRUNC( to_date('01-01-'
||'&&JAAR', 'DD-MM-YYYY') + LEVEL -1) DATUM
FROM DUAL
CONNECT BY LEVEL <=
(SELECT DAYS_IN_YEAR
FROM
(SELECT TO_CHAR(TRUNC(input_date, 'YYYY'), 'YYYY') AS "YEAR",
ADD_MONTHS(TRUNC(INPUT_DATE, 'YYYY'), 12) - TRUNC(input_date, 'YYYY') AS days_in_year
FROM
(SELECT ADD_MONTHS(TRUNC(TO_DATE('&&JAAR','YYYY')) -365, +12 * level) INPUT_DATE
FROM dual
CONNECT BY level <= 20
)
) SEL
WHERE SEL.YEAR = TO_CHAR(to_date('&&JAAR','YYYY'),'YYYY')
)
) Main ,
(SELECT *
FROM
(SELECT Datum,
CASE
WHEN Opmerking ='Hemelvaartsdag'
AND TO_CHAR(Datum,'DD-MM') = '05-05'
THEN 'Hemelvaartsdag/Bevrijdingsdag'
ELSE Opmerking
END opmerking,
row_number() Over (Partition BY Datum Order By Datum ) DUBBELFEEST
FROM
( SELECT XXX_EASTERSUNDAY('&&JAAR') DATUM, '1e Paasdag' OPMERKING FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+1, '2e Paasdag' FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+39, 'Hemelvaartsdag' FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+49, '1e Pinksterdag' FROM DUAL
UNION ALL
SELECT XXX_EASTERSUNDAY('&&JAAR')+50, '2e Pinksterdag' FROM DUAL
UNION ALL
SELECT TO_DATE('05-MAY-&&JAAR','DD-MON-YYYY'), 'Bevrijdingsdag' FROM DUAL
UNION ALL
SELECT TO_DATE('25-DEC-&&JAAR','DD-MON-YYYY'), '1e Kerstdag' FROM DUAL
UNION ALL
SELECT TO_DATE('26-DEC-&&JAAR','DD-MON-YYYY'), '2e Kerstdag' FROM dual
UNION ALL
SELECT TO_DATE('01-JAN-&&JAAR','DD-MON-YYYY'), 'Nieuwjaarsdag' FROM dual
UNION ALL
SELECT
CASE
WHEN
&&JAAR > 2013
THEN TO_DATE('27-APR-&&JAAR','DD-MON-YYYY')
ELSE TO_DATE('30-APR-&&JAAR','DD-MON-YYYY')
END ,
'Koning..dag'
FROM Dual
)
)
WHERE Dubbelfeest=1
) FEESTDAG
WHERE main.DATUM = FEESTDAG.DATUM (+)
ORDER BY main.DATUM
)
ORDER BY Datum
)

Monday, January 21, 2013

Cloud Control 12 udm to metric extension

Today I got a question to move udm from gridcontrol 11 to 12c metric extensions.
I used this inventarisation query as as startpoint, when I finished the query I thought that this could be usefull for other oracle dba' as well:

SELECT target_name,
collection_name,
aantal,
SUM(aantal) OVER ( PARTITION BY collection_name ) running_total_by_coll_name,
CASE
WHEN SUM(aantal) OVER ( PARTITION BY collection_name ) > 1
THEN 'POSSIBLE GENERIC METRIC EXTENSION'
ELSE 'UNIQUE METRIC EXTENSION'
END ME_OUTPUT
FROM
(SELECT target_name,
collection_name,
COUNT(*) aantal
FROM mgmt$target_metric_settings
WHERE METRIC_NAME ='SQLUDM'
AND collection_name NOT IN ('UDM_GENERIC_SECURITY_1','UDM_GENERIC_SECURITY_2', 'UDM_GENERIC_BACKUP_CHECK' , 'UDM_GENERIC_UNUSABLE_INDEXES')
GROUP BY target_name,
collection_name
)

Thursday, November 29, 2012

sw ( switching directories on linux)

Sometimes i'm a little tired of cd-ing between directories.

A long time ago I had a sw function in korn shell made by Frans van der Meijs.

But now on linux we use bash.

After a small adjustment it runs on linux - bash as well, it can save time and typeing .

Just make a file called .dests in your $HOME directory.

put in some paths.
cat $HOME/.dests

/tmp
/home
$ORACLE_HOME
$ORACLE_HOME/rdbms/admin
$ORACLE_HOME/network/log
$TNS_ADMIN
$ORA_CRS_HOME

source this function

. $HOME/sw

Contents of the sw file :

DESTS_FILE=$HOME/.dests
sw()
{
index=0
while read LINE
do
    if [ $? -ne 0 ]
    then break ;
    fi
    eval "MENU_ITEM[\${index}]=$LINE"
    index=`expr $index + 1 `
done < $DESTS_FILE
nr_of_dests=$index
if [ $# -eq 1 ]
then
    ANSWER=$1
else
    echo ""
    echo "SW-INFO-01: Destinations"
    echo ""
    index=0
    while [ $index -lt $nr_of_dests ]
    do
      echo " -- ${index}   ${MENU_ITEM[${index}]} --"
      index=`expr $index + 1`
    done
    echo
    echo "Your choice [] "
    read ANSWER
fi
if test -d ${MENU_ITEM[$ANSWER]}
then
    echo "SW-INFO-02: Changing current working directory to ${MENU_ITEM[$ANSWER]} SUCCEEDED"
    cd ${MENU_ITEM[$ANSWER]}
else
echo "SW-ERR-01 : Changing current working directory to ${MENU_ITEM[$ANSWER]} FAILED, DIRECTORY DOES NOT EXIST !"
echo "SW-INFO-03: please clean this entry from $HOME/.dests"
fi
pwd
}
filldest()
{
fc -l -10000 | awk '{print $2,$3}' | grep "cd \/" | sort | uniq | sed 's/cd //'>> $HOME/.dests
}
echo "SW-INFO-00: Use command >> sw << to switch directories ."
echo "SW-INFO-03: Use command >> filldest << to fill $DESTS_FILE ."

Usage :

sw 1
you switch to destination 1
sw 11
you switch to destination 11
sw
It will give you the menu.
Make a choice ....
HAPPY SWITCHING.

Tuesday, August 31, 2010

ORA-30009: Not enough memory for CONNECT BY operation and lotto

SQL> @lotto
SQL> select winnend_getal
2 from (
3 select winnend_getal, count(*)
4 from (
5 select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
6 from
7 ( select level n from dual
8 connect by level <= &aantal_nummers )
9 )
10 group by winnend_getal order by count(*) desc
11 )
12 where rownum <=6
13 /
Enter value for aantal_nummers: 1000000
old 8: connect by level <= &aantal_nummers )
new 8: connect by level <= 1000000 )
select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
*
ERROR at line 5:
ORA-30009: Not enough memory for CONNECT BY operation

Elapsed: 00:00:06.32
SQL> select winnend_getal
2 from (
3 select winnend_getal, count(*)
4 from (
5 select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
6 from
7 (
8 with n(n) as ( select 0
9 from dual
10 union all
11 select n+1 from n
12 where n<=&aantal_nummers
13 )
14 select * from n
15 )
16 )
17 group by winnend_getal order by count(*) desc
18 )
19 where rownum <= 6
20 /
Enter value for aantal_nummers: 1000000
old 12: where n<=&aantal_nummers
new 12: where n<=1000000

WINNEND_GETAL
-------------
35
15
12
9
41
11

6 rows selected.

Elapsed: 00:00:46.28
SQL>

I found this on asktom from a reply of user Sokrates ....

BTW Hope someone will win the lotto ....

Monday, August 30, 2010

filsystem_ioORA-56708

In DBCONSOLE I received the following message on a hpux 11.31 machine while trying to run the io calibrate feature of 11g.
DBCONSOLE> Information

The following Initialization parameters need to be set to the specified
value in order to run the I/O calibration tool successfully:
filesystemio_options - ASYNCH or SETALL
This error is a bit misleading...because filesystemio_options is already set, the cause is that the async io is disabled at the operating level......

cat calibrate_io.sql
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (8, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/

SQL> @"calibrate_io.sql"
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 456
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: at line 7

SQL> show parameter asyn

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
disk_asynch_io boolean
TRUE
tape_asynch_io boolean
TRUE
SQL> show parameter filesys

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
filesystemio_options string
SETALL
SQL> select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name='Data File' or filetype_name='Temp File');

NAME ASYNCH_IO
------------------------------ ---------------------------

/oracle/dikpater/db/apps_st/da ASYNC_OFF
ta11g/DIKPATEROCP/datafile/o1_
mf_undotbs1_6677o0hr_.dbf

Tuesday, August 24, 2010

ora-27492

job_queue_processes checked it was 1000 ;

exec dbms_ijob.set_enabled(true) ;

Solved the problem.

11gR2 SP2-1503: Unable to initialize Oracle call interface

$ which sqlplus
/oracle/system1/db/tech_st/11.2.0/bin/sqlplus
ibes01@system1: sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
****************************************************************************
The cause is that the variable ORA_TZFILE was pointing to a 10g environment.
****************************************************************************
$ set | grep TZ
ORA_TZFILE=/oracle/system1/db/tech_st/10.2.0/oracore/zoneinfo/timezlrg.dat
TZ=MET-1METDST

$ unset ORA_TZFILE
$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 24 08:58:55 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name:

Monday, August 23, 2010

imp hang import hang 25475

If your import doesn't continues
and current statement is :
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
then you might be using the wrong client s/w.
In our case we used 9206 instead of 9208. The import hangs.
Thanks to Frank van Bortel who run into this case.