help me : got error while creating datafile in NAS(nfs file system) on HPUX ()
From: jayendra.ahmedabad.acute <i.jayendra_at_gmail.com>
Date: Thu, 22 Apr 2010 07:53:12 -0700 (PDT)
Message-ID: <3ccd014c-34c3-4da0-8d36-e02ea0c4a07f_at_u9g2000prm.googlegroups.com>
syntax:
PROD FD_BALANCE_MST.AMOUNT%TYPE:=0;
BEGIN
BEGIN
SELECT SUM( NVL(M.PROV_INT_AMT,0)) INTO PROD
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD,M.FD_NO,MAX(TRN_DATE) TRN_DATE
END;
RETURN PROD;
END FUNC_GET_ANY_DAY_FD_AC_PROV;
/
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD, SUM(FUNC_GET_ANY_DAY_BALANCE(T2.COMP_CD,T2.BRANCH_CD,T2.GL_INT_TYPE,T2.GL_INT_ACCT_CD,sysdate)) BBAL,
AND NVL(CONF_BAL,0) < 0
bdf result
Error
ORA-01119: error in creating database file '/uatdata/oracle/oradata/ FCR/fcrdata1_01.dbf'
ORA-27086: unable to lock file - already in use HPUX-ia64 Error: 13: Permission denied
Additional information: 8
Date: Thu, 22 Apr 2010 07:53:12 -0700 (PDT)
Message-ID: <3ccd014c-34c3-4da0-8d36-e02ea0c4a07f_at_u9g2000prm.googlegroups.com>
syntax:
CREATE OR REPLACE FUNCTION
FUNC_GET_ANY_DAY_FD_AC_PROV
(A_COMP_CD FD_BALANCE_MST.COMP_CD%TYPE,
A_BRANCH_CD FD_BALANCE_MST.BRANCH_CD%TYPE,
A_ACCT_TYPE FD_BALANCE_MST.ACCT_TYPE%TYPE,
A_ACCT_CD FD_BALANCE_MST.ACCT_CD%TYPE,
A_ASON_DATE FD_BALANCE_MST.TRN_DATE%TYPE)
RETURN NUMBER AS
PROD FD_BALANCE_MST.AMOUNT%TYPE:=0;
BEGIN
BEGIN
SELECT SUM( NVL(M.PROV_INT_AMT,0)) INTO PROD
FROM FD_BALANCE_MST M,
(SELECT
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD,M.FD_NO,MAX(TRN_DATE) TRN_DATE
FROM FD_BALANCE_MST M
WHERE COMP_CD = A_COMP_CD AND
BRANCH_CD = A_BRANCH_CD AND
ACCT_TYPE = A_ACCT_TYPE AND
ACCT_CD = A_ACCT_CD AND
TRN_DATE <=A_ASON_DATE
GROUP BY
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD,M.FD_NO
)T
WHERE M.COMP_CD = T.COMP_CD
AND M.BRANCH_CD = T.BRANCH_CD
AND M.ACCT_TYPE = T.ACCT_TYPE
AND M.ACCT_CD = T.ACCT_CD
AND M.FD_NO = T.FD_NO
AND M.TRN_DATE = T.TRN_DATE
GROUP BY M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD;
EXCEPTION WHEN NO_DATA_FOUND THEN
PROD := 0;
END;
RETURN PROD;
END FUNC_GET_ANY_DAY_FD_AC_PROV;
/
SELECT *
FROM (SELECT --
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD, SUM(FUNC_GET_ANY_DAY_BALANCE(T2.COMP_CD,T2.BRANCH_CD,T2.GL_INT_TYPE,T2.GL_INT_ACCT_CD,sysdate)) BBAL,
SUM(M.PRO_BAL) FBAL, SUM(M.BAL) FBBAL
FROM (SELECT
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD,
SUM(NVL(M.PROV_INT_AMT,0)) AS
PRO_BAL,
FUNC_GET_ANY_DAY_FD_AC_PROV(M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD,sysdate)
BAL
FROM FD_DTL M
-- WHERE TRIM(COMP_CD) = TRIM(COMP)
-- AND TRIM(BRANCH_CD) =
TRIM(BRANCH)
GROUP BY
M.COMP_CD,M.BRANCH_CD,M.ACCT_TYPE,M.ACCT_CD
) M ,
ACCT_TYPE_MST T2
WHERE M.COMP_CD = T2.COMP_CD
AND M.BRANCH_CD = T2.BRANCH_CD
AND M.ACCT_TYPE = T2.ACCT_TYPE
-- AND M.ACCT_CD = T2.ACCT_CD
-- AND M.ACCT_TYPE BETWEEN 200 AND 215
)
WHERE ( (NVL(BBAL,0)-NVL(FBBAL,0)) <> 0 OR
(NVL(FBAL,0)-NVL(FBBAL,0)) <> 0
)
;
'LOC','LOC0','SH','SH01','REC','RECM','SB','SB01','CA','CA01','AUTO','FA01','FA02','FAU1','FAU2','FCUM','FD','FD01','FD02','IFDN','IFCN','IFDA','IFCA' ;
SELECT M.COMP_CD, M.BRANCH_CD, M.ACCT_TYPE, M.ACCT_CD
FROM ACCT_MST M
WHERE EXISTS ( SELECT *
FROM (SELECT COMP_CD,BRANCH_CD,ACCT_TYPE
FROM ACCT_TYPE_MST
-- WHERE COMP_CD = COMP
-- AND BRANCH-CD = BRANCH
WHERE PARENT_TYPE IN ( SELECT CODE
FROM SYS_TYPE_MST
WHERE PARENT_CODE
IN('LOC','LOC0','SH','SH01','REC',
'RECM','SB','SB01','CA','CA01',
'AUTO','FA01','FA02','FAU1','FAU2',
'FCUM','FD','FD01','FD02','IFDN',
'IFCN','IFDA','IFCA')
)
) T
WHERE M.COMP_CD = T.COMP_CD
AND M.BRANCH_CD = T.BRANCH_CD
AND M.ACCT_TYPE = T.ACCT_TYPE
)
AND NVL(CONF_BAL,0) < 0
;
SELECT *
FROM (SELECT M.COMP_CD, M.BRANCH_CD, T.GL_TYPE
ACCT_TYPE, T.GL_ACCT_CD ACCT_CD,
SUM(NVL(CONF_BAL,0)) MBAL,
FUNC_GET_ANY_DAY_BALANCE(M.COMP_CD,
M.BRANCH_CD, T.GL_TYPE, T.GL_ACCT_CD,SYSDATE) GBAL
FROM ACCT_MST M,
ACCT_TYPE_MST T
WHERE /*COMP_CD = COMP
AND BRANCH_CD = BRANCH
AND*/ M.COMP_CD = T.COMP_CD
AND M.BRANCH_CD = T.BRANCH_CD
AND M.ACCT_TYPE = T.ACCT_TYPE
AND ( ( NVL(T.CR_GL_TYPE,'0') = '0' AND
NVL(T.CR_GL_ACCT_CD,'0') = '0'
)OR
( NVL(T.DR_GL_TYPE,'0') = '0' OR
NVL(T.DR_GL_ACCT_CD,'0') = '0'
)
)
AND ( ( NVL(T.GL_TYPE,'0') <> '0' AND
NVL(T.GL_ACCT_CD,'0') <> '0'
)
)
GROUP BY M.COMP_CD, M.BRANCH_CD, T.GL_TYPE,
T.GL_ACCT_CD
)
WHERE MBAL <> GBAL
;
SELECT *
FROM (SELECT M.COMP_CD, M.BRANCH_CD,
SUM(FUNC_GET_ANY_DAY_IR_BALANCE(M.COMP_CD, M.BRANCH_CD, M.ACCT_TYPE, M.ACCT_CD, SYSDATE)
) MBAL,
FUNC_GET_ANY_DAY_BALANCE(M.COMP_CD,
M.BRANCH_CD,
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,211)),
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,212)),
SYSDATE)
REC_BAL,
FUNC_GET_ANY_DAY_BALANCE(M.COMP_CD,
M.BRANCH_CD,
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,213)),
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,214)),
SYSDATE)
RES_BAL
FROM ACCT_MST M
-- WHERE TRIM(M.COMP_CD) = TRIM(COMP)
-- AND TRIM(M.BRANCH_CD) = TRIM(BRANCH)
WHERE TRIM(M.COMP_CD) = '132'
AND TRIM(M.BRANCH_CD) = '002'
GROUP BY M.COMP_CD, M.BRANCH_CD
)
WHERE ABS(MBAL) = ABS(REC_BAL) OR
ABS(MBAL) = ABS(RES_BAL)
;
SELECT *
FROM (SELECT M.COMP_CD, M.BRANCH_CD,
SUM(FUNC_GET_ANY_DAY_IR_BALANCE(M.COMP_CD, M.BRANCH_CD, M.ACCT_TYPE, M.ACCT_CD, TRAN_DAT)
) MBAL,
FUNC_GET_ANY_DAY_BALANCE(M.COMP_CD,
M.BRANCH_CD,
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,211)),
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,212)),
TRAN_DAT)
REC_BAL,
FUNC_GET_ANY_DAY_BALANCE(M.COMP_CD,
M.BRANCH_CD,
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,213)),
TRIM(FUNC_SYS_PARA_MST_VALUE(M.COMP_CD,M.BRANCH_CD,214)),
TRAN_DAT)
RES_BAL
FROM SHARE_WARRANT M
WHERE TRIM(M.COMP_CD) = TRIM(COMP)
AND TRIM(M.BRANCH_CD) = TRIM(BRANCH)
GROUP BY M.COMP_CD, M.BRANCH_CD
)
WHERE ABS(MBAL) <> ABS(REC_BAL) OR
ABS(MBAL) <> ABS(RES_BAL)
;
SELECT *
FROM (SELECT TO_COMP_CD, TO_BRANCH_CD,
TO_ACCT_TYPE, TO_ACCT_CD,
SUM(DIVIDEND_AMOUNT) DIV_AMT,
FUNC_GET_ANY_DAY_BALANCE(TO_COMP_CD,
TO_BRANCH_CD, TO_ACCT_TYPE, TO_ACCT_CD,
SYSDATE) GBAL
FROM SHARE_WARRANT
WHERE /*TRIM(M.COMP_CD) = TRIM(COMP)
AND TRIM(M.BRANCH_CD) = TRIM(BRANCH)
AND */NVL(TO_COMP_CD,'X') <> 'X'
AND NVL(TO_BRANCH_CD,'X') <> 'X'
AND NVL(TO_ACCT_TYPE,'X') <> 'X'
AND NVL(TO_ACCT_CD,'X') <> 'X'
AND NVL(PAID,'N') = 'N'
GROUP BY TO_COMP_CD, TO_BRANCH_CD,
TO_ACCT_TYPE, TO_ACCT_CD
)
WHERE DIV_AMT <> GBAL
CREATE TABLESPACE FCRDATA1
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
DATAFILE '/nasora/fcrdata1_01.dbf' SIZE 100 M REUSE AUTOEXTEND ON
NEXT 1 M,
'/nasora/fcrdata1_02.dbf' SIZE 100 M REUSE AUTOEXTEND ON NEXT 1 M
--,
-- '/uatdata/oracle/oradata/FCR/fcrdata1_03.dbf' SIZE 10 M REUSE
AUTOEXTEND ON NEXT 1 M
AUTOALLOCATE
LOGGING
ONLINE
PERMANENT
BLOCKSIZE 8 K
/
bdf result
drwxrwxrwx 2 root sys 96 Jul 13 2007 mnt drwxrwxrwx 5 oracle oinstall 103 Apr 22 20:03 nasora dr-xr-xr-x 2 root root 96 Nov 17 2006 net dr-xr-xr-x 70 bin bin 8192 Dec 21 2006 opt drwxrwxrwx 10 oracle oinstall 1024 Apr 22 13:15 oracle -rw-r--r-- 1 root sys 0 Feb 4 2008 oracle.prefs drwxrwxrwx 5 root sys 8192 Jun 26 2008 samba dr-xr-xr-x 15 bin bin 8192 Dec 21 2006 sbin ---------------------------
Error
ORA-01119: error in creating database file '/uatdata/oracle/oradata/ FCR/fcrdata1_01.dbf'
ORA-27086: unable to lock file - already in use HPUX-ia64 Error: 13: Permission denied
Additional information: 8
NAS(nfs file system) on HPUX
NAS is os iomega (EMc system)
please help me Received on Thu Apr 22 2010 - 09:53:12 CDT