How to give privilege to kill session without giving ‘alter system’ privilege

Step 1 : Create the procedure with sys user create or replace procedure kill_session (pn_sid number ,pn_serial number) as lv_user varchar2(30); begin select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial; if lv_user is not null and lv_user in ('ERMANNKARA') then execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||''''; else raise_application_error(-20000,'Attempt … Continue reading How to give privilege to kill session without giving ‘alter system’ privilege

Advertisements

How to check character set in Oracle

One of the requirements for samePage is that the character set should be UTF-8. You can either check with your DBA or run the following SQL to determine whether your database character set is UTF-8.    SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; It should return the value AL32UTF8. Another option is to … Continue reading How to check character set in Oracle

Why parallel expdp gives unable to write error DATAPUMP With PARALLEL > 1 ON 11.2 RAC

hello, Problem: Datapump on 11.2 RAC with PARALLEL > 1  hits the following errors. ORA-31693: Table data object "IIR_USER"."IDS_00_INUSE" failed to load/unload and is being skipped due to error: ORA-31617: unable to open dump file "/export/expıırdb_04.dmp" for write ORA-19505: failed to identify file "/export/expıırdb_04.dmp" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or … Continue reading Why parallel expdp gives unable to write error DATAPUMP With PARALLEL > 1 ON 11.2 RAC

Oracle 11g: DDL WAIT Option (DDL_LOCK_TIMEOUT)

Hello,In Oracle 11g, the new initialization parameter DDL_LOCK_TIMEOUT controls the duration (in seconds) for which a DDL statement will wait for a DML lock.The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock. The default value of zero indicates a status of NOWAIT. SQL> show parameter DDL_LOCK_TIMEOUT; NAME … Continue reading Oracle 11g: DDL WAIT Option (DDL_LOCK_TIMEOUT)

ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

hello, problem : ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device   Linux-x86_64 Error: 28: No space left on device Additional information: 4 Additional information: 981795 Additional information: -1 ORA-31693: Table data object "EBDYSLIVE"."VeriTarihcesi":"SYS_P726" failed to load/unload and is being skipped due to error: ORA-31644: unable to position to block number 842047 … Continue reading ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

How to check Database Size, Used and Free Space in a Database

  select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile … Continue reading How to check Database Size, Used and Free Space in a Database

RESIZING DATAFILE IN ASM

Re-sizing  datafiles in ASM is very simple. +ASM oracle@akyaka:/home/oracle>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 26 14:53:56 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, Automatic Storage Management, OLAP, Data Mining and Real Application … Continue reading RESIZING DATAFILE IN ASM