LaGiPusHinGWitH ----- IT

About This Site

Teknologi Informasi sangatlah penting untuk menunjang kemajuan ilmu pengetahuan di Indonesia pada khususnya. Dan ini semua haruslah ditunjang dengan kemampuan yang sepadan dengan kemajuan teknologi itu sendiri, terlebih untuk para praktisi IT itu sendiri. Akhir kata "Selamat berkarya" untuk kita semua. koy^k^guy^ - Owner

Navigation

Home |

Search


Advanced Search

Calendar

<<
 October 2007
>>
S M T W T F S
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

Categories

Monthly Archives

Most recent entries

Syndicate

Site Statistics

This page has been viewed 264640 times
Page rendered in 0.3760 seconds
38 queries executed
Debug mode is on
Total Entries: 61
Total Comments: 17
Total Trackbacks: 42607
Most Recent Entry: 09/28/2010 09:19 pm
Most Recent Comment on: 09/28/2010 09:51 pm
Total Members: 1
Total Logged in members: 0
Total guests: 5
Total anonymous users: 0
Most Recent Visitor on: 06/02/2012 03:43 pm
The most visitors ever was 269 on 05/28/2011 03:07 pm

Referrers

Site Credits

Powered by:
ExpressionEngine

Design by:
BlogMoxie

Saturday, October 13, 2007
Resizing TableSpace on Oracle

tutor_resizeTS.txt

Dear all lagi.........

Sesuai janji gw, gw bikin catatan kecil lagi...yang ini khusus ngebahas soal mengatasi ukuran tablespace yang kritis.
Masih ingatkan sama catatan gw yang terdahulu.

langsung aja maaaang....

Pertama2 kita check dengan script yang sudah kita bikin -->

the code :

## Cek TABLESPACE By Command Line Sql ##:

set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading “MB ALLOC”
col megs_free format 999,999,999 heading “MB FREE”
col megs_used format 999,999,999 heading “MB USED”
col pct_free format 999 heading “% FREE”
col pct_used format 999 heading “% USED”
--col bsize format 99999 new_value bsize heading “BLOCK SIZE”

--set termout off
--select value bsize from v$parameter
--where name = ‘db_block_size’;
--set termout on

break on report skip 1
compute sum label “TOTAL” of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report

select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;

terus kita running lewat sqlplus-->

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 12,048 255 11,793 2 98
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

Disini terlihat ada 2 buah tablespace yang kritis(lebih dari 80%)....
Sekarang kita akan menambah size dari tablespace tersebut, oh iyah...semua command ini full dari sqlplus yah

langkah berikutnya adalah kita lihat dulu nama2 datafile dari tablespace yang akan kita resize (apa sih datafile?
cari tahu aja yah digoogle..atau tanya lagi ntar insya allah gw jawab)

SQL> select * from dba_data_files where tablespace_name = ‘MAA_TRXP’;

FILE_NAME
---------------------------------------
/m02/oradata/maa/maa_trxp01.dbf
/m02/oradata/maa/maa_trxp02.dbf
/m02/oradata/maa/maa_trxp03.dbf

3 rows selected.

hmmmm...ada yang kurang, apa yah? oh iya jumlah/size dari masing2 datafile belum tampak nih…

sekarang kita lihat field2 pada object datafile biar hasilnya spesifik:

SQL> desc dba_data_files;

Name
---------------------------------------
FILE_NAME
FILE_ID
TABLESPACE_NAME
BYTES
BLOCKS
STATUS
RELATIVE_FNO
AUTOEXTENSIBLE
MAXBYTES
MAXBLOCKS
INCREMENT_BY
USER_BYTES
USER_BLOCKS

SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = ‘MAA_TRXP’
group by file_name;

SUBSTR(FILE_NAME,1,50) SUM(BYTES)
-------------------------------------------------- ----------
/m02/oradata/maa/maa_trxp01.dbf 5242880000
/m02/oradata/maa/maa_trxp02.dbf 5242880000
/m02/oradata/maa/maa_trxp03.dbf 2147483648

3 rows selected.

Sip...sekarang kita resize tablespace dengan membuat datafile yang baru.... sebenarnya penentuan besaran kapasitas
dari datafile ada analisa tersendiri tapi...seperti biasa akan dibahas pada catatan mengenai Database analysis...hehehe...ngeles.

SQL> alter tablespace maa_trxp add datafile ‘/m02/oradata/maa/maa_trxp04.dbf’ size 5000 M;

Tablespace altered.

Check lagi datafile pada tablespace yang bersangkutan:

SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = ‘MAA_TRXP’
group by file_name;

SUBSTR(FILE_NAME,1,50) SUM(BYTES)
-------------------------------------------------- ----------
/m02/oradata/maa/maa_trxp01.dbf 5242880000
/m02/oradata/maa/maa_trxp02.dbf 5242880000
/m02/oradata/maa/maa_trxp03.dbf 2147483648
/m02/oradata/maa/maa_trxp04.dbf 5242880000

4 rows selected.

Nah loooh...dah nambah kan??? siipp, mission succesed.

Sekarang check tablespace-nya, dah ngga kritis lagi atau masih kritis.

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql

Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 17,048 5,245 11,803 31 69
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

Ok deh kaka...sip kan, sekarang dah 69%, dan untuk tablespace lainnya ngga perlu deh gw jelasin lagi step2-nya,
selain ngga perlu juga akan menambah pusing para pembaca sekalian...apalagi gw yang ngetik.... smile

Alhamdulillah...akhirnya selesai juga tugas2 gw, selamat Hari Raya Idul Fitri 1428 H...Taqabalallahu mina wa minkum,
Mohon Maaf Lair dan Batin.... dan SELAMAT BERLIBUR..... ASYIKKKKK GW CUTI....SEMOGA ngga ada masalah sama server2 gw.
</pre>

Posted by roninmorgue on 10/13 at 12:03 AM
Computer DataBase • (0) Comments • (183) Trackbacks • Permalink
Page 1 of 1 pages