1. 해당 서버내 원격지에서 SYSDBA 권한을 가진 USER가 존재하는지 조회
SQL > SELECT *
FROM V$PWFILE_USERS;
2. 존재하지 않을 경우 서버의 oracle 계정으로 들어가 as sysdba 로 접속하여 권한부여
SQL > grant sysdba to system;
3. ORACLE Error가 발생할 경우가 있는데 이유는 패스워드파일이 존재하지 않기 때문
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
4. ORACLE Password file 생성
orapwd file=$ORACLE_HOME/dbs/orapwdevdb password=oracle entries=5
- devdb는 SID, entries는 동시에 접속가능한 session 수, oracle은 DB내부의 sys의 password가 아니라 외부에서 접속할때 인증에 필요한 암호이다.
해당파일을 생성하면 dba권한부여가 가능해짐
'Programer Life/DB'에 해당되는 글 48건
- 2011.10.04 원격지에서 SYSDBA 권한 USER 사용 방법
- 2011.03.24 REF CURSOR 호출 방법
- 2011.03.23 ORA-29285: file write error
- 2011.01.03 AUTONOMOUS_TRANSACTION 처리
- 2010.03.04 순환관계(Connect by) 의 PLAN 차이
- 2010.03.04 히든 파라미터(Hidden parameter) 조회 및 변경
- 2010.02.25 MView 엠뷰 준비, 생성, 자동리플래시, Materialized View Auto Manual Refresh
- 2010.02.11 WARNING: inbound connection timed out (ORA-3136) 에 관해
- 2010.01.27 Recyclebin 과 as of timestamp 로 테이블 및 프로시져 복구 1
- 2010.01.25 10g/9i에서 LINKAGG 함수를 만들어보자 1
Declare
out_code varchar2(11);
out_msg varchar2(100);
type ref_cur is ref cursor;
r_cur ref_cur;
begin
r_cur := PackageName.FunctionName(Parameters...);
dbms_output.put_line(out_code);
dbms_output.put_line(out_msg);
end;
/
out_code varchar2(11);
out_msg varchar2(100);
type ref_cur is ref cursor;
r_cur ref_cur;
begin
r_cur := PackageName.FunctionName(Parameters...);
dbms_output.put_line(out_code);
dbms_output.put_line(out_msg);
end;
/
ORA-29285: file write error
Cause:Failed to write to, flush, or close a file.
Action:Verify that the file exists, that it is accessible, and that it is open in write or append mode.
내가 겪은 원인은 Maxline 사이즈를 초과한 라인을 입력하려고 시도했기때문에 문제가 생겼었다.
UTL_FILE.FOPEN(
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER) RETURN file_type;
max_linesize = Maximum number of characters per line, including the newline character, for this file.
(minimum value 1, maximum value 32767). The default is approximately 1000bytes. (Tom says 1022..)
max_linesize를 지정하지않으면 대략 1000 byte 로 잡히기때문에 위와같은 오라클에러를 떨굼.ㅠㅠ
하여 아래와 같이 변경!!
v_file := utl_file.fopen('디렉토리명', file_name, 'w')
--------> v_file := utl_file.fopen('디렉토리명', file_name, 'w', 32767)
Cause:Failed to write to, flush, or close a file.
Action:Verify that the file exists, that it is accessible, and that it is open in write or append mode.
내가 겪은 원인은 Maxline 사이즈를 초과한 라인을 입력하려고 시도했기때문에 문제가 생겼었다.
UTL_FILE.FOPEN(
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER) RETURN file_type;
max_linesize = Maximum number of characters per line, including the newline character, for this file.
(minimum value 1, maximum value 32767). The default is approximately 1000bytes. (Tom says 1022..)
max_linesize를 지정하지않으면 대략 1000 byte 로 잡히기때문에 위와같은 오라클에러를 떨굼.ㅠㅠ
하여 아래와 같이 변경!!
v_file := utl_file.fopen('디렉토리명', file_name, 'w')
--------> v_file := utl_file.fopen('디렉토리명', file_name, 'w', 32767)
Function 사용도중 아래와 같은 에러를 만나게 되는경우가 있다.
ORA-14551: 질의 안에 DML 작업을 수행할 수 없습니다.
이메세지는 Function 문에서 DML(Insert, Update, Delete)문을 사용하지 못하는 경우에 발생하는 오류인데
이 오류를 제거하려면
DML을 처리하는 문법은 Procedure에서 값을 리턴하는 문법은 Function에서 각각 처리하도록 변경해준다.
Procedure
CREATE OR REPLACE PROCEDURE P_GET_FILEINFO(I_FILE_NAME IN VARCHAR2, O_MSG OUT VARCHAR2) BEGIN |
Function
CREATE OR REPLACE FUNCTION F_GET_FILEINFO( I_FILE_NAME in VARCHAR2) RETURN VARCHAR2 IS END F_GET_FILEINFO ; |
이렇게 처리해도 마찬가지로
ORA-14551: 질의 안에 DML 작업을 수행할 수 없습니다.
에러가 발생하는데, 이럴경우에는 트랜잭션을 Procedure / Function을 분리해 준다.
Procedure에 아래 문법을 추가해준다.
CREATE OR REPLACE PROCEDURE P_GET_FILEINFO(I_FILE_NAME IN VARCHAR2, O_MSG OUT VARCHAR2) /************************************************** BEGIN |
이상은 Oracle 10g에서 테스트 되었습니다.
참고
http://blog.naver.com/yooysj?Redirect=Log&logNo=120065898810
http://blog.naver.com/supernam?Redirect=Log&logNo=24012181
http://blog.naver.com/jjaemani?Redirect=Log&logNo=50001741324
[출처] 승제만세 By Chostar : http://manseya.blog.me/50081264080
[출처] AUTONOMOUS_TRANSACTION 처리|작성자 승제만세
동일한 쿼리, 조회 테이블의 인덱스 구조 및 데이터 분포도도 모두 동일하다.
하지만 위 쿼리와 아래쿼리는 순환구조의 plan부분이 차이가 보인다.
정확한 문제점은 무엇인지는 모르지만 두개의 오라클은 약간의 버전차이가 있다.
FTS가 나는 쪽은 오라클 버전이 낮다.
- 오라클 버전 10.2.0.4.0
- 오라클 버전 10.2.0.1.0
< 해결 방법 >
히든 파라미터(Hidden parameter) 중 '_old_connect_by_enabled' 의 값을 true로 변경한다.
(히든 파라미터이므로 spfile수정 후 DB Shutdown하여 적용시킨다.)
히든 파라미터 조회 방법은 아래 게시물을 참고!
하지만 위 쿼리와 아래쿼리는 순환구조의 plan부분이 차이가 보인다.
정확한 문제점은 무엇인지는 모르지만 두개의 오라클은 약간의 버전차이가 있다.
FTS가 나는 쪽은 오라클 버전이 낮다.
- 오라클 버전 10.2.0.4.0
- 오라클 버전 10.2.0.1.0
< 해결 방법 >
히든 파라미터(Hidden parameter) 중 '_old_connect_by_enabled' 의 값을 true로 변경한다.
(히든 파라미터이므로 spfile수정 후 DB Shutdown하여 적용시킨다.)
히든 파라미터 조회 방법은 아래 게시물을 참고!
1. 히든 파라미터(Hidden parameter) 조회
- sys 계정으로 접속해야 볼 수 있음.
- sqlplus internal 또는 sqlplus '/as sysdba' 등의 Admin계정
SELECT KSPPINM
, KSPPSTVL
FROM X$KSPPI X, X$KSPPCV Y
WHERE X.INDX = Y.INDX
AND X.KSPPINM LIKE '%히든 파라미터명%'
AND SUBSTR(X.KSPPINM, 1, 1) = '_' -- 히든 파라미터는 파라미터명 맨앞에 '_'가 붙어있음
2. 히든 파라미터(Hidden parameter) 변경 또는 수정
- 세션 수정 : ALTER SESSION SET "파라미터명" = 변경값
- 동적파일 수정 : ALTER SYSTEM SET "파라미터명" = 변경값 SCOPE=SPFILE;
- init 파일 수정 : initSid명.ora 내 파라미터명 = 변경값
MView 엠뷰 준비, 생성, 자동리플래시, Materialized View Auto Manual Refresh
2010. 2. 25. 11:15 |1. 준비 사항
- 유저에게 권한 부여
: grant create materialized view to 유저명
- Rewrite 기능 Enabled
: ALTER SYSTEM SET QUERY_REWRITE_ENABLED='TRUE';
- 옵티마이저 모드 변경
: ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
- Job Processes 설정
: C:\oracle\product\10.2.0\db_1\srvm\admin 안에 있는 init.ora 파일을 열어서
: job_queue_processes = 10
# job_queue_processes = 10 #datawarehouse
# job_queue_interval = 10
job_queue_keep_connections = true
2. 생성
- Create 소스 테이블 ( F_SALES, D_ITEM)
: CREATE TABLE F_SALES (
YMD_CD NUMBER not null,
ITEM_CD VARCHAR2(20) not null,
EMP_NO NUMBER not null,
SALE_AMT NUMBER,
SALE_QTY NUMBER);
: CREATE TABLE D_ITEM (
ITEM_CD VARCHAR2(20),
ITEM_NM VARCHAR2(20));
- 테스트 값 입력
: insert into f_sales values (20080509, 'ORA0001', 10, 15000000, 1); commit;
: insert into d_item values ('ORA0001', 'BIEE_101332'); commit;
- Mview 로그 만들기 ( Auto Refresh를 위해 필요)
: create materialized view log on F_SALES with rowid including new values;
- Mview 생성
: CREATE MATERIALIZED VIEW MV_SALES
USING INDEX
REFRESH FORCE
--START WITH sysdate
--NEXT SYSDATE+(1/24/60)
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT A.YMD_CD YMD_CD, A.EMP_NO EMP_NO, B.ITEM_NM, SUM(A.SALE_AMT) SALE_AMT, SUM(A.SALE_QTY) SALE_QTY
FROM F_SALES A, D_ITEM B
WHERE A.ITEM_CD=B.ITEM_CD
GROUP BY A.YMD_CD, A.EMP_NO, B.ITEM_NM
- Mview 수정 ( 오라클 버그로 인해 위 주석처리 한 부분을 수정해줘야 함, 1분에 한번 리플래시되도록)
: alter MATERIALIZED VIEW MV_SALES1 REFRESH FORCE START WITH sysdate NEXT SYSDATE+(1/24/60) WITH ROWID;
3. 검증
- Job이 제대로 등록되었는지 확인( LAST_DATE 와 NEXT_DATE 를 확인한다.)
: select * from user_jobs;
- Mview가 제대로 생성되었는지 확인
: SELECT * FROM MV_SALES;
- Mivew Refresh 기능 확인을 위해 INSERT
: insert into f_sales values (20080509, 'ORA0001', 8, 15000000, 1); commit;
- Refresh 확인
: Job에 나와있는 Next_date 이후 시간에 SELECT * FROM MV_SALES;
4. 참고
- Mview Log 확인하기
: select * from dba_mview_logs
- 수동으로 Refresh 하기
: execute dbms_refresh.refresh('"BKMIN"."MV_SALES"');
작성 : 본인
도움 : Prodba 카페의 '용돌이' 님
| 출처 : http://cafe.naver.com/prodba |
에서 질문하고 답변 얻고 해서 정리했어요~
처음 ORA-3136을 접했을때 생각해봤던 건데 도움이 될까 해서 정리해서 올립니다.
10g가 되면서 inbound_connect_timeout 이 60초로 바뀌게 되었고 그로 인해서 가끔씩 alert에 제목과 같은 메지시가 나오곤 합니다.
우선 ORA-3136이 발생되는 경우를 살펴보면
CASE 1
WIPPY@/oracle> sqlplus /@rac1
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 7 13:33:27 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
이상태에서 타이핑 없이 대기를 하게되면 60초 후에 alert log에 ORA-3136 이 떨어지게 됩니다.
그렇다면 그 60초 동안 어떤일이 발생하는가?
1. ps -ef|grep LOCAL=NO 로 확인해 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다.
2. v$process 나 v$resource_limit를 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다.
위를 통해서 60초를 강제로 설정해 놓은 이유를 알 수 있습니다.
누군가 악의적인 목적으로 저런식의 접속을 한다음 끊지 않으면 설정해 놓은 process개수를 다 채워버리게 되고 정상적인 connection이 처리되지 못하는 문제가 있겠죠. 따라서 60초를 주기로 oracle에서는 저런 process를 강제로 죽이는 것입니다.
ORA-3136을 발생시키지는 않지만 유사한 경우가 있습니다.
CASE 2
WIPPY@/oracle> telnet 192.168.0.11 1521
Trying 192.168.0.11...
Connected to 192.168.0.11 (192.168.0.11).
Escape character is '^]'.
위와 같이 했을 경우에는 다음과 같은 현상이 나타나게 됩니다.
1. ps -ef|grep LOCAL=NO 로 확인해 보면 process하나 늘어나 있는 것을 볼 수 있습니다.
2. v$process 나 v$resource_limit를 보면 변화가 없습니다.
즉 OS상에서는 process가 뜨지만 oracle의 view에는 반영이 되지 않는 상황입니다.
이러한 상황도 system resource를 사용하는 것이기 때문에 그다지 좋은 상황은 아니겠죠.
위에 유사한 두가지 CASE에 대해서 설명을 했는데 ORA-3136 에 대해서 좀 찾아보신 분들은 알겠지만 이 메세지를 안나오게 하기위해서는 sqlnet.ora 에 SQLNET.INBOUND_CONNECT_TIMEOUT = 0 설정을 하거나 listener.ora에 INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 을 설정하라고 합니다.
제가 테스트 해본 결과에 의하면
sqlnet.ora - SQLNET.INBOUND_CONNECT_TIMEOUT = 0 : sqlplus /@RAC1 에만 영향을 줍니다.
listener.ora - INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 : telnet 192.168.0.11 1521 에만 영향을 줍니다.
즉 APP의 접속 형태에 따라 sqlner.ora와 listener.ora를 취사선택하거나 둘다 설정을 해야할 필요성이 있는 것입니다. INBOUND_CONNECT_TIMEOUT을 설정할때는 위와 같은 내용을 인지하고 설정을 하셔야 할것으로 생각됩니다. 무작정 ERROR를 보이지 않게 하기 위해서 설정을 한다면 보안상 심각한 문제를 야기할 수도 있으니까요.
즉 ORA-3136이 발생하게 되면 connection이 늦게 처리되는 APP에 대해서 먼저 확인하고 60초 안에 접속이 이루어 지도록 APP단의 수정을 해야 하는 것이 우선이라고 생각합니다.
## 10.2.0.4 linux에서 테스트한 결과 입니다. 버전에 따라 차이가 있을 수 있음을 .. 이해하시길..
1. as of timestamp로 테이블 데이터 복구하기
- > select * from 테이블명 as of timestamp to_timestamp (날짜,날짜형식) 또는 sysdate-5/1440
2. as of timestamp로 프로시져 복구하기
- > select * from dba_source as of timestamp to_timestamp (날짜,날짜형식) 또는 sysdate-5/1440 where name = '프로시져명'
Sample query)
SQL> select obj#,name from obj$ as of timestamp sysdate-5/1440
where name =’SP_TEST’;
SQL> select obj#,source from source$ as of timestamp sysdate-6/1440
where obj#=53258;
3. Recyclebin의 테이블 복구하기
Flashback Drop - Procedure
In Oracle 10g, when you drop a table, the table is not really dropped. Rather, the table is renamed. The data in the table is still available. You can use the Flashback Drop technologies to recover an accidentally dropped table. The procedure is as follows,
Step 1: Check whether the dropped table is still available in the recycle bin, command.
User level
Scott@prod> show recyclebin;
Database level
dba@prod> Select * from dba_recyclebin;
The original_name column shows the dropped table’s original name. It also has the recycle bin’s name and the dropped time.
Step 2: Use the Flashback Drop command to recover the table
Scott@prod> Flashback Table Employees to before drop [Rename to New_Employees];
Or you can use the system generated name as follows
Scott@prod> Flashback Table “BIN$YkyfmFKl8MLgQwppGAzwwg==$0" to before drop [Rename to New_Employees];
Step 3: To permanently drop the table.
Scott@prod> Drop Table Employees purge;
Scott@prod> purge dba_recyclebin;
Related Posts : Flashback Recovery Timestamp and SCN Conversion
Flashback Table - Privileges
Flashback Table - Procedure
Flashback Query
Important Points - Flashback Table
ASK TOM_Oracle : [멋져부러~]
9i:
SELECT deptno, CURSOR(SELECT * FROM emp where deptno=dept.deptno) nested_TABLE
FROM dept;
-- User defined agregate through pipelined function
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
RETURN VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
loop
fetch cur into tmp;
exit when cur%NOTFOUND;
ret := ret || ',' || tmp;
end loop;
RETURN ret;
END;
/
SELECT distinct
deptno,
SUBSTR(CONCAT_LIST(CURSOR(
SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
)),1,40) employees
FROM emp e;
Followup November 28, 2001 - 2pm Central time zone:
Or, even better, in 9i -- use a user defined aggregate so you don't have run the concat_list
function once per row and then through out the dups (it would be better to write:
select deptno, SUBSTR(CONCAT_LIST(CURSOR(
SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
)),1,40) employees
from ( select distinct deptno from emp );
)
But a good general purpose solution is:
scott@ORA9I.WORLD> create or replace type StringAggType as object
2 (
3 theString varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT StringAggType )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT StringAggType ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN StringAggType,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT StringAggType,
22 ctx2 IN StringAggType)
23 return number
24 );
25 /
Type created.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> create or replace type body StringAggType
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
5 return number
6 is
7 begin
8 sctx := StringAggType( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT StringAggType,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.theString := self.theString || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN StringAggType,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT StringAggType,
32 ctx2 IN StringAggType)
33 return number
34 is
35 begin
36 self.theString := self.theString || ',' || ctx2.theString;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /
Type body created.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> CREATE or replace
2 FUNCTION stringAgg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING StringAggType;
5 /
Function created.
scott@ORA9I.WORLD>
scott@ORA9I.WORLD> column enames format a30
scott@ORA9I.WORLD> select deptno, stringAgg(ename) enames
2 from emp
3 group by deptno
4 /
DEPTNO ENAMES
---------- ------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAME
S,WARD
scott@ORA9I.WORLD>
