Skip to content
April 17, 2018 / doganay

HOW TO CONNECT TO ORACLE USING PROXY USER

#proxy connect
$ sqlplus / as sysdba
SQL> alter user user1 GRANT CONNECT THROUGH dba1;
SQL> conn dba1[user1]
SQL> sho user
user1

#to revoke
$ sqlplus / as sysdba
SQL> alter user user1 revoke CONNECT THROUGH dba1;
Advertisements
March 30, 2018 / doganay

HOW TO SEND CTRL^C TO ANY SESSION

In 11g we set 10237 event:

exec dbms_system.set_ev( &sid, &serial, 10237, 1, '');

But do not forget to clear that event immediately:

exec dbms_system.set_ev( &sid, &serial, 10237, 0, '');

If you do not clear, session continues to get error:

*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

In 18c it’s much more easier:

-- Current SQL in session on this instance.
ALTER SYSTEM CANCEL SQL '&sid, &serial';

There are also some other alternatives:

-- Current SQL in session on instance with INST_ID = 1.
ALTER SYSTEM CANCEL SQL '&sid, &serial, @1';

-- Specified SQL in session on this instance.
ALTER SYSTEM CANCEL SQL '&sid, &serial, 84djy3bnatbvq';

-- Specified SQL in session on instance with INST_ID = 1.
ALTER SYSTEM CANCEL SQL '&sid, &serial, @1, 75fqr2nadeydf';
March 30, 2018 / doganay

HOW TO DISPLAY OS VARIABLE in ORACLE


12c

select sys_context('USERENV','ORACLE_HOME') as OS_VAR_OH from dual


11g

create or replace function print_os_var (os_var_name in varchar2) return clob
as
v_var_os clob;
begin
dbms_system.get_env(os_var_name, v_var_os);
return v_var_os;
end;
/

14:16:27 SYS@DBAT1124> select print_os_var('ORACLE_HOME') from dual;

PRINT_OS_VAR
------------
/u01/app/oracle/product/11204

You may get any parameter which has set before you startup the instance. For example:

$ export AAA="abc"

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 30 14:52:15 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size 2257352 bytes
Variable Size 2046823992 bytes
Database Buffers 1073741824 bytes
Redo Buffers 17203200 bytes
Database mounted.
Database opened.

14:54:27 SYS@DBAT1124> select print_os_var('AAA') from dual;

PRINT_OS_VAR
------------
abc

 

March 30, 2018 / doganay

SELECT WHOLE PARTITION WHICH MATCH SPECIFIC DATA

 

14:38:20 SYS@DBAT1124> create table t1 (a date)
14:38:28 2 PARTITION BY RANGE(a)
14:38:31 3 (
14:38:31 4 PARTITION p1 VALUES LESS THAN (TO_DATE('01.02.2018', 'DD-MM-YYYY')),
14:38:34 5 PARTITION p2 VALUES LESS THAN (TO_DATE('01.03.2018', 'DD-MM-YYYY'))
14:38:35 6 );

Table created.

14:38:36 SYS@DBAT1124> insert into t1 values (to_date('20.01.2018','dd.mm.yyyy'));

1 row created.

14:39:13 SYS@DBAT1124> insert into t1 values (to_date('20.02.2018','dd.mm.yyyy'));

1 row created.

14:39:27 SYS@DBAT1124> commit;

Commit complete.

14:39:29 SYS@DBAT1124> select * from t1 partition for (date '2018-01-15');

A
----------
2018/01/20

1 row selected.

--subpartition example:
select * from t2 subpartition for(1,'subpart1');

March 30, 2018 / doganay

$GI_HOME/crs/install/roothas.pl -unlock RUNNING SLOW


Problem:

We experienced that

$GI_HOME/crs/install/roothas.pl -unlock

which is a part of GI psu is slow.

Cause:

That issue may be related with too many ASM audit logs.

Solution:

Delete unnecessary ASM audit logs:

rm /u01/app/11.2.0.4/grid/rdbms/audit/*.aud

Also, you can add crontab entry to schedule it:

0 * * * * rm /u01/app/11.2.0.4/grid/rdbms/audit/*.aud

 

March 30, 2018 / doganay

X$KQLFXPL FULL TABLE SCAN WHEN SELECTING V$SQL_PLAN


Problem:

A friend reported this runs slow on 11.2.0.4:
select * from v$sql_plan where sql_id=trim(’83gvu1ba2x95y ‘);

Plan was:

OPERATION           OBJECT_NAME        
-----------------   ------------
SELECT STATEMENT                       
 FIXED TABLE FULL   X$KQLFXPL   

In a normal case (select * from v$sql_plan where sql_id=’83gvu1ba2x95y’) must be:

                 
OPERATION                  OBJECT_NAME        
-------------------------  -----------------
SELECT STATEMENT                              
 FIXED TABLE FIXED INDEX   X$KQLFXPL (ind:4)


Solution:

select /*+ cursor_sharing_exact */ * from v$sql_plan where sql_id=trim('83gvu1ba2x95y ');

 

March 30, 2018 / doganay

HOW TO ENABLE SQLNET TRACE

##client sqlnet.ora
#DIAG_ADR_ENABLED is important, not documented much:
DIAG_ADR_ENABLED= OFF
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=/logdirectory
TRACE_UNIQUE_CLIENT=true
TRACE_FILE_CLIENT=kerb_client

 

March 30, 2018 / doganay

HOW TO EXECUTE SQL USING INPUT BINDS

DECLARE
v_sql    VARCHAR2 (32000);
v_sql_id VARCHAR2 (30) := 'f3yfg50ga0r8n';
v_b1     VARCHAR2(100) := 'bind1';
v_b2     VARCHAR2(100) := 'bind2';
BEGIN
BEGIN
SELECT sql_fulltext
INTO v_sql
FROM gv$sql
WHERE sql_id = v_sql_id AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
SELECT sql_text
INTO v_sql
FROM dba_hist_sqltext
WHERE sql_id = v_sql_id AND ROWNUM = 1;
END;

EXECUTE IMMEDIATE v_sql
USING v_b1, v_b2;
END;
/

 

March 30, 2018 / doganay

ORA-38818: illegal reference to editioned object


Problem:

In oracle 12c, when you want to use sysman grid objects in a procedure or package, you get this error:

[Error] ORA-38818 (4: 41): PL/SQL: ORA-38818: illegal reference to editioned object .
ORA-38818: illegal reference to editioned object SYSMAN.MGMT_TARGETS


Solution:

Use ctas in the beginning of sp, and use this flat table instead of sysman views:

create table myuser.mgmt_targets as select target_type, target_name from sysman.mgmt_targets;

create or replace procedure1 is

begin

execute immediate 'delete myuser.mgmt_targets';

execute immediate 'insert /*+ append */ into myuser.mgmt_targets select target_type, target_name from sysman.mgmt_targets';

execute immediate 'commit';

..

..

end;

/

 

March 30, 2018 / doganay

RMAN-10014: PL/SQL error 306 on line 2620 column 18: wrong number or types of arguments in call to ‘CHECKDATAFILEFORSTANDBY’


Problem:

RMAN> resync catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 03/26/2018 10:29:11
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 2620 column 18: Statement ignored
RMAN-10014: PL/SQL error 306 on line 2620 column 18: wrong number or types of arguments in call to 'CHECKDATAFILEFORSTANDBY'


Solution:

RMAN> upgrade catalog;

recovery catalog owner is MY_CATALOG_OWNER
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 12.02.00.01
DBMS_RCVMAN package upgraded to version 12.02.00.01
DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> resync catalog;