Manage/kill sessions in Oracle

A connection is a network link connection to the database. A connection can have 0, one or many sessions, but the normal situation is only one session per connection.
The tasks in a session are executed by processes. But a process does not have to be dedicated to a specific session.

If you want to see the current active sessions, select the v$session table:

Advertisements

Increase tablespace size in Oracle

The database filenames are stored in the table dba_data_files

SQL> describe dba_data_files;             
 Name             NULL?    Type
---------------- ----- ---------------------------
 FILE_NAME              VARCHAR2(513)
 FILE_ID                NUMBER
 TABLESPACE_NAME        VARCHAR2(30)
 BYTES                  NUMBER
 BLOCKS                 NUMBER
 STATUS                 VARCHAR2(9)
 RELATIVE_FNO           NUMBER
 AUTOEXTENSIBLE         VARCHAR2(3)
 MAXBYTES               NUMBER
 MAXBLOCKS              NUMBER
 INCREMENT_BY           NUMBER
 USER_BYTES             NUMBER
 USER_BLOCKS            NUMBER
 ONLINE_STATUS          VARCHAR2(7)

We can see the files datasize in MB with:

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 Mbytes,AUTOEXTENSIBLE from dba_data_files order by TABLESPACE_NAME;

We can use the tables dba_data_files and dba_free_space to know the size and the free space of the tablespaces:

SQL> select     a.TABLESPACE_NAME,
        round(a.BYTES/(1024*1024),2) mbytes_total,
        round(b.BYTES/(1024*1024),2) mbytes_free,
        round(b.largest/(1024*1024),2) largest,
        round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from    
        (
                select  TABLESPACE_NAME,
                        sum(BYTES) BYTES 
                from    dba_data_files 
                group   by TABLESPACE_NAME
        )
        a,
        (
                select  TABLESPACE_NAME,
                        sum(BYTES) BYTES ,
                        max(BYTES) largest 
                from    dba_free_space 
                group   by TABLESPACE_NAME
        )
        b
where   a.TABLESPACE_NAME=b.TABLESPACE_NAME;

If we want to add 10GB to a tablespace then:

ALTER TABLESPACE APP_LOG_INDX ADD DATAFILE '/u04/oradata/XXXXX/APP_log_indx_10.dbf' SIZE 10000M AUTOEXTEND OFF;

For more info please visit:
Changing Datafile Size

You still can have problems with the disk usage due to the archived logs.
But you can easily remove them with the rman tool:

$ rman TARGET /
RMAN> delete archivelog all;

PL/SQL Procedure for automatic INSERT/UPDATE

Imagine we have a table like the following one:

CREATE TABLE MYUPDATE_TABLE
(
  OUID    NUMBER(10)                   NOT NULL,
  SERVICE NUMBER(10)                   NOT NULL,
  DAY     DATE                         NOT NULL,
  NUM	  NUMBER(10)                   NOT NULL
)

This table stores the number of events that a SERVICE has in a given DAY in the column NUM.

Most of the times we will want just to update the number stored at NUM for the given date and servive given by the columns DAY and SERVICE.
But we only can make an update if that row exists, if not we must to create it with an INSERT.

This process can be donde by checking first if the desired row exists. If not exists insert this NUM value into the table for the given DAY and SERVICE.
If it exists then get the number NUM and add it the new desired value.
This is done with the following Oracle procedure:

create or replace procedure MYUPDATE_TABLE(nService  NUMBER,nDay  DATE, nNUM  NUMBER) is
sOUID  NUMBER(19);
oldNUM  NUMBER(19);
cursor c1 is SELECT OUID, NUM FROM VE_EMLOGSERVICE WHERE  SERVICE=nService AND DAY=nDAY;
begin     
    open c1;
    fetch c1 into sOUID,oldNUM;
    if (c1%notfound) then
      INSERT INTO MY_TABLE(OUID,SERVICE,DAY) VALUES (MY_SEQ.nextval,nService,nDay,nNUM);
      commit;
    else
       UPDATE VE_EMLOGSERVICE SET  NUM = oldNUM+nNUM WHERE OUID = sOUID;
       commit;
    end if;
    close c1;
end;
/