Neuronworks Blog, WebMethods, Oracle, Bea, Java Dev2Dev, Arch2Arch Neuronworks Make IT Easy, Let's Play the Game » Oracle

Archive

Posts Tagged ‘Oracle’

Me-Resize ukuran Temporary Tablespace

December 29th, 2008
3 comments

Suatu ketika saya kaget melihat ukuran temporary tablespace saya yang mencapai 2 GB, kenapa bisa sebesar ini, ternyata setelah di lihat memang di konfigurasinya autoextends, ya sudah akhirnya saya mencoba untuk meresize ukuran tersebut.

berikut kronologi melakukan resize temp tablesapce :

langkah pertama yang saya lakukan adalah :

SQL> ALTER DATABASE tempfile '/app/oradata/NEURON/temp01.dbf' resize 250M;
ALTER DATABASE tempfile '/app/oradata/NEURON/temp01.dbf' resize 250M
*
ERROR AT line 1:
ORA-03297: FILE contains used data beyond requested RESIZE VALUE

ooo , ternyata error berarti tidak bisa menggunakan cara ini, cara berikutnya adalah Drop dan Create ulang tablesapce temp

berikut langkah-langkahnya :

SQL> DROP TABLESPACE temp;
 
TABLESPACE dropped.

Create ulang temp tablespace

SQL> CREATE TEMPORARY TABLESPACE TEMP
  2  TEMPFILE '/app/oradata/NEURON/temp01.dbf' SIZE 500M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
 
TABLESPACE created.

perintah diatas akan berhasil jika, oracle yang kita gunakan adalah oracle 8i atau oracle 9i dengan temp tablespace bukan default temporary tablesapace.

SQL> DROP TABLESPACE temp;
DROP TABLESPACE temp
*
ERROR AT line 1:
ORA-12906: cannot DROP DEFAULT TEMPORARY TABLESPACE

jika kita ingin merubah ukuran temp tablespace yang menjadi default temp tablespace kita harus meggunakan menggunakan cara berikut :

misalkan tablespace yang akan dirubah adalah temp dan merupakan default tablesapce, langkah pertama kita harus membuat tablespace baru misal temp2, kemudian merubah default tablespace dari temp menjadi temp2, kita rubah kembali default tablespace ke temp dan hapus temp2

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/app/oradata/NEURON/temp2_01.dbf' SIZE 5M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
 
TABLESPACE created.
 
 
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
 
DATABASE altered.
 
 
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
 
TABLESPACE dropped.
 
SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/app/oradata/NEURON/temp01.dbf' SIZE 500M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
 
TABLESPACE created.
 
 
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
 
DATABASE altered.
 
 
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
 
TABLESPACE dropped.

untuk oracle 9i ke atas perubahan ukuran tablespace akan lebih mudah dilakukan
berikut cara untuk meresize temp tablespace :

SQL> SELECT tablespace_name, file_name, bytes
  2  FROM dba_temp_files WHERE tablespace_name = 'TEMP';
 
TABLESPACE_NAME   FILE_NAME                                 BYTES
----------------- -------------------------------- --------------
TEMP              /app/oradata/NEURON/temp01.dbf   2,107,200,000
 
 
SQL> ALTER DATABASE TEMPFILE '/app/oradata/NEURON/temp01.dbf' DROP INCLUDING DATAFILES;
 
DATABASE altered.
 
 
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/app/oradata/NEURON/temp01.dbf' SIZE 512m
  2  AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;
 
TABLESPACE altered.
 
 
SQL> SELECT tablespace_name, file_name, bytes
  2  FROM dba_temp_files WHERE tablespace_name = 'TEMP';
 
TABLESPACE_NAME   FILE_NAME                                 BYTES
----------------- -------------------------------- --------------
TEMP              /app/oradata/NEURON/temp01.dbf      536,870,912

terkadang di sistem operasi windows command DROP INCLUDING DATAFILES tidak menghapus file fisik, untuk memastikannya anda perlu mencek terlebih dahulu, jika belum terhapus, hapus file dengan os Command.

Oracle ,

Startup dan Shutdown Oracle

December 24th, 2008
2 comments

Sebelum melakukan startup atau shutdown cek terlebih dahulu apakah service oracle sudah jalan atau belum :

1. Login ke OS dengan user oracle
2. check listener

#lsnrctl stat

jika hasilnya sepertin dibawah ini :

TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=NeuronServer.site)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused</code>

berarti listener oracle tidak jalan.

untuk menjalankan ketik

#lsnrctl start

listener bisa dijalankan jika service oracle sudah hidup.

3. Start Up Oracle

1
2
3

#sqlplus /nolog
SQL>conn / AS sysdba
SQL>startup
ORACLE instance started.
 
Total SYSTEM Global Area  608174080 bytes
Fixed SIZE                  1220820 bytes
Variable SIZE             121638700 bytes
DATABASE Buffers          478150656 bytes
Redo Buffers                7163904 bytes
DATABASE mounted.
DATABASE opened.

Kemudian hidupkan listener dan cek kembali listener

#lsnrctl START
LSNRCTL FOR Linux: Version 10.2.0.1.0 - Production ON 24-DEC-2008 09:36:57
 
Copyright (c) 1991, 2005, Oracle.  ALL rights reserved.
 
Starting /app/oracle/product/10.2/bin/tnslsnr: please wait...
 
TNSLSNR FOR Linux: Version 10.2.0.1.0 - Production
SYSTEM parameter FILE IS /app/oracle/product/10.2/network/admin/listener.ora
LOG messages written TO /app/oracle/product/10.2/network/LOG/listener.LOG
Listening ON: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening ON: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NeuronServer.site)(PORT=1521)))
 
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS OF the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR FOR Linux: Version 10.2.0.1.0 - Production
START DATE                24-DEC-2008 09:36:59
Uptime                    0 days 0 hr. 0 MIN. 0 sec
Trace LEVEL               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter FILE   /app/oracle/product/10.2/network/admin/listener.ora
Listener LOG FILE         /app/oracle/product/10.2/network/LOG/listener.LOG
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NeuronServer.site)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) FOR this service...
The command completed successfully

Oracle , ,

Create Tokenize Function in Oracle

December 24th, 2008
2 comments

Fungsi tokenizer sering sekali digunakan , jiak kita menggunakan java fungsi itu akan relatif mudah di gunakan karena disudah disediakan oleh java, nah bagaimana kita melakukan fungsi tokenizer di oracle.
berikut ada sample procedure untuk melakukan tokenizer di oracle

The pl/sql Oracle version for the Tokenizer procedure

CREATE OR REPLACE PROCEDURE tokenizer (
         iStart     IN       NUMBER,
         sPattern   IN       VARCHAR2,
         sBuffer    IN       VARCHAR2,
         sResult    OUT      VARCHAR2,
         iNextPos   OUT      NUMBER
        )
AS
     nPos1   NUMBER;
     nPos2   NUMBER;
BEGIN
     nPos1 := INSTR (sBuffer, sPattern, iStart);
    IF nPos1 = 0
    THEN
            sResult := NULL;
    ELSE
           nPos2 := INSTR (sBuffer, sPattern, nPos1 + 1);
          IF nPos2 = 0
          THEN
                 sResult := RTRIM (LTRIM (SUBSTR (sBuffer, nPos1 + 1)));
                 iNextPos := nPos2;
          ELSE
                sResult := SUBSTR (sBuffer, nPos1 + 1, nPos2 - nPos1 - 1);
                iNextPos := nPos2;
          END IF;
     END IF;
END tokenizer;

untuk test nya gunakan script dibawah ini

– a Procedure to Test the Tokenizer

CREATE OR REPLACE PROCEDURE sp_test_tokenizer
AS
      sepr VARCHAR2(1);
      sbuf VARCHAR2(200);
      sres VARCHAR2(200);
      pos NUMBER;
      istart NUMBER;
BEGIN
 
      sbuf := ‘@0@11@222@3333@44444@555555@6666666@77777777@888888888?;
      sepr := ‘@’;
      istart := 1;
      tokenizer (istart ,sepr,sbuf,sres,pos);
      IF (pos &lt;&gt; 0) THEN
          dbms_output.put_line (sres);
      END IF;
     WHILE (pos &lt;&gt; 0)
     LOOP
             istart := pos;
             tokenizer (istart ,sepr,sbuf,sres,pos );
             dbms_output.put_line (sres);
     END LOOP;
END sp_test_tokenizer;
/

dari SQLPLUS

SQL&gt; SET serveroutput ON
SQL&gt;exec sp_test_tokenizer;

silakan mencoba semoga bermanfaat
<sumber dari oracle.com>

Oracle ,