Resolution
In order to resolve this issue, go to the Windows Explorer and follow the below mentioned steps.
Right-click on the inaccessible hard drive.
Click Properties.
Go to the Security tab, and then click Advanced.
Click ‘Edit’ by going to the Owner tab.
Modify the ownership of the desired account.
Alternatively, you can also try the following option, if you are not able to access any file or folder on a Windows 7 drive.
Right-click on the inaccessible file or folder.
Click Properties.
Select the Security tab.
Click your name Under Group or user name. This would show you the permissions you have to access the file and folder.
You can also try the following method
Change the drive letter for inaccessible hard drive.
Run the following command: chkdsk /r I:
Try to boot into the safe mode and access the HDD.
Try accessing the HDD in Windows 7 Ultimate and Home Premium.
You will be able to access the drive, as chkdsk command would have deleted the files that were causing the problem.
Wednesday, January 27, 2016
ORA-29264 unknown or unsupported URL scheme
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29264: unknown or unsupported URL scheme
Error: ORA-29264 (ORA-29264)
Text: unknown or unsupported URL scheme
---------------------------------------------------------------------------
Cause: The URL scheme was unknown or unsupported.
Action: Check the URL to make sure that the scheme is valid and
supported.
Please remove any leading or trailing characters (space, enter etc) in the profile's value.
The code works fine when
i use
http://www.***.com/image?imageid=1234 worked
but when i use
http://www.finance.***.com/image?imageid=1234
I fails with the specified error.
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29264: unknown or unsupported URL scheme
Error: ORA-29264 (ORA-29264)
Text: unknown or unsupported URL scheme
---------------------------------------------------------------------------
Cause: The URL scheme was unknown or unsupported.
Action: Check the URL to make sure that the scheme is valid and
supported.
Please remove any leading or trailing characters (space, enter etc) in the profile's value.
The code works fine when
i use
http://www.***.com/image?imageid=1234 worked
but when i use
http://www.finance.***.com/image?imageid=1234
I fails with the specified error.
ORA-29273 ORA-06512 ORA-29259
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29259: end-of-input reached
ORA-06512: at line 1
Error: ORA-29259 (ORA-29259)
Text: end-of-input reached
---------------------------------------------------------------------------
Cause: The end of the input was reached.
Action: If the end of the input is reached prematurely, check if the
input source terminates prematurely. Otherwise, close the
connection to the input.
Please remove any leading or trailing characters (space, enter etc) in the value.
The issue was with proxy setting.
Once the UTL_HTTP.SET_PROXY was set appropriately, I no longer get the end-of-input error.
URL is not properly set
CAUSE
The site that is accessed via utl_http requires mutual authentication.
SOLUTION
This is not possible in 10g . This was implemented in 11g only.
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'LD_AD_IE_SPAN_NSEF_BPL-4' ORDER BY 2 DESC;
SELECT UTL_HTTP.REQUEST ('http://172.168.1.104') FROM DUAL;
SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('172.168.1.104'));
select utl_http.request('https://marc.charpentier.com/ws/services/Payment?wsdl',NULL,'file:E:\wallet','******') from dual;
recreate ACL privileges
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29259: end-of-input reached
ORA-06512: at line 1
Error: ORA-29259 (ORA-29259)
Text: end-of-input reached
---------------------------------------------------------------------------
Cause: The end of the input was reached.
Action: If the end of the input is reached prematurely, check if the
input source terminates prematurely. Otherwise, close the
connection to the input.
Please remove any leading or trailing characters (space, enter etc) in the value.
The issue was with proxy setting.
Once the UTL_HTTP.SET_PROXY was set appropriately, I no longer get the end-of-input error.
URL is not properly set
CAUSE
The site that is accessed via utl_http requires mutual authentication.
SOLUTION
This is not possible in 10g . This was implemented in 11g only.
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'LD_AD_IE_SPAN_NSEF_BPL-4' ORDER BY 2 DESC;
SELECT UTL_HTTP.REQUEST ('http://172.168.1.104') FROM DUAL;
SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('172.168.1.104'));
select utl_http.request('https://marc.charpentier.com/ws/services/Payment?wsdl',NULL,'file:E:\wallet','******') from dual;
recreate ACL privileges
TNSaddress already in use ora12542
1)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Determines the time that must elapse before TCP can release a closed connection and reuse its resources. This interval between closure and release is known as the TIME_WAIT state or 2MSL state. During this time, the connection can be reopened at much less cost to the client and server than establishing a new connection.Value Name: MaxUserPort
Type: REG_DWORD
Value: 65534
2)
TcpTimedWaitDelay
Value Type: REG_DWORD
Value: 30
3)
netsh interface tcp show global
netsh int tcp set global autotuninglevel=disabled
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Determines the time that must elapse before TCP can release a closed connection and reuse its resources. This interval between closure and release is known as the TIME_WAIT state or 2MSL state. During this time, the connection can be reopened at much less cost to the client and server than establishing a new connection.Value Name: MaxUserPort
Type: REG_DWORD
Value: 65534
2)
TcpTimedWaitDelay
Value Type: REG_DWORD
Value: 30
3)
netsh interface tcp show global
netsh int tcp set global autotuninglevel=disabled
ORA-08104 this index object 4145783 is being online built or rebuilt
*
ERROR at line 1:
ORA-20000: this index object "LDBO"."IDXCONTRACTBILL" is being online built or
rebuilt
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL>
SQL>
SQL>
SQL> E
this index object is being online built or rebuilt
Cause:
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is on going when in fact it is not.
Solution:
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. More details about the function on metalink – Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]
Run the following to resolve:
declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/
select min(object_id) from dba_objects where object_name = 'IDXCONTRACTBILL'
392745
declare
ret boolean;
begin
ret:=dbms_repair.ONLINE_INDEX_CLEAN(392745);
end;
/
exit
drop index LDBO.IDXCONTRACTBILL;
CREATE INDEX LDBO.IDXCONTRACTBILL ON LDBO.CONTRACTDETAILS (FIRMNUMBER,NFINANCIALYEAR,CONTRACT);
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE;
ALTER INDEX LDBO.IDXCONTRACTINDEX REBUILD ONLINE;
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LDBO' , tabname => 'CONTRACTDETAILS',cascade => true, estimate_percent => 100,method_opt=>'for all columns size 254', granularity => 'ALL', degree => DBMS_STATS.DEFAULT_DEGREE, no_invalidate => false, force=> TRUE );
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE pctfree 20;
ERROR at line 1:
ORA-20000: this index object "LDBO"."IDXCONTRACTBILL" is being online built or
rebuilt
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL>
SQL>
SQL>
SQL> E
this index object is being online built or rebuilt
Cause:
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is on going when in fact it is not.
Solution:
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. More details about the function on metalink – Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]
Run the following to resolve:
declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/
select min(object_id) from dba_objects where object_name = 'IDXCONTRACTBILL'
392745
declare
ret boolean;
begin
ret:=dbms_repair.ONLINE_INDEX_CLEAN(392745);
end;
/
exit
drop index LDBO.IDXCONTRACTBILL;
CREATE INDEX LDBO.IDXCONTRACTBILL ON LDBO.CONTRACTDETAILS (FIRMNUMBER,NFINANCIALYEAR,CONTRACT);
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE;
ALTER INDEX LDBO.IDXCONTRACTINDEX REBUILD ONLINE;
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'LDBO' , tabname => 'CONTRACTDETAILS',cascade => true, estimate_percent => 100,method_opt=>'for all columns size 254', granularity => 'ALL', degree => DBMS_STATS.DEFAULT_DEGREE, no_invalidate => false, force=> TRUE );
ALTER INDEX LDBO.IDXCONTRACTBILL REBUILD ONLINE pctfree 20;
TNS-12560 TNS protocol adapter error opiodr aborting process unknown ospid
Fatal NI connect error 12560, connecting to:
(LOCAL=NO)
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
Time: 31-JAN-2015 15:52:27
Tracing not turned on.
Tns error struct:
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (11852) as a result of ORA-609
The Solution
Change the user account that the listener is running as to the same as the one that the database service runs as, restart the listener and this should resolve the issue.
dim-00019 create service error
set ORACLE_SID=orcl
oradim -new -sid ORCL -startmode manual -spfile
Enter password for Oracle service user: <PASSWORD IS ENTERED>
DIM-00019: create service error
O/S-Error: (OS 87) The parameter is incorrect.
You said "Even after deleting the service"
How have you been deleting the service ?
Check out if your service still exists at the registry level
1. regedit
2. go towards HKEY_LOCAL_MACHINE > SYSTEM > CURRENT CONTROL SET > SERVICES > and check the Oracle services.
If your service is still present > delete your service at this level
As a last resort you might want to reboot your server (if possible)
C:\>oradim -delete -sid cs62
C:\>oradim -new -sid cs62
C:\>set oracle_sid=cs62
C:\>sqlplus /nolog
oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr
oduct\10.2.0\db_1\database\initorcl.ora'
Instance created.
DIM-00019: create service error
O/S-Error: (OS 2) The system cannot find the file specified.
Solution of the Problem
On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new oracle software in your windows machine without any database and then create oracle instance and service using oradim.
oradim -new -sid ORCL -startmode manual -spfile
Enter password for Oracle service user: <PASSWORD IS ENTERED>
DIM-00019: create service error
O/S-Error: (OS 87) The parameter is incorrect.
You said "Even after deleting the service"
How have you been deleting the service ?
Check out if your service still exists at the registry level
1. regedit
2. go towards HKEY_LOCAL_MACHINE > SYSTEM > CURRENT CONTROL SET > SERVICES > and check the Oracle services.
If your service is still present > delete your service at this level
As a last resort you might want to reboot your server (if possible)
C:\>oradim -delete -sid cs62
C:\>oradim -new -sid cs62
C:\>set oracle_sid=cs62
C:\>sqlplus /nolog
oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr
oduct\10.2.0\db_1\database\initorcl.ora'
Instance created.
DIM-00019: create service error
O/S-Error: (OS 2) The system cannot find the file specified.
Solution of the Problem
On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new oracle software in your windows machine without any database and then create oracle instance and service using oradim.
LD Error building key for index
Error building key for index "c:\users\suprim~1.moh\appdata\local\temp\00004ftl001q.cdx" tag "Clrisk".
cursor_sharing should be exact
cursor_sharing should be exact
ORA 01950 no privileges on tablespace
Saved
Record Not Saved to DataBase ORA-01950: no privileges on tablespace 'USR'
ORA-06512: at "LDBO.SP_SCHEDULERSETUP", line 735
sb17271112
E:\ldoutput
SYS@FWS1213> grant unlimited tablespace to ldbo;
Grant succeeded.
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on USR;
User altered.
SYS@FWS1213>
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on INDX;
ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;
grant unlimited tablespace TO LDBO;
Select Tablespace_Name, Username, Bytes / 1024 / 1024/ 1024 "Used GB", Max_Bytes / 1024 / 1024/ 1024 As "Max GB" from dba_ts_quotas;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB",Increment_By from dba_data_files;
Record Not Saved to DataBase ORA-01950: no privileges on tablespace 'USR'
ORA-06512: at "LDBO.SP_SCHEDULERSETUP", line 735
sb17271112
E:\ldoutput
SYS@FWS1213> grant unlimited tablespace to ldbo;
Grant succeeded.
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on USR;
User altered.
SYS@FWS1213>
SYS@FWS1213> ALTER USER LDBO QUOTA UNLIMITED on INDX;
ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;
grant unlimited tablespace TO LDBO;
Select Tablespace_Name, Username, Bytes / 1024 / 1024/ 1024 "Used GB", Max_Bytes / 1024 / 1024/ 1024 As "Max GB" from dba_ts_quotas;
select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB",Increment_By from dba_data_files;
TROUBLESHOOTING ORA-03135 connection lost contact from ODP.NET connections with connection pooling enabled
SYMPTOMS
ODP.NET applications that have connection pooling enabled (which is the default) may intermittently experience the following error:
ORA-03135: connection lost contact
CAUSE
The nature of connection pooling is that there are frequently long lived idle connections, which may end up being disconnected by 3rd party software such as firewalls and load balancers.
Typically the exception occurs when trying to use a connection via a DataReader, DataAdapter, OracleCommand, etc, rather than during the OracleConnection::Open call.
For ease of troubleshooting, often the behavior can be reproduced from a SQLPlus session if left idle long enough.
SOLUTION
The ideal solution is to address the issue in the environment to prevent the disconnect from occurring, however several workarounds can be employed:
1) Simply catch the exception, and retry the operation.
2) Use the ODP.NET connection string parameter "validate connection=true" By default, it is set to false, which means that no checking of the connection is done when it is retrieved from the pool as a result of a OracleConnection::Open call. Setting it to true will cause ODP.NET to verify that the connection is still good by making a database round trip. If a problem with the connection is found, it is removed from the pool, and another connection is tried internally.
Note that while this typically alleviates ora-3135, it does have performance implications as every con.Open call will result in a round trip to the database. The overhead may or may not be significant depending upon application performance, so testing should be done in your environment.
3) Enable Dead Connection Detection on the database, which will result in a probe packet being sent from database to client periodically, which will usually prevent the firewall or load balancer from seeing the connection as idle.
Note 151972.1 Dead Connection Detection (DCD) Explained
4) Connection pooling can be disabled entirely, but this will likely have a much larger performance impact. To disable connection pooling, add "pooling=false" to the connection string.
ODP.NET applications that have connection pooling enabled (which is the default) may intermittently experience the following error:
ORA-03135: connection lost contact
CAUSE
The nature of connection pooling is that there are frequently long lived idle connections, which may end up being disconnected by 3rd party software such as firewalls and load balancers.
Typically the exception occurs when trying to use a connection via a DataReader, DataAdapter, OracleCommand, etc, rather than during the OracleConnection::Open call.
For ease of troubleshooting, often the behavior can be reproduced from a SQLPlus session if left idle long enough.
SOLUTION
The ideal solution is to address the issue in the environment to prevent the disconnect from occurring, however several workarounds can be employed:
1) Simply catch the exception, and retry the operation.
2) Use the ODP.NET connection string parameter "validate connection=true" By default, it is set to false, which means that no checking of the connection is done when it is retrieved from the pool as a result of a OracleConnection::Open call. Setting it to true will cause ODP.NET to verify that the connection is still good by making a database round trip. If a problem with the connection is found, it is removed from the pool, and another connection is tried internally.
Note that while this typically alleviates ora-3135, it does have performance implications as every con.Open call will result in a round trip to the database. The overhead may or may not be significant depending upon application performance, so testing should be done in your environment.
3) Enable Dead Connection Detection on the database, which will result in a probe packet being sent from database to client periodically, which will usually prevent the firewall or load balancer from seeing the connection as idle.
Note 151972.1 Dead Connection Detection (DCD) Explained
4) Connection pooling can be disabled entirely, but this will likely have a much larger performance impact. To disable connection pooling, add "pooling=false" to the connection string.
Table statistics being locked after import export
Table statistics get locked when exporting only the table structures with DataPump. This situation is identified as an issue that occurs with Oracle 10.2. Using DataPump data is not exported or imported if the option CONTENT = METADATA_ONLY is set.
To resolve this there are two options listed on My Oracle Support.
1. After the import unlock the statistics for tables using the command:
execute DBMS_STATS.UNLOCK_TABLE_STATS('owner','table_name');
NOTE the statistics can also be unlocked at the schema level.
2. Do not import table statistics using the option EXCLUDE=TABLE_STATISTICS.
To resolve this there are two options listed on My Oracle Support.
1. After the import unlock the statistics for tables using the command:
execute DBMS_STATS.UNLOCK_TABLE_STATS('owner','table_name');
NOTE the statistics can also be unlocked at the schema level.
2. Do not import table statistics using the option EXCLUDE=TABLE_STATISTICS.
ORA-08103 object no longer exists
check alert log
block corruption
analyze validate structure
Doc ID 8103.1
You can try to drop and rebuild the index(es) and see whether it solves the problem.
UNDO_RETENTION
ORA-08103 object no longer exists
Cause: The object has been deleted by another user since the operation began. Or a prior incomplete recovery restored the database to a point in time during the deletion of the object
Action: Remove references to the object or delete the object if this is the result of an incomplete recovery.
http://www.dba-oracle.com/t_ora_08103_object_no_longer_exists.htm
There are several likely reasons for this error, and you should always check your alert log for details. This error often requires opening a service request with MOSC:
0: A software bug (see list below).
1: You are not signed-on as the table owner.
2: Database corruption of a header block.
3: Accidental delete of the target table. (check the recyclebin)
4: Data file I/O error (check alert log)
5: corruption in UNDO log (drop and re-create)
6. An index is disabled or is offline.
Possible solution include:
1: Set db_file_multiblock_read_count to 1.
2: Run dbv on all data files.
3: Run catalog.sql, catproc.sql and utlrp.sql to ensure no data dictionary corruption.
4: Purge recyclebin & dba_recyclebin.
5: When the ORA-08103 is on an index operation, place the index online or make index usable
select index_name , status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
IDX_CUST UNUSABLE
alter index idx_cust rebuild online;
more than 32678 bytes clob DBMS_LOB.GETLENGTH return null
can not input into clob
solution
use utl_file, generate and input
Go to Tools => Preferences from the menu.
Then click 'Output' (on the left side, in the 'Oracle' menu), and see if the 'Enabled' checkbox is checked or not...
lncount:= CEIL(DBMS_LOB.GETLENGTH(icJournaldetail)/256);
declare this
lcStr clob := icJournaldetail;
one partition has very high number of rows then how i split partition. Error ORA-14080 partition cannot be split along the specified high bound
Metalink :
ORA-14080 when trying to Split a Partition. [ID 100299.1]
Split lower and not higher.
ORA-14080 when trying to Split a Partition. [ID 100299.1]
Split lower and not higher.
Data pump export to a network mapped drive fails with ORA-39002 ORA-39070
It is working fine when both Server is in 2008 and above
wrong
create or replace directory z as 'Z:\backupset';
right
create or replace directory z as '\\172.16.1.36\c$\DB_Backup\backupset';
C:\Users\Administrator>expdp schemas=idencraft userid=system/sys directory=z logfile=data_pump_dir:log.log
Cause of the Problem
The CREATE DIRECTORY command used the drive letter, not the UNC naming convention (\\<server>\<sharepoint>).
In this example: CREATE DIRECTORY dump_dir AS 'Z:\backupset';
The account running the command does not have the necessary privileges to access the mapped network disk.
The user LOCAL SYSTEM normally will not have file system permissions or network permissions.
Solution
Oracle service runs under local system account by default and this local system account cannot see what you have mapped as the mappings are unique to each user account. You can check Start --> Run --> Services.msc --> OracleServiceXXXX ---> Properties --> Logon
The quick solution would be to run the Oracle service under the user account you used to create the mapping but this creates a couple of additional problems e.g. user account password expires or the account is locked.
Check if the mapping granted rights to oradba group
wrong
create or replace directory z as 'Z:\backupset';
right
create or replace directory z as '\\172.16.1.36\c$\DB_Backup\backupset';
C:\Users\Administrator>expdp schemas=idencraft userid=system/sys directory=z logfile=data_pump_dir:log.log
Cause of the Problem
The CREATE DIRECTORY command used the drive letter, not the UNC naming convention (\\<server>\<sharepoint>).
In this example: CREATE DIRECTORY dump_dir AS 'Z:\backupset';
The account running the command does not have the necessary privileges to access the mapped network disk.
The user LOCAL SYSTEM normally will not have file system permissions or network permissions.
Solution
Oracle service runs under local system account by default and this local system account cannot see what you have mapped as the mappings are unique to each user account. You can check Start --> Run --> Services.msc --> OracleServiceXXXX ---> Properties --> Logon
The quick solution would be to run the Oracle service under the user account you used to create the mapping but this creates a couple of additional problems e.g. user account password expires or the account is locked.
Check if the mapping granted rights to oradba group
ORA-29283 Using UTL_FILE On Windows
SYMPTOMS
Using UTL_FILE and attempting to perform a read or write on a file stored on a network resource on Windows using UNC notation like
\\myserver\myshare
fails with the following error:
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at line 4
CAUSE
The reason for this error may be for various reasons such as permissions or the network resource is unavailable.
SOLUTION
1. Verify the user that the Oracle Database is started as and verify the permissions for this network resource to ensure that the correct permissions have been set on the share.
2. Login as the same user that the database is started as and test the resource availability and access to files on the share for both reading and writing.
3. Verify the UTL_FILE_DIR database parameter or the database directory object are setup correctly and referencing the correct network resource.
TIP: Using Mapped drives is not recommended as this resource is only available when the user that created it is logged into the server. For most Windows Servers hosting Databases, this is not the normal use case scenario.
SOLUTION
The error can be raised because of the following causes.
General causes and solutions of ORA-29283 error:
1. Check whether the schema where the PLSQL code is run has READ,WRITE permission on the database directory.
If SCOTT is the schema , then login as SYS grant required permission.
GRANT READ,WRITE ON DIRECTORY <DIR_NAME> TO SCOTT
2. Check whether the path used in database directory physically exists on the OS.
Eg :
CREATE OR REPLACE DIRECTORY DIRNAME as '/home/test';
/home/test should exist on the OS. When the database directory is created , oracle doesnt check the validity of the directory path used. The validation of the path is done at the runtime. Hence it is necessary to check the file path on OS before the program is executed.
3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed.
]$ ls -l /u01/app/oracle/product/10.2/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 96725778 Jul 7 2008 /u01/app/oracle/product/10.2/db_1/bin/oracle
"oracle" is the owner of the oracle executable. oracle - user should have enough permission on the directory.
]$ su oracle
Password:
]$ whoami
oracle
]$touch /home/test/abc.txt
You should be able to create a file using touch command login as the owner of the oracle process.
4. Make sure you have restarted the listener once you have done any OS level changes to the directories accessed by RDBMS.
Once you have done any changes in OS level permission, you should always restart the listener so that it inherits the recently changed permission.
Else oracle will sometime raise a ora-29283 error.
5. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle.
If the file is created with the rw-r-- permission and owned by another user, RDBMS wont be able to read the file. In such case you will have to change the permissions of the file to rw-rw--
chmod 750 <filename>
6. Using remote directories :
UTL_FILE package can access only server side files where the database instance is running. You cannot access client side files. If you are using UNIX system, then create a NFS mount of the client folder on the server . If on Windows platform then go through
Note 45172.1 : Running UTL_FILE on Windows NT
Note 1034188.6: INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive
Solution:
Start the Oracle service as a user who has the same permissions as SYSTEM, and also who has access to the shared directory.
7. Check ORA_NLS on application server :
If the ORA_NLSXX where XX is 32, 33 or 10 is set, it must be set before starting the database and on the client side too.
Note 77442.1 : ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained
Using UTL_FILE and attempting to perform a read or write on a file stored on a network resource on Windows using UNC notation like
\\myserver\myshare
fails with the following error:
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at line 4
CAUSE
The reason for this error may be for various reasons such as permissions or the network resource is unavailable.
SOLUTION
1. Verify the user that the Oracle Database is started as and verify the permissions for this network resource to ensure that the correct permissions have been set on the share.
2. Login as the same user that the database is started as and test the resource availability and access to files on the share for both reading and writing.
3. Verify the UTL_FILE_DIR database parameter or the database directory object are setup correctly and referencing the correct network resource.
TIP: Using Mapped drives is not recommended as this resource is only available when the user that created it is logged into the server. For most Windows Servers hosting Databases, this is not the normal use case scenario.
SOLUTION
The error can be raised because of the following causes.
General causes and solutions of ORA-29283 error:
1. Check whether the schema where the PLSQL code is run has READ,WRITE permission on the database directory.
If SCOTT is the schema , then login as SYS grant required permission.
GRANT READ,WRITE ON DIRECTORY <DIR_NAME> TO SCOTT
2. Check whether the path used in database directory physically exists on the OS.
Eg :
CREATE OR REPLACE DIRECTORY DIRNAME as '/home/test';
/home/test should exist on the OS. When the database directory is created , oracle doesnt check the validity of the directory path used. The validation of the path is done at the runtime. Hence it is necessary to check the file path on OS before the program is executed.
3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed.
]$ ls -l /u01/app/oracle/product/10.2/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 96725778 Jul 7 2008 /u01/app/oracle/product/10.2/db_1/bin/oracle
"oracle" is the owner of the oracle executable. oracle - user should have enough permission on the directory.
]$ su oracle
Password:
]$ whoami
oracle
]$touch /home/test/abc.txt
You should be able to create a file using touch command login as the owner of the oracle process.
4. Make sure you have restarted the listener once you have done any OS level changes to the directories accessed by RDBMS.
Once you have done any changes in OS level permission, you should always restart the listener so that it inherits the recently changed permission.
Else oracle will sometime raise a ora-29283 error.
5. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle.
If the file is created with the rw-r-- permission and owned by another user, RDBMS wont be able to read the file. In such case you will have to change the permissions of the file to rw-rw--
chmod 750 <filename>
6. Using remote directories :
UTL_FILE package can access only server side files where the database instance is running. You cannot access client side files. If you are using UNIX system, then create a NFS mount of the client folder on the server . If on Windows platform then go through
Note 45172.1 : Running UTL_FILE on Windows NT
Note 1034188.6: INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive
Solution:
Start the Oracle service as a user who has the same permissions as SYSTEM, and also who has access to the shared directory.
7. Check ORA_NLS on application server :
If the ORA_NLSXX where XX is 32, 33 or 10 is set, it must be set before starting the database and on the client side too.
Note 77442.1 : ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained
ORA-04091 table DPNSDL.TBLERRORLIST is mutating, trigger function may not see it ORA-04091 ORA-06512 ORA-04088
ORA-04091: table is mutating, trigger/function may not see it
ORA-04091 ORA-06512 ORA-04088
Reason:
Mutating trigger error occurs when you refer the same table in the trigger code, on which the trigger is defined..
And here, it is very clear that your Update will call the trigger, which will cause another updates, which will call the trigger....... Infinite loop..
SOLUTION
Modify your custom code to not interfere with the target table involved in the update process of the mapping.
Use procedure inspite of trigger
Use Statement level trigger inspite of rowlevel(for each row :old.testorstatus != :new.testorstatus )
Compound Trigger Solution
ORA-04091 ORA-06512 ORA-04088
Reason:
Mutating trigger error occurs when you refer the same table in the trigger code, on which the trigger is defined..
And here, it is very clear that your Update will call the trigger, which will cause another updates, which will call the trigger....... Infinite loop..
SOLUTION
Modify your custom code to not interfere with the target table involved in the update process of the mapping.
Use procedure inspite of trigger
Use Statement level trigger inspite of rowlevel(for each row :old.testorstatus != :new.testorstatus )
Compound Trigger Solution
Windows cannot be installed on this disk. The disk may fail soon
Run chkdsk command from Windows 7 installation media.
Press Shift + F10 to open a Command Prompt and reformat disk with Diskpart command while you see the first installer screen.
Reset the BIOS to defaults and make sure there is no setting in the BIOS that prevents writing to the boot sector. Also make sure the BIOS is actually detecting the hard drive correctly
hard drive manufacturer's diagnostic tool.
chkdsk /f /r
at time of windows installation media
Repair your computer
In command prompt type diskpart to enter the utility.
Type select disk # replacing "#" with the drive number of the one you wish to format. To see a list of disks, type list disk.
Type clean. This deletes all volumes from the drive.
Type convert mbr to convert the disk to mbr.
convert gpt to revert back to GPT. (Optional step)
Start DISKPART.
Type LIST DISK and identify your SSD disk number (from 0 to n disks).
Type SELECT DISK <n> where <n> is your SSD disk number.
Type CLEAN
Type CREATE PARTITION PRIMARY
Type ACTIVE
Type FORMAT FS=NTFS QUICK
Type ASSIGN
Type EXIT twice (one to get out of DiskPart, the other to exit the command line tool)
Press Shift + F10 to open a Command Prompt and reformat disk with Diskpart command while you see the first installer screen.
Reset the BIOS to defaults and make sure there is no setting in the BIOS that prevents writing to the boot sector. Also make sure the BIOS is actually detecting the hard drive correctly
hard drive manufacturer's diagnostic tool.
chkdsk /f /r
at time of windows installation media
Repair your computer
In command prompt type diskpart to enter the utility.
Type select disk # replacing "#" with the drive number of the one you wish to format. To see a list of disks, type list disk.
Type clean. This deletes all volumes from the drive.
Type convert mbr to convert the disk to mbr.
convert gpt to revert back to GPT. (Optional step)
Start DISKPART.
Type LIST DISK and identify your SSD disk number (from 0 to n disks).
Type SELECT DISK <n> where <n> is your SSD disk number.
Type CLEAN
Type CREATE PARTITION PRIMARY
Type ACTIVE
Type FORMAT FS=NTFS QUICK
Type ASSIGN
Type EXIT twice (one to get out of DiskPart, the other to exit the command line tool)
Subscribe to:
Posts (Atom)