help me : got error while creating datafile in NAS(nfs file system) on HPUX ()
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)) ASPRO_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_CODEIN('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