Goal & Purpose

1. Partition 조회

SELECT PARTITION_NAME, Count(1)

FROM USER_TAB_PARTITIONS

WHERE TABLE_OWNER = 'PRD_SOAINFRA'

AND PARTITION_NAME BETWEEN 'PT_20140829' and 'PT_20140904'

GROUP BY PARTITION_NAME

ORDER BY PARTITION_NAME;

2. Tablespace 사용률 확인

SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) SIZE_GB

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME='PRD_SOAINFRA'

GROUP BY TABLESPACE_NAME;

3. Partition 추가

alter table PRD_SOAINFRA.DOCUMENT_CI_REF add partition PT_20140902 values less than(TIMESTAMP '2014-09-03 00:00:00') tablespace PRD_SOAINFRA;

4. Partition Table 조회

SELECT table_name, count(1)

from user_tab_partitions

where 1=1

group by table_name;

How To Start, Stop and Restart Oracle Listener

1. Display Oracle Listener Status

Before starting, stopping or restarting make sure to execute lsnrctl status command to check the oracle listener status as shown below. Apart from letting us know whether the listener is up or down, you can also find the following valuable information from the lsnrctl status command output.

• Listner Start Date and Time.
• Uptime of listner – How long the listener has been up and running.
• Listener Parameter File – Location of the listener.ora file. Typically located under $ORACLE_HOME/network/admin
• Listener Log File – Location of the listener log file. i.e log.xml

If the Oracle listener is not running, you’ll get the following message.

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:39

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
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=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory

If the Oracle listener is running, you’ll get the following message.

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:02

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                29-APR-2009 18:43:13
Uptime                    6 days 21 hr. 43 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/devdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "devdb" has 1 instance(s).
  Instance "devdb", status UNKNOWN, has 1 handler(s) for this service...
Service "devdb.thegeekstuff.com" has 1 instance(s).
  Instance "devdb", status READY, has 1 handler(s) for this service...
Service "devdbXDB.thegeekstuff.com" has 1 instance(s).
  Instance "devdb", status READY, has 1 handler(s) for this service...
Service "devdb_XPT.thegeekstuff.com" has 1 instance(s).
  Instance "devdb", status READY, has 1 handler(s) for this service...
The command completed successfully

2. Start Oracle Listener

If the Oracle listener is not running, start the listener as shown below. This will start all the listeners. If you want to start a specific listener, specify the listener name next to start. i.e lsnrctl start [listener-name]

$ lsnrctl start

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:42

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.1.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/devdb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                04-APR-2009 16:27:42
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/devdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "devdb" has 1 instance(s).
  Instance "devdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3. Stop Oracle Listener

If the Oracle listener is running, stop the listener as shown below. This will stop all the listeners. If you want to stop a specific listener, specify the listener name next to stop. i.e lsnrctl stop [listener-name]

$ lsnrctl stop

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:37

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
The command completed successfully

4. Restart Oracle Listener

To restart the listener use lsnrctl reload as shown below instead of lsnrctl stop and lsnrctl start. realod will read the listener.ora file for new setting without stop and start of the Oracle listener.

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

1. Listener Down

2. Instance Down

3. ASM Down

4. CRS Down

select instance_name, status from v$instance;

select instance_name, status from gv$instance;

차이점은?

--TABLESPACE CHECK

SELECT TABLESPACE_NAME     AS "테이블스페이스명",

EXTEND_BYTES/1024/1024   AS "총 할당량(MB)" ,

USED_BYTES/1024/1024      AS "총 사용량(MB)",

ROUND(((USED_BYTES)/EXTEND_BYTES)*100,2) || '%'    AS "여유공간(MB)",

FREE_BYTES/1024/1024

ROUND((1-((USED_BYTES)/EXTEND_BYTES))*100,2) || '%' AS "여유공간률"

FROM (SELECT T1.TABLESPACE_NAME,

T1.EXTEND_BYTES,

T1.EXTEND_BYTES - T2.FREE_BYTES USED_BYTES,

T2.FREE_BYTES

FROM (SELECT TABLESPACE_NAME, SUM(BYTES) EXTEND_BYTES

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) T1,

(SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES

FROM DBA_FREE_SPACe

GROUP BY TABLESPACE_NAME) T2

WHERE T2.TABLESPACE_NAME = T1.TABLESPACE_NAME)

ORDER BY 6;

 1. Optimization :

인덱스로 구성된 컬럼의 max() 값을 구하는 방법은 full scan을 야기하므로

인덱스를 역순으로 검색하되 조건에 만족하는 첫번째 row만을 가져오도록 변경하는 방법

[source]

SELECT MAX(RCV_DATE)

FROM 테이블1

WHERE RCV_DATE_TYPE='RS'

AND RCV_DA <= '20040329'

[target]

SELECT /* index_desc(a 인덱스1) */ rcv_date

FROM 테이블1

WHERE RCV_DATE_TYPE = 'RS'

AND RCV_DATE <='20040329'

and rownum=1;

PARALLEL Hint

/* PARALLEL(table_name, degree) */

1) 정의

   항상 데이터가 균일하게 분산되었다고 가정

   최신 통계정보 수집이 필수

   옵티마이저의 실행계획을 개발자가 부여하는 실행 계획을 바꿔서 실행할 때 사용하는 방법

2) 효과

   Disk I/O 가 많은 테이블에 Disk I/O 작업을 분산시켜 속도를 향상시켜 준다.

   Sorting 작업이 많은 경우 Sorting 작업을 분산시켜 작업 속도를 향상 시킬 수 있다.

3) 실행되는 sql에만 명령질의 옵션

  select  /*+ hint */

  update /*+ parallel(테이블명, 범위)*/ set 설정값 where 조건절

  delete  /*+  parallel(테이블명, 범위)*/ 테이블명

  insert   /*+  parallel(테이블명, 범위)*/ into 테이블명 valuse(값1, 값2...)

4) 병렬옵션

  병렬 질의 (parallel query) : 대용량 테이블을 참조한다면 서버 프로세스는 테이블의 처음부터 마지막까지 순차적으로 읽어야하는데에 성능문제를 가져온다, 이 경우 병렬 질의 옵션을 사용하면 여러개의 병렬 프로세스들은 읽어야 할 테이블 데이터를 균등하게 배분하여 각각 읽기 작업을 동시 수행하게 되므로, 전체를 순차적으로 읽는 것보다 빠르게 데이터를 읽을 수 있다. 또한 병렬 프로세스들이 각각의 CPU에서 작업이 진행될 수 있게 해줌으로써 CPU간에 로드밸런싱을 유지할 수 있다는 장점을 가지고 있다 

  병렬 인덱스 (parallel index creation)

  병렬 데이터 로딩 (parallel data load)

  병렬 복구 (parallel recovery)

  병렬 옵션이 설치되어 있는 지 확인

  SQL > select * from v$option;

            where parameter like '%Parallel%';

cf) http://androiddev.tistory.com/entry/오라클-PARALLEL-힌트

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

SELECT /* + PARALLEL(A 8) */ count(*)

FROM cube_instance A

WHERE A.CREATION_date BETWEEN ....

CASCADE 란 무엇일까?

ARCHIVE MODE하고 redolog 용어를 많이 들어보기는 했지만, 아직까지 정확한 용어...

1. Create User yhoonkim identified by 1234

2. 시작옵션 nomount, mount, open

   http://blog.naver.com/ooabc/20199170963 참고