DDL

You Cannot Resize a DataFile in “ALTER TABLESPACE” Except …

Posted on
According to SQL syntax of Oracle database, you can’t resize the data files by using ALTER TABLESPACE except the tablespace was created as BIGFILE, but we know most of tablespaces are created as SMALLFILE. So if you need to resize a normal data file, you’d better to use ALTER DATABASE. SQL> alter database datafile ‘/oracle/oradata/user01.dbf’ […]
DDL

The Default Values in “ALTER SYSTEM SET” Statement

Posted on
Without declaring SCOPEIf you do not specify this clause, then:If a server parameter file was used to start up the database, then BOTH is the default.If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.Started bySCOPEspfileBOTHpfileMEMORYWithout declaring SIDIf you do […]
PL/SQL

External Procedures May Consume a Lot of Memory

Posted on
External procedures are very special procedures stored outside of the database, and it can be coded by C or Java language. Since the external libraries were executed on OS-level, their behaviors are not managed and bounded by database anymore, so it could become a security leak. The users who have been granted the privilege to […]
Session

How to Compose Kill Blocking Sessions Statements at a Time

Posted on
After the blocking sessions are identified, DBA should notify the session owners and leave them a chance to exit sessions gracefully by themselves. If things go worse, DBA must terminate these sessions right away. Killing Blocking Sessions For Separate Nodes Here is a sample SQL which can both identify and compose kill session immediate statements […]
PL/SQL

How to Identify Blocking Sessions

Posted on
Here I introduce a sample of PL/SQL code to monitor the blocking sessions every N minutes and output in a formatted report.First of all, edit the sql script.[oracle@primary01 ~]$ vi CheckBlockingSessions.sqlSET SERVEROUTPUT ON;ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;DECLARE  V_MAX_COUNT NUMBER := 4;  V_WAIT_MIN  NUMBER := 5;  CURSOR C_BLOCKING  IS    SELECT BLOCKER.INST_ID,      BLOCKER.SID BLOCKER_SID […]