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.

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

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

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

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)

ORA-14759 SET INTERVAL is not legal on this table

SQL> alter table ldbo.TBLSNPRISKDETAILS set INTERVAL (NUMTOYMINTERVAL(1,'MONTH')
);
alter table ldbo.TBLSNPRISKDETAILS set INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.


reason:
PARTITION SAUDA_MAX values LESS THAN (maxvalue)  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M )


 STORAGE(INITIAL 10000M NEXT 1000M)
  TABLESPACE "USR"
  PARTITION BY RANGE ("DSAUDADATE")
(
PARTITION SAUDA_APR values LESS THAN (TO_DATE('01-MAY-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAY values LESS THAN (TO_DATE('01-JUN-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JUN values LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JUL values LESS THAN (TO_DATE('01-AUG-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_AUG values LESS THAN (TO_DATE('01-SEP-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_SEP values LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_OCT values LESS THAN (TO_DATE('01-NOV-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_NOV values LESS THAN (TO_DATE('01-DEC-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_DEC values LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JAN values LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_FEB values LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAR values LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAX values LESS THAN (maxvalue)  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M )
);

ORA-14400 inserted partition key does not map to any partition

ORA-14400: inserted partition key does not map to any partition


insert into ldbo.TBLSNPRISKDETAILS (CFIRMNUMBER, CCLIENTCODE, DSAUDADATE, NPRODUCTCODE, NSEGMENTTYPE, NFINANCEAMOUNT, NMARGINAMOUNT, NBILLAMOUNT, NCOLLATERAL, NCOLLATERALBHCUT, NDEMATSTOCK, NDEMATSTOCKBHCUT, NOVERALLPOSITION, NOVERALLPOSITIONBHCUT, NNETMARGIN, NNETMARGINBHCUT, NFOEXPOSURE, NDERIVATIVESNOTIONAL, NOSSALES, NMARGINPERCENTAGE, NMARPERBHCUT, NPOASTOCK, NPOASTOCKBHCUT, NUNREALIZEDAMOUNT, NBHLEVEL, NBHLEVELBHCUT, NMFDEMATSTOCK, NMFDEMATSTOCKBHCUT, NMFPOASTOCK, NMFPOASTOCKBHCUT, NTILLDATEFINANCE, NTILLDATEMARGIN, CSCHEMETYPE, CBRANCHCODE, CCTCLBRANCHCODE, NMFNOTIONAL, CCOMPANYNAME, CCLIENTCATEGORY, NPENDINGACCEPTED, NADHOCCHARGE, NACCRUEDINTEREST, CNEXTRADEDATE, CBDRCODE, NPISAMOUNT, NGLOBALMARGIN, NTOTALSTOCK, NDPCHARGES, NTOTALILLBENSTOCK)
values ('FOR-000001', 'N124349   ', to_date('31-05-2017', 'dd-mm-yyyy'), 0, 0, 267.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 58630.70, 64229.30, 58363.70, 63962.30, 0.00, 0.00, 0.00, 99.54, 99.58, 58630.70, 64229.30, 0.00, 0, 0, 0.00, 0.00, 0.00, 0.00, 267.00, 0.00, 'NORMAL    ', '8002', 'HO    ', 0.00, 'RSL_SELF_RACE_ONLINE', ' ', 0.00, 0.00, 0.00, '02/04/2014', ' ', 0.00, 51034.05, 64229.30, 0.00, 0);

ORA-14400: inserted partition key does not map to any partition



 STORAGE(INITIAL 10000M NEXT 1000M)
  TABLESPACE "USR"
  PARTITION BY RANGE ("DSAUDADATE")
(
PARTITION SAUDA_APR values LESS THAN (TO_DATE('01-MAY-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAY values LESS THAN (TO_DATE('01-JUN-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JUN values LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JUL values LESS THAN (TO_DATE('01-AUG-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_AUG values LESS THAN (TO_DATE('01-SEP-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_SEP values LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_OCT values LESS THAN (TO_DATE('01-NOV-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_NOV values LESS THAN (TO_DATE('01-DEC-2015','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_DEC values LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_JAN values LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_FEB values LESS THAN (TO_DATE('01-MAR-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),
PARTITION SAUDA_MAR values LESS THAN (TO_DATE('01-APR-2016','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M ),



add following
PARTITION SAUDA_MAX values LESS THAN (maxvalue)  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M )

ora-12801 ora-12853 ora-04031

not need to set large_pool or other

only set sga pga or memory

tnsping no listener

tns-12541 no listener nslookup dns timeout

etc/hosts

network issue right


nslookup DB-serverIP

If timeout contact to IT Admin



ipconfig /registerdns




TNS-03505: Failed to resolve name
This shows that the configuration is incorrect and the SID/service name that you are attempting to connect to cannot be resolved. Check the TNSNames entry is there for your database you are attempting to connect to. If it is, check that the IP address/DNS entry are correct as previously mentioned above when regarding configuring the TNSNames.ora file. Then, check that you can ping the IP address from the server you are making the connection from. If you can’t, that is your problem. If you are using DNS you can try using NSLOOKUP to resolve the name. For example, NSLOOKUP TEST will attempt to look up and show you the IP address for the TEST name in your DNS. The problem will be in this area somewhere. - See more at: http://www.ora00600.com/wordpress/articles/listener-tnsnames-configuration/#sthash.lsuFuJ4S.dpuf

ora-31626 ora-31687 ora-31688

If your init parameter AQ_TM_PROCESSES parameter has a value of zero, remove it.


Ensure that your streams_pool_size is configured with a minimum value of 50MB and 128MB would be better then retry the job.

SQL> SHOW PARAMETER streams_pool_size

TO SET THE PARAMETER streams_pool_size

SQL> ALTER SYSTEM SET streams_pool_size=128M

SHUT DOWN & RESTART THE DATABASE

EXPDP ORA-31626 ORA-31633 ORA-06512 ORA-01950

ORA-31626 ORA-31633 ORA-06512 ORA-01950
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01950: no privileges on tablespace 'SYSTEM'



ALTER USER SYSTEM QUOTA unlimited ON SYSTEM;
ALTER USER SYSTEM QUOTA unlimited ON USR;
ALTER USER SYSTEM QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO SYSTEM;

ALTER USER LDBO QUOTA unlimited ON SYSTEM;
ALTER USER LDBO QUOTA unlimited ON USR;
ALTER USER LDBO QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO LDBO ;

impdp hang on grant role and sql SYS.kupc$que_int.receive

 increase stream_pool_size

Handler PageHandlerFactory-Integrated has a bad module ManagedPipelineHandler in its module list

HTTP Error 500.21 - Internal Server Error

Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list


%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i

ora-01536 space quota exceed

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;




select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile from dba_users where username='LDBO';

impdp hang statement suspended, wait error to be cleared

space issue or add more datafiles

select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB" from dba_data_files;

select file_name,tablespace_name,bytes/1024/1024 "Size MB",status,autoextensible,maxbytes/1024/1024 "MaxSize MB" from dba_temp_files

ORA-01652 unable to extend temp segment by 64 in tablespace TEMPORARY

ALTER TABLESPACE temporary ADD TEMPFILE 'E:\NBSD1314\TEMP02.ORA' SIZE 1000M AUTOEXTEND ON;
ALTER database TEMPFILE 'E:\NBSD1314\TEMP02.ORA' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;


change file location as per client DB folder

alter tablespace
   temporary
add tempfile
   'D:\APXD1516\TEMP02.ORA'
size
   1000m
autoextend on
next
   100m
maxsize
   unlimited;

ORA-00600 internal error code, arguments [FILEjsks.c LINE2388 IDOCIKCallPush] ORA-28031 maximum of 148 enabled roles exceeded

ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388 ID:OCIKCallPush] ORA-28031: maximum of 148 enabled roles exceeded

Error starting at line 1 in command:
begin
dbms_scheduler.run_job('JobImportClientMaster8');
end;
Error report:
ORA-00600: internal error code, arguments: [FILE:jsks.c LINE:2388 ID:OCIKCallPush], [], [], [], [], [], [], [], [], [], [], []
ORA-28031: maximum of 148 enabled roles exceeded




https://dbamohsin.wordpress.com/tag/ora-600/


SR 3-8664869401 : ORA 600-[FILE:jsks.c LINE:2388 FUNCTION:jsksStartOCICall() ID:OCIKCallPus]


Caused by Bug 8895202 : ITL HAS HIGHER COMMIT SCN THAN BLOCK SCN
The solution was to set the following dynamic parameter on both the primary and standby database:


ALTER SYSTEM SET "_ktb_debug_flags"=8 SCOPE=BOTH;


This parameter is designed to heal blocks having invalid dependent scn’s on switchover operations.
From the traces provided to oracle, I was told that the affected object for this issue was ID 12152331.
Running the following SQL determines the object:



select owner,object_name,object_type,subobject_name,object_id,data_object_id
  from   dba_objects
  where  object_id in (12152331)
  or     data_object_id in (12152331);


The affected blocks were on index SYS.I_SCHEDULER_JOB_RUN_DETAILS on table SCHEDULER$_JOB_RUN_DETAILS.
As well as the dynamic parameter, I also did the following:


ANALYZE TABLE SCHEDULER$_JOB_RUN_DETAILS VALIDATE STRUCTURE online;

ALTER INDEX SYS.I_SCHEDULER_JOB_RUN_DETAILS REBUILD online;

PLS-00565 STR_ARRAY must be completed as a potential REF target (object type)

create or replace TYPE "STR_ARRAY" is table of varchar2(3999);
/





ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 0, column 0:
PLS-00565: STR_ARRAY must be completed as a potential REF target (object type)


DROP TYPE "STR_ARRAY" ;

create or replace TYPE "STR_ARRAY" is table of varchar2(3999);
/

ORA-01658

select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024;


select file_name,tablespace_name,bytes/1024/1024/1024 "Size GB",status,autoextensible,maxbytes/1024/1024/1024 "MaxSize GB",increment_by from dba_data_files;

select tablespace_name,bigfile from dba_tablespaces;



alter database default tablespace SYSTEM;


ALTER DATABASE DATAFILE 'E:\MCS0809\USERs01.ORA' AUTOEXTEND ON MAXSIZE UNLIMITED;


Hard disk space is not equal to the database space
tablespace is full, and then hang it a few more data files
Reply:
Hard disk space is sufficient enough table space does not mean