DB Link 설정을 그동안 사용하면서 많이 사용을 안해서 그런지 이런 오류 메세지를 만난적이 없었다.

그러다 오늘 알게되었기에 오라클 문서를 확인하고 기록으로 남기기 위해 작성한다.

ORA-02020  too many database links in use

Cause: The current session has exceeded the INIT.ORA open_links maximum.

Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling 

          open cursors that reference remote databases.

이런 오류 메시지를 만났다면 Open_links parameter 설정을 확인해주어야 한다는 의미다.

OPEN_LINKS

OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

출처 : http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams164.htm#REFRN10138

해당 파라미터의 초기값은 4개이다. 결론부터 얘기하면 DB Link 설정 갯수가 4개보다 많기 때문에 위와 같은 오류 메세지를 보게

되었다는 얘기입니다. 따라서, 추가로 생성해야 하는 상황이라면 이 값을 조정해주시면 됩니다.

위의 설정 값을 확인하기 위해서는 "select name, value from v$parameter where name='open_links'; "와 같은 Query로도 확인이

가능합니다. ( sqlplus에서라면.. show parameter open_links로도 확인 가능)

값의 조정은 init.ora 파일에서 직접해도 되고, "alter system set open_links=10 scope=spfile;" 와 같이 조정해도 됩니다.

값은 필요한 만큼 조정하시되, 위에 보시면 최대 값이 255인것을 감안하시면 됩니다.

값이 조정되었다면 이를 반영하기 위해서는 DBMS를 Restart 시켜주어야 반영됨을 잊지 마세요.

이외에 참고로 DB Link에 연관된 파라미터 정보와 제한 정보는 아래와 같습니다. 참고하시기 바랍니다.

출처 : http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205

이미 Oracle PartnerNetwork 멤버인 경우 파트너 혜택 표 액세스

OPN 혜택 및 리소스 Silver Gold Platinum Diamond 파트너 환영 키트 안전한 OPN 파트너 웹 사이트 액세스  자료실 컨텐츠의 제한적 이용 Cloud Services, 1-Click 및 Oracle Database Appliance 자료실 파트너 뉴스레터 Oracle Partner Business Center Global Diamond Desk 전화를 통한 파트너 관리 지원1   전담 오라클 파트너 비즈니스 컨설턴트     Oracle OpenWorld에서 파트너 포럼 및/또는 라운지에 참가하여 솔루션 소개2 세일즈 부문 부사장/경영진 후원자와의 미팅에 초대     파트너 자문 위원회 참가2     경영진 파트너 포럼 참가2 Partner Award 수상 자격1 Global Alliance Manager 지정       오라클 경영진 후원     지역별 글로벌 역량 강화 혜택 및 툴 Silver Gold Platinum Diamond 파트너 전용 온라인 세미나, 선별된 이벤트와 워크숍 참석2 Oracle University의 e러닝 컨텐츠 라이브러리 액세스 Competency Center에서 제공하는 지침 학습 과정(GLP)을 통한 파트너별 교육 이용1 Cloud Services, 1-Click 제품 및 Oracle Database Appliance Enablement 2.0 Boot Camp1 Cloud Services, 1-Click 제품 및 Oracle Database Appliance Oracle University 제품 및 서비스에 대한 할인1 20% 25% 25% 25% OPN 멤버쉽 가입 또는 갱신 후 처음 30일 이내에 구입하는 Oracle University 학습 크레딧에 대한 할인 35% 35% 35% 35% OPN 멤버쉽 가입 또는 갱신 후 처음 30일 이내에 구입하는 SSCD(Self-Study CD)에 대한 할인 35% 35% 35% 35% 라이브 가상 강의(LVC)에 대한 할인1 20% 25% 30% 30% 시험 바우처에 대한 할인 20% 25% 25% 25% 무료 평가/시험 바우처1   2 6 6 -100(Worldwide 파트너) 개발 혜택 및 툴 Silver Gold Platinum Diamond 테크놀로지 프로그램에 대한 개발 라이센스5 1-Click 제품 및 Oracle Database Appliance OPN 정책 에 나와 았음 OPN 정책 에 나와 았음 OPN 정책 에 나와 았음 Oracle Validated Integration   수수료 방식 할인 할인 SOA 기반의 Oracle Validated Integration1   수수료 방식 1개 포함 1개 포함 오라클 제품으로의 마이그레이션을 지원하기 위한 리소스(툴 키트, 워크벤치 및 타사 마이그레이션 제공업체)1   사전에 베타 프로그램 신청 가능   마케팅 혜택 및 툴 Silver Gold Platinum Diamond Oracle.com/events에 파트너 이벤트 게시 가능   전문화된 기술력과 솔루션을 갖춘 경우 오라클 파트너 브랜드 및 로고 사용1 Oracle.com에 소개1     Oracle Cloud Marketplace에 게시 신청4 우선 홍보 기회 우선 홍보 기회 마케팅 키트1 Cloud Services, 1-Click 제품 및 Oracle Database Appliance 오라클 출판물 광고비 할인(인쇄 및 디지털) 25% 35% 40% 50% 오라클 관련 정보를 심층적으로 다룬 뉴스레터 후원에 대한 할인 혜택 25% 35% 40% 50% 마케팅 캠페인을 위한 연락처 목록 액세스1     MDF(Marketing Development Funds) 신청1     전담 글로벌 파트너 마케팅 관리자       공동 고객 성공 사례에 대한 전문적인 개발1   Oracle Magazine의 파트너 뉴스 섹션에서 홍보1     홍보(PR) 지원 및 가이드라인1 공동 보도 자료 공동 보도 자료 Oracle Cloud Marketplace 또는 AppCloud Marketplace에 승인된 통합에 대한 보도 자료 게시4 Google 애드워즈 캠페인의 오라클 상표 JavaONE 및 Oracle OpenWorld에서 솔루션 소개1 오라클 고객을 직접 만나는 자리에서 연설1     오라클 내부용 활동에서 연설할 수 있는 기회4     판매 혜택 및 툴 Silver Gold Platinum Diamond 각 자료실에 나와 있는 멤버쉽 레벨 및 리셀러 자격 조건 달성에 따라 공인된 오라클 제품 및 서비스 판매1 1-Click 제품 및 Oracle Database Appliance 오라클 프로그램 및 서비스 판매를 위한 OPN 멤버 할인 가격1 1-Click 제품 및 Oracle Database Appliance 세일즈 키트1 Cloud Services, 1-Click 제품 및 Oracle Database Appliance OMM 정책에 따라 결정되는 Oracle Open Market Model(OMM) Resale Initiative를 통해 순 신규 영업 기회 등록1 OMM 정책에 따라 결정되는 OMM Referral Initiative를 통해 오라클 비즈니스를 추천하는 경우 보상1 Oracle OMM Non-commission Co-Sell Initiative를 통해 오라클 영업 기회를 등록하는 경우 인지도 강화1 고객에게 Oracle Financing 제공1 테크놀로지 및 애플리케이션 프로그램을 위한 데모 라이센스5 1-Click 제품 및 Oracle Database Appliance OPN 정책 에 나와 았음 OPN 정책 에 나와 았음 OPN 정책 에 나와 았음 Oracle PartnerNetwork Solutions Catalog를 통해 가시성 확보1 우선 배치 및 홍보 우선 배치 및 홍보 Oracle PartnerNetwork Solutions Catalog를 통해 영업 기회 창출1 내부용으로 사용할 오라클 프로그램 라이센스 구입 시 할인1   내부용으로 사용할 CRM On-Demand 프로그램 라이센스 구입 시 할인1   비즈니스 실무 및 가격 정보 교육1 국제적인 부패 방지 교육1 지원 혜택 및 툴 Silver Gold Platinum Diamond Oracle Solaris, Solaris Cluster 및 Solaris Studio 업데이트 및 패치 이용5   OPN SI(Support IdentifierI)를 통해 "My Oracle Support" 이용5 1-Click 제품 및 Oracle Database Appliance 소프트웨어 업그레이드 및 패치 (테크놀로지 및 애플리케이션 제품만 해당) - 현 오라클 지원 제품 및 릴리스5 1-Click 제품 및 Oracle Database Appliance Partner Business Center의 지원 서비스 1-Click 제품 및 Oracle Database Appliance 특별 파트너 할인 가격으로 SR(Service Request) 패키지 구입1 (테크놀로지 및 애플리케이션 제품만 해당)   개발, 데모 및 통합 라이센스와 함께 사용할 무료 SR(Service Request)1     Enterprise Linux Basic Support 서비스 및 Oracle VM Premier Support 서비스를 통해 무료 SR을 사용하여 파트너의 Linux 및 Oracle VM 개발 시스템 지원1     Linux Basic Support 서비스 및 Oracle VM Premier Support 서비스를 위한 패치/업데이트를 다운로드하여 OPN 멤버의 Linux 및 Oracle VM 개발 시스템 지원   파트너의 Linux 및 Oracle VM 개발 시스템을 위한 Linux 및 Oracle VM 지원 서비스 가격 할인1 OCVS(Oracle Collaborative Vendor Support) 프로그램 이용1   파트너 내부용으로 사용할 ACS(Advanced Customer Services)에 대한 할인1 Linux 및 VM 전용 ACS(Advanced Customer Services) 파트너 팀에 대한 개별적 액세스1     ACS의 혜택에 대한 보완적 컨설팅1     ACS(Advanced Customer Services) 내부 웹 페이지에 대한 OPN 멤버 노출1     공동 캠페인을 위한 ACS(Advanced Customer Services)의 공동 마케팅 및 영업 기회 창출 지원1     유효한 ASFU/ESL 협약을 체결한 OPN 멤버를 위한 기술 지원 SR(Service Request) 및 확장된 지원 패키지8   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready 및 Oracle Exastack Optimized와 관련된 혜택 및 툴 Silver Gold Platinum Diamond 새로운 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready 및 Oracle Exastack Optimized 알림 및 오리엔테이션 통지1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 안전한 OPN 전문화 리소스 센터 이용   전문화 프로그램 전문화 프로그램 전문화 프로그램 Oracle Exastack Optimized 로고   Oracle Exastack Optimized Oracle Exastack Optimized Oracle Exastack Optimized OPN 전문화(Specialized) 로고1   전문화 프로그램 전문화 프로그램 전문화 프로그램 Oracle Exastack Ready 로고   Oracle Exastack Ready Oracle Exastack Ready Oracle Exastack Ready Oracle Validated Integration 로고   Oracle Validated Integration Oracle Validated Integration Oracle Validated Integration OPN 고급 전문화 프로그램 로고   고급 전문화 프로그램 고급 전문화 프로그램 고급 전문화 프로그램 뉴스레터, 웹 사이트, 광고 등으로 홍보1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized OPN Solutions Catalog에 우선 배치   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 오라클의 내부 파트너 찾기 시스템에 우선 배치1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 내부 뉴스레터에 발표1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 무료 시험 바우처1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 보도 자료에 오라클 경영진의 인용구 사용1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 오라클 회의실 시설 이용1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 데모 서비스 이용1, 4   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized  / 우선 이용 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized  / 우선 이용 무료 지원 SR(Service Request) (테크놀로지 및 애플리케이션 제품만 해당)1, 3   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized Enterprise Linux Basic Support 서비스 및 Oracle VM Premier Support 서비스를 통해 무료 지원 SR(Service Request)로 파트너의 Linux 및 Oracle VM 개발 시스템 지원1, 3   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 제품 릴리스 준비 자료1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 구현 방법 이용1   전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized 전문화 프로그램, Oracle Validated Integration, Oracle Exastack Ready, Oracle Exastack Optimized

reference : http://www.oracle.com/partners/campaign/specialized-benefits-036151-ko.html

• V$Option의 내용 

  PARAMETER	VALUE
  Partitioning	TRUE
  Objects	TRUE
  Real Application Clusters	FALSE
  Advanced replication	TRUE
  Bit-mapped indexes	TRUE
  Connection multiplexing	TRUE
  Connection pooling	TRUE
  Database queuing	TRUE
  Incremental backup and recovery	TRUE
  Instead-of triggers	TRUE
  Parallel backup and recovery	TRUE
  Parallel execution	TRUE
  Parallel load	TRUE
  Point-in-time tablespace recovery	TRUE
  Fine-grained access control	TRUE
  Proxy authentication/authorization	TRUE
  Change Data Capture	TRUE
  Plan Stability	TRUE
  Online Index Build	TRUE
  Coalesce Index	TRUE
  Managed Standby	TRUE
  Materialized view rewrite	TRUE
  Database resource manager	TRUE
  Spatial	TRUE
  Automatic Storage Management	FALSE
  Export transportable tablespaces	TRUE
  Transparent Application Failover	TRUE
  Fast-Start Fault Recovery	TRUE
  Sample Scan	TRUE
  Duplexed backups	TRUE
  Java	TRUE
  OLAP Window Functions	TRUE
  Block Media Recovery	TRUE
  Fine-grained Auditing	TRUE
  Application Role	TRUE
  Enterprise User Security	TRUE
  Oracle Data Guard	TRUE
  Oracle Label Security	FALSE
  OLAP	TRUE
  Basic Compression	TRUE
  Join index	TRUE
  Trial Recovery	TRUE
  Data Mining	TRUE
  Online Redefinition	TRUE
  Streams Capture	TRUE
  File Mapping	TRUE
  Block Change Tracking	TRUE
  Flashback Table	TRUE
  Flashback Database	TRUE
  Transparent Data Encryption	TRUE
  Backup Encryption	TRUE
  Unused Block Compression	TRUE
  Oracle Database Vault	FALSE
  Result Cache	TRUE
  SQL Plan Management	TRUE
  SecureFiles Encryption	TRUE
  Real Application Testing	TRUE
  Flashback Data Archive	TRUE
  DICOM	TRUE
  Active Data Guard	TRUE
  Server Flash Cache	TRUE
  Advanced Compression	TRUE
  XStream	TRUE
  Deferred Segment Creation	TRUE

TRIM

Syntax

Description of the illustration trim.gif

Purpose

TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotation marks.

• If you specify LEADING, then Oracle Database removes any leading characters equal to trim_character.

• If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character.

• If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character.

• If you do not specify trim_character, then the default value is a blank space.

• If you specify only trim_source, then Oracle removes leading and trailing blank spaces.

• The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.

• If either trim_source or trim_character is null, then the TRIM function returns null.

Both trim_character and trim_source can be VARCHAR2 or any datatype that can be implicitly converted to VARCHAR2. The string returned is of VARCHAR2 datatype if trim_source is a character datatype and a LOB if trim_source is a LOB datatype. The return string is in the same character set as trim_source.

Examples

This example trims leading zeros from the hire date of the employees in the hr schema:

SELECT employee_id,
      TO_CHAR(TRIM(LEADING 0 FROM hire_date))
      FROM employees
      WHERE department_id = 60
      ORDER BY employee_id;

EMPLOYEE_ID TO_CHAR(T
----------- ---------
        103 3-JAN-90
        104 21-MAY-91
        105 25-JUN-97
        106 5-FEB-98
        107 7-FEB-99

LTRIM

Syntax

Description of the illustration ltrim.gif

Purpose

LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, then it defaults to a single blank. If char is a character literal, then you must enclose it in single quotation marks. Oracle Database begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if char is a character datatype, NVARCHAR2 if char is a national character datatype, and a LOB if char is a LOB datatype.

The following example trims the redundant first word from a group of product names in the oe.products table:

SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name"
   FROM products
   WHERE product_name LIKE 'Monitor%';

PRODUCT_NAME         Short Name
-------------------- ---------------
Monitor 17/HR        17/HR
Monitor 17/HR/F      17/HR/F
Monitor 17/SD        17/SD
Monitor 19/SD        19/SD
Monitor 19/SD/M      19/SD/M
Monitor 21/D         21/D
Monitor 21/HR        21/HR
Monitor 21/HR/M      21/HR/M
Monitor 21/SD        21/SD
Monitor Hinge - HD   Hinge - HD
Monitor Hinge - STD  Hinge - STD

RTRIM

Syntax

Description of the illustration rtrim.gif

Purpose

RTRIM removes from the right end of char all of the characters that appear in set. This function is useful for formatting the output of a query.

If you do not specify set, then it defaults to a single blank. If char is a character literal, then you must enclose it in single quotation marks. RTRIM works similarly to LTRIM.

Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if char is a character datatype, NVARCHAR2 if expr1 is a national character datatype, and a LOB if char is a LOB datatype.

The following example trims all the right-most occurrences of period, slash, and equal sign from a string:

SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" FROM DUAL;
 
RTRIM exam
----------
BROWNING:

reference : Oracle® Database SQL Language Reference - Oracle11gR1

그동안 너무 RTRM과 LTRIM만 사용한듯.... 

I still find confusion out there about the LOGGING and NOLOGGING clauses when performing DML and DDL operations, the reality is that the NOLOGGING clause will work only on particular conditions, but all regular inserts,updates and deletes will still log the operations.

The benefits of the NOLOGGING option are:

• Will save disk space when the archive option is enabled.
• Will largely reduce I/O on the redologs.
• Will reduce the time it takes to complete the operation.

Please note that NOLOGGING operations will only reduce -not eliminate- the logging.

Lets see an example -

We can see that there is a big difference on the redo size generated by each insert, there are many post and articles on the internet  that show the speed benefits when using the NOLOGGING option, but here I mainly want to clarify that a regular insert (no APPEND hint) will still generate redologs even if the table have been created with the NOLOGGING option.

What happens to the data after a restore when a nologging operation was performed on it?

I will present some scenarios to show the consequences when we need to perform a restore after a nologging transaction, this way we will know what to expect and we can better prepare ourselves in case of a disaster.

I took a full database backup, now I will create several tables with different options to see what happens after a restore, you might see some surprises here!

Scenarios:

• Table “create as select” with the nologging option (table_ctas_nologging).
• Regular table “create as select” (table_ctas_logging)
• A nologging table created empty, and a regular (logging) insert (table_ctas_nologging_insert)
• Table created with nologging, then two inserts, one with and one without logging (table_insert_mixed)
• Regular logging table, with a nologging index (table_ctas_index_nologging)

Now I will shutdown the database and restore the tablespace from the backup.
Next is an extract from RMAN

Now lets see the status of the tables:

That doesn’t look good, lets see the next table

Good, no problem here, the next scenario is more interesting, the table was created with the NOLOGGING option, but the inserts were done without the APPEND hint

Good, no problem here, now let’s see our table with half data inserted with logging and half with nologging

Wow, the whole table is unredable!

Now lets see the table with the NOLOGGING  index .

Ok, thats nice, the table is accessible, but what happend if we try to use the index?

I tried to rebuil the index but I was still getting the same error message, at the end I was forced to drop it and recreate it.

Conclusions:

• Use the NOLOGGING option only on temporary/working/staging tables.
• Always perform a backup after a NOLOGGING operation.
• Unless explicitly indicated, DDLs like CTAS and DMLs like inserts will log all operations.

FROM ORACLE DOCUMENTATION:

NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:

DML:

• Direct-path INSERT (serial or parallel) resulting either from an INSERT or aMERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.
• Direct Loader (SQL*Loader)

DDL:

• CREATE TABLE … AS SELECT
• CREATE TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS
• ALTER TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS(to specify logging of newly created LOB columns)
• ALTER TABLE … modify_LOB_storage_clause … modify_LOB_parameters … NOCACHE |CACHE READS (to change logging of existing LOB columns)
• ALTER TABLE … MOVE
• ALTER TABLE … (all partition operations that involve data movement)
  • ALTER TABLE … ADD PARTITION (hash partition only)
  • ALTER TABLE … MERGE PARTITIONS
  • ALTER TABLE … SPLIT PARTITION
  • ALTER TABLE … MOVE PARTITION
  • ALTER TABLE … MODIFY PARTITION … ADD SUBPARTITION
  • ALTER TABLE … MODIFY PARTITION … COALESCE SUBPARTITION
• CREATE INDEX
• ALTER INDEX … REBUILD
• ALTER INDEX … REBUILD [SUB]PARTITION
• ALTER INDEX … SPLIT PARTITION

For objects other than LOBs, if you omit this clause, then the logging attribute of the object defaults to the logging attribute of the tablespace in which it resides.

For LOBs, if you omit this clause, then:

• If you specify CACHE, then LOGGING is used (because you cannot have CACHENOLOGGING).
• If you specify NOCACHE or CACHE READS, then the logging attribute defaults to the logging attribute of the tablespace in which it resides.

NOLOGGING does not apply to LOBs that are stored internally (in the table with row data). If you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, then Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data.

 

reference : http://bit.ly/1aYFZw3

Oracle DBMS의 Version별 OS Platform Certification 정보입니다.

Oracle 12c는 Windows 2003이 아예 빠져 버렸네요. 아무래도 Windows 2003의 지원 정책 때문인 것으로 사료됩니다.

12.1

• OS versions and minimum levels:
  • Windows 2008
  • Windows 2008 R2
  • Windows 2012 (single instance only)
    • Also certified as a guest under Window 2012 under Hyper-V (single instance only) -  see Supported Virtualization and Partitioning Technologies for Oracle Database and RAC Product Releases
  • Windows 7
  • Windows 8
• Client certifications:
  • Oracle provides both a 64-bit and 32-bit Windows client with Oracle Database 12.1 for Windows x64 releases 
  • 32-bit client is certified with all certified 64-bit versions of Windows
  • 32-bit client is also certified on Windows x86 (32-bit) 7, 8, and 2008
• For complete details of OS versions supported, minimum levels, and other installation requirements see Oracle Database Installation Guide 12c Release 1 (12.1) for Windows.

11.2

• OS versions and minimum levels:
  • Windows XP
  • Windows 7
  • Windows Vista
  • Windows 2003
  • Windows 2003 R2
  • Windows 2008
  • Windows 2008 R2
• See Oracle Database Installation Guide 11g Release 2 (11.2) for Microsoft Windows for the Minimum Operating System Requirements.
  • All 11.2 Windows Installation Guides
• RAC:
  • 11.2 RAC is certified on Windows 64, is NOT certified to run on Windows 32
  • RAC Technologies Compatibility Matrix (RTCM) for Windows x86-64
  • Microsoft Windows is ONLY certified on Oracle Clusterware

11.1

• OS versions and minimum levels
  • Windows XP
  • Windows Vista
  • Windows 2003
  • Windows 2003 R2
  • Windows 2008 (11.1.0.7 or higher required) -- use the 11.1.0.7 database install created specifically for Windows 2008
• See Oracle Database Installation Guide 11g Release 1 (11.1) for Microsoft Windows for the Minimum Operating System Requirements.
  • All 11.1 Windows Installation Guides
• RAC:
  • RAC Technologies Compatibility Matrix (RTCM) for Windows x86-64
  • Microsoft Windows is ONLY certified on Oracle Clusterware.

10.2

• OS versions and minimum levels
  • Windows XP
  • Windows 7 (see note below)
  • Windows Vista (see note below)
  • Windows 2003
  • Windows 2003 R2 (<10.2.0.2 or higher)
    • iSQL*Plus version 10.2.0.3.0 and higher is certified 2003 and 2003 R2.
  • Windows 2008 (See note below)
  • Windows 2008 R2 (see note below)
• See Database Installation Guide for Microsoft Windows (64-bit) for the Minimum Operating System Requirements.
  • All 10.2 Installation Guides
• RAC:
  • 10.2 RAC is NOT certified on XP or Vista
  • RAC Technologies Compatibility Matrix (RTCM) for Windows x86-64
  • Microsoft Windows is ONLY certified on Oracle Clusterware
• Windows Vista, 7, Server 2008, and Server 2008 R2: 

• Do not install Oracle Database 10g Release 2 on Windows Server 2008, Server 2008 R2, 7, nor Vista using the 10.2.0.1 version. You must use the 10.2.0.4 (64-bit) x64 version specifically created for these operating systems. After installation, for Windows 7 and Windows Server 2008 R2, patchset 10.2.0.5 or higher must be applied.  For more information on installation see the release notes sections on the installation media:
  
  • 32-bit
  • 64 bit

출처 : Oracle Support - 문서 ID 1307195.1 발췌

Current Interoperability Support Situation

The matrix below summarizes client and server combinations that are supported for the most commonly used product versions. A full matrix appears at the end of this document.

New interoperability problems will only be investigated if BOTH releases involved are covered by a valid support contract at the time that the issue is reported .
eg:

A 11.1.0 client to an 11.2.0 server issue requires the customer to have a valid Extended Support contract for the 11.1.0 client in order for Oracle to investigate it.

Client Version	Server Version
	12.1.0	11.2.0	11.1.0	10.2.0	10.1.0	9.2.0
12.1.0	Yes	Yes	ES	ES #7	No #3	No #3
11.2.0	Yes	Yes	ES	ES #7	No	Was #5
11.1.0	ES	ES	ES	ES #7	Was #6	Was #5
10.2.0	ES #7	ES #7	ES #7	Was	Was	Was #5
10.1.0(#4)	No	Was #6	Was #6	Was	Was	Was
9.2.0	No#8	Was #5	Was #5	Was #5	Was	Was

Key:

Yes	Supported
LES	Limited extended support previously applied to 9.2 releases but has now ended.
ES	Supported but fixes only possible for customers with Extended Support .
Was	Was a supported combination but one of the releases is no longer covered by any of Premier Support , Primary Error Correct support , Extended Support nor Extended Maintenance Support so fixes are no longer possible.
No	Has never been Supported

Specific Notes:

• #1 - See Note:207319.1
• #2 - An ORA-3134 error is incorrectly reported if a 10g client tries to connect to an 8.1.7.3 or lower server. See Note:3437884.8 .
• #3 - An ORA-3134 error is correctly reported when attempting to connect to this version.
• #4 - There are problems connecting from a 10g client to 8i/9i where one is EBCDIC based. See Note:3564573.8
• #5 - For connections between 10.2 (or higher) and 9.2 the 9.2 end MUST be at 9.2.0.4 or higher. Connections between 10.2 (or higher) and 9.2.0.1, 9.2.0.2 or 9.2.0.3 have never been supported.
• #6 - For connections between 11.1 (or higher) database server and 10.1 / 10.2 database server across a database link the 10g end MUST be at 10.1.0.5 / 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. (This does not apply to normal 11g clients to 10g database server only server to server and a few very specific client products, such as Oracle Forms). See Note:4511371.8 for more details.
• #7 - For database link connections between 11.1 (or higher) and 10.2 the 10g end MUST be at 10.2.0.2 (or higher) in order to use PLSQL between those versions. See Note:4511371.8 for more details.
• #8 - Attempting to connect from 9.2 to 12.1 will fail with an "ORA-28040: No matching authentication protocol" error.

General Notes:

1. For database links between different Oracle versions connections must be supported in BOTH directions in the matrix above.
   eg: As 11.2 -> 10.1 is not supported then database links between these version are not supported in either direction.
   
2. Unsupported combinations may appear to work but can encounter errors for particular operations. The fact that they appear to work should not be relied upon - issues on unsupported combinations will not be investigated.
3. Since new database servers are compatible with a limited set of older OCI clients, it may not be necessary to upgrade the client software when upgrading the database. However, some new features may not work without upgrading the client software. For example, an Oracle 10.2 client is able to connect to an 11.2 database, but is not able to take advantage of newer features such as Client Result Cache (introduced in 11.1).
4. Oracle Applications , or other Oracle products, may have supported configurations not listed in the matrix above.
5. The matrix above also applies between different platforms and between 32/64 bit releases of Oracle client / server except where any Oracle platform desupport notice indicates otherwise .
6. Unix BEQUEATH (BEQ) connections are NOT supported between different releases. eg: Client 10.2 is not supported to make an Oracle Net connection to a 11.2 server using the BEQ protocol adapter regardless of the interoperability support listed above. See Note:364252.1 for more details.

Oracle Client Version별로 Oracle DB Server Version에 Support하는데 있어서 상호 호환성 여부를 정리한 자료입니다.

출처 : Oracle Support

Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology

By James Koopmann

SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;
Date
---------------------------
06/20/2003 16:55:14
06/26/2003 11:16:36

  1         SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
  2         TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
  3         trunc(86400*(date2-date1))-
  4         60*(trunc((86400*(date2-date1))/60)) seconds,
  5         trunc((86400*(date2-date1))/60)-
  6         60*(trunc(((86400*(date2-date1))/60)/60)) minutes,
  7         trunc(((86400*(date2-date1))/60)/60)-
  8         24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
  9         trunc((((86400*(date2-date1))/60)/60)/24) days,
 10         trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks
 11*        FROM date_table
DATE1             DATE2                SECONDS    MINUTES      HOURS       DAYS      WEEKS
----------------- ----------------- ---------- ---------- ---------- ---------- ----------
06202003:16:55:14 07082003:11:22:57         43         27         18         17          2
06262003:11:16:36 07082003:11:22:57         21          6          0         12          1

SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;
Date
-----------------------------------------------------
20-JUN-03 04.55.14.000000 PM
26-JUN-03 11.16.36.000000 AM

  1  SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table
Date
-------------------
06/20/2003 16:55:14
06/26/2003 11:16:36

 1  SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
Date
-----------------------
06/20/2003 16:55:14:000
06/26/2003 11:16:36:000

1  SELECT time1,  time2,  (time2-time1)
  2*   FROM date_table
TIME1                          TIME2                          (TIME2-TIME1)
------------------------------ ----------------------------   ----------------------
06/20/2003:16:55:14:000000     07/08/2003:11:22:57:000000     +000000017 18:27:43.000000
06/26/2003:11:16:36:000000     07/08/2003:11:22:57:000000     +000000012 00:06:21.000000

  1  SELECT time1,
  2         time2,
  3         substr((time2-time1),instr((time2-time1),' ')+7,2)                 seconds,
  4         substr((time2-time1),instr((time2-time1),' ')+4,2)                 minutes,
  5         substr((time2-time1),instr((time2-time1),' ')+1,2)                 hours,
  6         trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))   days,
  7         trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
  8*   FROM date_table
TIME1                       TIME2                      SECONDS MINUTES HOURS DAYS WEEKS
-------------------------   -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000  07/08/2003:11:22:57:000000  43     27      18    17    2
06/26/2003:11:16:36:000000  07/08/2003:11:22:57:000000  21     06      00    12    1

SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
System altered.

SQL> select sysdate from dual;
SYSDATE
---------
01-JAN-03

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------
09-JUL-03 11.05.02.519000 AM -06:00