пятница, 23 октября 2015 г.

Update BLOB

set define off
DECLARE
   vb1 CLOB :=
'long text';
   vb2 CLOB :=
                'long text';
   vb3 CLOB :=
                'long text';
   vb CLOB := vb1||vb2||vb3;
   vb_upd BLOB;
FUNCTION convert_clob_to_blob (pclob CLOB) RETURN BLOB
IS
    lblob              BLOB;
    ldest_offset    INTEGER := 1;
    lsource_offset INTEGER := 1;
    llang_context INTEGER := dbms_lob.default_lang_ctx;
    lwarning        INTEGER := dbms_lob.warn_inconvertible_char;
BEGIN
    dbms_lob.createtemporary (lblob, TRUE);

    dbms_lob.converttoblob (dest_lob    => lblob,
                                              src_clob    => pclob,
                                              amount      => dbms_lob.lobmaxsize,
                                              dest_offset => ldest_offset,
                                              src_offset => lsource_offset,
                                              blob_csid   => dbms_lob.default_csid,
                                              lang_context=> llang_context,
                                              warning     => lwarning);
    RETURN lblob;
END convert_clob_to_blob;
BEGIN
   vb_upd := convert_clob_to_blob(vb);
   UPDATE tab SET BLOB_COL = vb_upd
   WHERE ...;
   COMMIT;
END;

/

Privilegies

It is useful to find out full hierarchy of privileges by grantee. I have attached the scripts to do it.

privs_role_all_by_grantee.sql
privs_sys_all_by_grantee.sql
privs_tab_all_by_grantee.sql

Startup history

set pagesize 3000
set lines 300
col HOST_NAME format a30
col PLATFORM_NAME format a30
col STARTUP_TIME format a21
col DB_NAME format a9
select DBID
     , TO_CHAR(STARTUP_TIME, 'yyyy.mm.dd hh24:mi:ss') STARTUP_TIME
     , HOST_NAME
     , DB_NAME
     , PLATFORM_NAME
from DBA_HIST_DATABASE_INSTANCE
order by STARTUP_TIME;

вторник, 6 августа 2013 г.

What is the diff in 2 explain plans: diff_plan_cursor

0) Generate the diff plan report
We have sql_id gu7rvn4s4rb0m, and 2 child cursors 0 and 2
variable v clob
set serveroutput on
set autoprint on
set lines 150
exec :v := dbms_xplan.diff_plan_cursor('gu7rvn4s4rb0m', 0, 2);
http://stads59.us.oracle.com:8080/orarep/plandiff/all?task_id=119&format=html&method=qbreg

PL/SQL procedure successfully completed.

V
--------------------------------------------------------------------------------
TASK_119

Display the report, 2 options:
1) Display the report via XML DB
--open http port if needed, if you are on test/dev environment
exec dbms_xdb.SETHTTPPORT(8080);
--replacing url by local hostname
http://<db host name>:8080/orarep/plandiff/all?task_id=119&format=html&method=qbreg

2) Display in sqlplus
--report in text format, or replace format=html or format=xml
set define off;
set long 999999999
set pagesize 3000
select dbms_report.get_report(
        '/orarep/plandiff/all?task_id=119&format=text&method=qbreg')
from dual;

DBMS_REPORT.GET_REPORT('/ORAREP/PLANDIFF/ALL?TASK_ID=119&FORMAT=TEXT&METHOD=QBRE
--------------------------------------------------------------------------------
General Information
-------------------------------------------------------------------------------

Task Information:                      Workload Information:
------------------------------------- -------------------------------------
Task Name    : TASK_119
Task Owner   : SYS
Description : diff_plan_cursor

Report Details: SQL Plan Comparison Query Block Diff
------------------------------------------------
| Query Block | SQL Plan 1 | SQL Plan 2 | Diff |
------------------------------------------------

Join Order Diff
-----------------------------------------
| Query Block | SQL Plan 1 | SQL Plan 2 |
-----------------------------------------

Join Method Diff
---------------------------------------------------------
| Query Block | Table to Join | SQL Plan 1 | SQL Plan 2 |
---------------------------------------------------------
| SEL$1       | "T2"@"SEL$1" | HASH       | MERGE      |
---------------------------------------------------------

Plan Id          : 325
Plan Hash Value : 2795996736

----------------------------------------
| Id | Operation            | Name    |
----------------------------------------
|   0 | SELECT STATEMENT     |         |
| * 1 |   HASH JOIN          |         |
|   2 |    TABLE ACCESS FULL | TEST100 |
|   3 |    TABLE ACCESS FULL | TEST100 |
----------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("T1"."ID"="T2"."ID")

Plan Id          : 326
Plan Hash Value : 216926072

-----------------------------------------
| Id | Operation           | Name      |
-----------------------------------------
|   0 | SELECT STATEMENT    |           |
|   1 |   MERGE JOIN        |           |
|   2 |    INDEX FULL SCAN | TEST100_I |
| * 3 |    SORT JOIN        |           |
|   4 |     INDEX FULL SCAN | TEST100_I |
-----------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("T1"."ID"="T2"."ID")
* 3 - filter("T1"."ID"="T2"."ID")

-----------------------------------------------------------------
| Diff | Transformation | SQL Plan 1 | SQL Plan 2 | Query block |
-----------------------------------------------------------------
| *    | . PARSER       | Yes        | Yes        | . SEL$1     |
-----------------------------------------------------------------

четверг, 13 сентября 2012 г.

win: Oracle Service not starting

C:\WINDOWS\system32>sc start OracleServiceTESTDB11G_stby
[SC] StartService FAILED 1058:

The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.
C:\WINDOWS\system32>sc qc OracleServiceTESTDB11G_stby
[SC] QueryServiceConfig SUCCESS
...
        START_TYPE         : 4   DISABLED
...
C:\WINDOWS\system32>sc config OracleServiceTESTDB11G_stby start= demand
[SC] ChangeServiceConfig SUCCESS

C:\WINDOWS\system32>sc qc OracleServiceTESTDB11G_stby
[SC] QueryServiceConfig SUCCESS
...
        START_TYPE         : 3   DEMAND_START
...
C:\WINDOWS\system32>sc start OracleServiceTESTDB11G_stby

SERVICE_NAME: OracleServiceTESTDB11G_stby
        TYPE               : 10 WIN32_OWN_PROCESS
        STATE              : 2 START_PENDING
                                (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 0 (0x0)
        SERVICE_EXIT_CODE : 0 (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x7d0
        PID                : 4204
        FLAGS              :

C:\WINDOWS\system32>sc query OracleServiceTESTDB11G_stby

SERVICE_NAME: OracleServiceTESTDB11G_stby
        TYPE               : 10 WIN32_OWN_PROCESS
        STATE              : 4 RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0 (0x0)
        SERVICE_EXIT_CODE : 0 (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

среда, 12 сентября 2012 г.

Win7 stuck on welcome screen

1) Reboot, press F8, choose safe mode
2) If started -> cmd, msconfig, Check Selective startup, uncheck Load startup items, go to tab Services - > check Hide all Microsoft services, Disable All
3) Reboot normally, set needed services again with msconfig

суббота, 12 мая 2012 г.

Win: not typing 1st quotation mark

Issue is in keyboard United States-International
Solution:
change keyboard layout

вторник, 31 января 2012 г.

rac: patch - rolling ?

opatch query <patch dir> | grep rolling

--example output
Patch is a rolling patch: true

понедельник, 30 января 2012 г.

general: how to create service in none-RAC database

-service_names init parameter (implicitly)
-DBMS_SERVICE

понедельник, 23 января 2012 г.

rac: shutdown all instances immediate

--

-- Check shutdown option for each instance (default - immediate)

--

oracle@rac1:~> crs_stat -p ora.rac.rac1.inst | grep -i stop_mode
USR_ORA_STOP_MODE=immediate
--

-- Stop immediate (if USR_ORA_STOP_MODE<>immediate)

--

srvctl stop database -d <db_name> -o immediate

пятница, 13 января 2012 г.

rac: where is ocr backup

ocrconfig -showbackup

воскресенье, 1 января 2012 г.

General: real undo retention

V$UNDOSTAT

TUNED_UNDORETENTION

четверг, 29 декабря 2011 г.

rac: srvctl / vipca error libpthread.so.0: cannot open shared object file:

Unset LD_ASSUME_KERNEL in srvctl / vipca

rac: asm instance startup : ORA-27504: IPC error creating OSD context

Set LD_LIBRARY_PATH=$ORACLE_HOME/lib

вторник, 27 декабря 2011 г.

rac: vipca No default gateway error

Set FAIL_WHEN_DEFAULTGW_NOT_FOUND=0 IN CRS_HOME/bin/racgvip

rac: crs version

--on current node
crsctl query crs softwareversion
--lowest version (all nodes)
crsctl query crs activeversion

понедельник, 26 декабря 2011 г.

General: Debug Oracle Installer

./runInstaller -logLevel trace

-debug /-J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2/

среда, 9 ноября 2011 г.

Linux: OpenSUSE - ssh timeout

trace for ssh
linux-nn5t:~ # strace -p 5117
Process 5117 attached - interrupt to quit
connect(3, {sa_family=AF_INET, sin_port=htons(22), sin_addr=inet_addr("192.168.55.100")}, 16) = -1 ETIMEDOUT (Connection timed out)
close(3) = 0
write(2, "ssh: connect to host 192.168.55."..., 67) = 67
exit_group(255) = ?
Process 5117 detached

Solution:

Stop firewall

/sbin/SuSEfirewall2 off

вторник, 8 ноября 2011 г.

Linux: Oracle VirtualBox on OpenSUSE 11.4

aus@linux-nn5t:~/Desktop> su -
Password:
linux-nn5t:~ # /etc/init.d/vboxdrv setup
Stopping VirtualBox kernel modules done
Uninstalling old VirtualBox DKMS kernel modules done
Trying to register the VirtualBox kernel modules using DKMS
Error! Your kernel headers for kernel 2.6.37.1-1.2-default cannot be found at
/lib/modules/2.6.37.1-1.2-default/build or /lib/modules/2.6.37.1-1.2-default/source.
failed
(Failed, trying without DKMS)
Recompiling VirtualBox kernel modules failed
(Look at /var/log/vbox-install.log to find out what went wrong)

--install dkms http://mirror.bjtu.edu.cn/packman/suse/openSUSE_Tumbleweed/Extra/noarch/dkms-2.1.1.2-0.pm.1.1.noarch.rpm

linux-nn5t:~ # which dkms
/usr/sbin/dkms
linux-nn5t:~ # /etc/init.d/vboxdrv setup
Stopping VirtualBox kernel modules done
Uninstalling old VirtualBox DKMS kernel modules done
Trying to register the VirtualBox kernel modules using DKMS
Error! Your kernel headers for kernel 2.6.37.1-1.2-default cannot be found at
/lib/modules/2.6.37.1-1.2-default/build or /lib/modules/2.6.37.1-1.2-default/source.
failed
(Failed, trying without DKMS)
Recompiling VirtualBox kernel modules failed
(Look at /var/log/vbox-install.log to find out what went wrong)
linux-nn5t:~ # uname -r
2.6.37.1-1.2-default
linux-nn5t:~ # cd /lib/modules
linux-nn5t:/lib/modules # ls -la
total 36
drwxr-xr-x 7 root root 4096 Nov 7 06:23 .
drwxr-xr-x 19 root root 12288 Nov 7 05:40 ..
drwxr-xr-x 6 root root 4096 Nov 7 05:05 2.6.37.1-1.2-default
drwxr-xr-x 3 root root 4096 Nov 7 06:01 2.6.37.6-0.7-default
drwxr-xr-x 3 root root 4096 Nov 7 06:01 2.6.37.6-0.7-pae
drwxr-xr-x 3 root root 4096 Nov 7 06:01 2.6.37.6-0.7-trace
drwxr-xr-x 4 root root 4096 Nov 7 06:23 2.6.37.6-0.7-xen
linux-nn5t:/lib/modules # ls -la 2.6.37.1-1.2-default
total 2776
drwxr-xr-x 6 root root 4096 Nov 7 05:05 .
drwxr-xr-x 7 root root 4096 Nov 7 06:23 ..
drwxr-xr-x 12 root root 4096 Mar 2 2011 kernel
-rw-r--r-- 1 root root 650566 Nov 7 05:05 modules.alias
-rw-r--r-- 1 root root 630327 Nov 7 05:05 modules.alias.bin
-rw-r--r-- 1 root root 2912 Feb 22 2011 modules.builtin
-rw-r--r-- 1 root root 4205 Nov 7 05:05 modules.builtin.bin
-rw-r--r-- 1 root root 327336 Nov 7 05:05 modules.dep
-rw-r--r-- 1 root root 466072 Nov 7 05:05 modules.dep.bin
-rw-r--r-- 1 root root 248 Nov 7 05:05 modules.devname
-rw-r--r-- 1 root root 113584 Feb 22 2011 modules.order
-rw-r--r-- 1 root root 131 Nov 7 05:05 modules.softdep
-rw-r--r-- 1 root root 263785 Nov 7 05:05 modules.symbols
-rw-r--r-- 1 root root 336527 Nov 7 05:05 modules.symbols.bin
drwxr-xr-x 2 root root 4096 Mar 2 2011 systemtap
drwxr-xr-x 2 root root 4096 Mar 2 2011 updates
drwxr-xr-x 2 root root 4096 Mar 2 2011 vdso

--install kernel 2.6.37.1-1.2.2

linux-nn5t:/lib/modules # rpm -qa | grep kernel
kernel-default-2.6.37.1-1.2.2.i586
kernel-desktop-devel-2.6.37.1-1.2.2.i586
kernel-xen-2.6.37.6-0.7.1.i586
kernel-default-devel-2.6.37.1-1.2.2.i586
kernel-firmware-2.6.38-1.2.1.noarch
kernel-xen-devel-2.6.37.1-1.2.2.i586
kernel-devel-2.6.37.1-1.2.2.noarch

linux-nn5t:/lib/modules # ls -la 2.6.37.1-1.2-default
total 2776
drwxr-xr-x 6 root root 4096 Nov 8 01:39 .
drwxr-xr-x 9 root root 4096 Nov 8 01:39 ..
lrwxrwxrwx 1 root root 44 Nov 8 01:39 build -> /usr/src/linux-2.6.37.1-1.2-obj/i386/default
drwxr-xr-x 12 root root 4096 Mar 2 2011 kernel
-rw-r--r-- 1 root root 650566 Nov 7 05:05 modules.alias
-rw-r--r-- 1 root root 630327 Nov 7 05:05 modules.alias.bin
-rw-r--r-- 1 root root 2912 Feb 22 2011 modules.builtin
-rw-r--r-- 1 root root 4205 Nov 7 05:05 modules.builtin.bin
-rw-r--r-- 1 root root 327336 Nov 7 05:05 modules.dep
-rw-r--r-- 1 root root 466072 Nov 7 05:05 modules.dep.bin
-rw-r--r-- 1 root root 248 Nov 7 05:05 modules.devname
-rw-r--r-- 1 root root 113584 Feb 22 2011 modules.order
-rw-r--r-- 1 root root 131 Nov 7 05:05 modules.softdep
-rw-r--r-- 1 root root 263785 Nov 7 05:05 modules.symbols
-rw-r--r-- 1 root root 336527 Nov 7 05:05 modules.symbols.bin
lrwxrwxrwx 1 root root 27 Nov 8 01:39 source -> /usr/src/linux-2.6.37.1-1.2
drwxr-xr-x 2 root root 4096 Mar 2 2011 systemtap
drwxr-xr-x 2 root root 4096 Mar 2 2011 updates
drwxr-xr-x 2 root root 4096 Mar 2 2011 vdso
linux-nn5t:/lib/modules # /etc/init.d/vboxdrv setup
Stopping VirtualBox kernel modules done
Uninstalling old VirtualBox DKMS kernel modules done
Trying to register the VirtualBox kernel modules using DKMS
Error! Bad return status for module build on kernel: 2.6.37.1-1.2-default (i586)
Consult the make.log in the build directory
/var/lib/dkms/vboxhost/4.1.6/build/ for more information.

DO YOU HAVE gcc INSTALLED???
failed
(Failed, trying without DKMS)
Recompiling VirtualBox kernel modules failed
(Look at /var/log/vbox-install.log to find out what went wrong)
linux-nn5t:/lib/modules # rpm -qa | grep gcc
libgcc45-4.5.1_20101208-9.8.i586
libstlport_gcc4-4.6.2-9.1.i586
--install gcc
linux-nn5t:/lib/modules # /etc/init.d/vboxdrv setup
Stopping VirtualBox kernel modules done
Uninstalling old VirtualBox DKMS kernel modules done
Trying to register the VirtualBox kernel modules using DKMS done
Starting VirtualBox kernel modules done
linux-nn5t:/lib/modules # ^A

вторник, 11 октября 2011 г.

Windows: Get details of service

-- Find service name
sc query state= all | findstr OracleService
-- Get details
sc qc OracleServiceORCL

Страницы

пятница, 23 октября 2015 г.

вторник, 6 августа 2013 г.

четверг, 13 сентября 2012 г.

среда, 12 сентября 2012 г.

суббота, 12 мая 2012 г.

вторник, 31 января 2012 г.

понедельник, 30 января 2012 г.

понедельник, 23 января 2012 г.

пятница, 13 января 2012 г.

воскресенье, 1 января 2012 г.