주요 SQL

SQL>select  status  from  v$instance ; - oracle 상태 확인

SQL>alter system set shared_pool_size = 100M; - shared pool 크기 조정

SQL>show parameter processes;

SQL>show parameter undo;

SQL> show parameter db_block_size;

Redo Copy Latch 조회

SQL> col name for a13

SQL> select name,gets,misses,immediate_gets,wait_time

  2  from v$latch_children

  3  where name='redo copy';

NAME    GETS     MISSES IMMEDIATE_GETS  WAIT_TIME

------------- ---------- ---------- -------------- ----------

redo copy       6  0 0    0

redo copy       6  0 0    0

redo copy       6  0 0    0

redo copy       6  0 0    0

redo copy       6  0 0    0

redo copy       6  0 0    0

redo copy       6  0 0    0

redo copy       6  0      2416    0

Redo allocation latch 조회

SQL> select count(*)

  2  from v$latch_children

  3  where name='redo allocation';

  COUNT(*)

----------

29

현재 Redo Log Block Size

SQL>select max(lebsz_ from sys.x$kccle;

MAX(LEBSZ)

----------

       512

commit과 관련된 파라미터

SQL> show parameter commit;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

commit_logging     string

commit_point_strength     integer 1

commit_wait     string

commit_write     string

SQL>alter system set commit_logging = immediate;

SQL>alter system set commit_wailt = nowait;

SQL>alter system set commit_write = immediate, nowait;

SQL> show parameter commit;

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

commit_logging     string IMMEDIATE

commit_point_strength     integer 1

commit_wait     string NOWAIT

commit_write     string IMMEDIATE, NOWAIT

용량 관리 하기

SQL> create table scott.iphak (studno number) tablespace haksa;

Table created.

SQL> begin

  2  for i in 1..50000 loop 

  3  insert into scott.iphak values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> /

begin

*

ERROR at line 1:

ORA-01653: unable to extend table SCOTT.IPHAK by 8 in tablespace HAKSA

ORA-06512: at line 3

수동으로 data file 추가

SQL> alter tablespace haksa add datafile '/app/oracle/oradata/testdb/haksa02.dbf' size 20M;

Tablespace altered.

SQL> @tablespacesize

TABLESPACE_NAME    MB FILE_NAME

--------------- ----- --------------------------------------------------

USERS    5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1   85 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX  490 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM  680 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE  100 /app/oracle/oradata/testdb/example01.dbf

HAKSA    1 /app/oracle/oradata/testdb/haksa01.dbf

HAKSA   20 /app/oracle/oradata/testdb/haksa02.dbf

Data file 크기 수동 증가

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' resize 20M;

Database altered.

SQL> @tablespacesize

TABLESPACE_NAME    MB FILE_NAME

--------------- ----- --------------------------------------------------

USERS    5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1   85 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX  490 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM  680 /app/oracle/oradata/testdb/system01.dbf

EXAMPLE  100 /app/oracle/oradata/testdb/example01.dbf

HAKSA   20 /app/oracle/oradata/testdb/haksa01.dbf

HAKSA   20 /app/oracle/oradata/testdb/haksa02.dbf

Data file 크기 자동 증가(32bit는 16GB, 64bit는 32GB 증가)

SQL> alter database datafile '/app/oracle/oradata/testdb/haksa01.dbf' autoextend on;

Database altered.

Data file autoextend 유무 확인

SQL>set line 200

SQL>col tablespace_name for a10

SQL>col file_name for a50

SQL>select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status

  2  from dba_data_files;

TABLESPACE    MB FILE_NAME    Aut ONLINE_

---------- ----- -------------------------------------------------- --- -------

USERS       5 /app/oracle/oradata/testdb/users01.dbf    YES ONLINE

UNDOTBS1      85 /app/oracle/oradata/testdb/undotbs01.dbf    YES ONLINE

SYSAUX     490 /app/oracle/oradata/testdb/sysaux01.dbf    YES ONLINE

SYSTEM     680 /app/oracle/oradata/testdb/system01.dbf    YES SYSTEM

EXAMPLE      100 /app/oracle/oradata/testdb/example01.dbf    YES ONLINE

HAKSA      20 /app/oracle/oradata/testdb/haksa01.dbf    YES ONLINE

HAKSA      20 /app/oracle/oradata/testdb/haksa02.dbf    NO ONLINE

SQL>alter tablespace haksa offline;

SQL>alter tablespace haksa offline temporary;

SQL>alter tablespace haksa online;

SQL>alter system checkpoint;