SQL Tuning Task Fails With ORA-01727 - Numeric Precision Specifier Is Out
Jan 26, 2013
Database was recently upgraded from 10.2.0.4 to 11.2.0.3 and the EM dbcontol repository was recreated.If I schedule a sql tuning advisor task for any sql query, i get this error. I have also tried to drop sysman user and recreate it, but no luck.
Type Findings Recommendations Rationale Benefit (%) Other Statistics New Explain Plan Compare Explain Plans
Error ORA-01727: numeric precision specifier is out of range (1 to 38)
I am trying to run copy command to copy data from one database to other.I 'm getting this error while running copy command. "ORA-01727: numeric precision specifier is out of range (1 to 38)"
I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data APPEND INTO TABLE cdr.day_tables FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( RecordCode ,CdrStart DATE 'YYYY DDD SSSSS' [code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS BEFORE INSERT ON DAY_TABLES FOR EACH ROW DECLARE [code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY ( BNUMBER IN VARCHAR2 , CALLDATE IN DATE , CALLDURATION IN NUMBER ) AS record_found NUMBER; BEGIN [code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND. ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6 ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
i have problems to display icons in a task bar in an application that is being migrated from oracle 4.5 to 10g in windows xp.The task bar shows all the icons in the original aplication but when the application runs in 10g, just some of the icons are shown, not all of them.All the icons are in the same file direction "C:fileiconos" and they're all gif files.
I have added the file locatoin to the configuration of the registry.dat,forms, in the registry of the computer, in the orion and orion-web files and in the default file following advice given to other threads here but the problem stays in the application.I have added also the variables to the registry of the coputer for UI_ICON and UI_ICON_EXTENSION with their values.
we have a database application which is done frequently.in these we load data throught Sql loader, we create an DB instances, we do several DML operation on the database.
now for such task in an application we need to keep an logging track of each task performed in PL/SQl procedure packages.
I have written a below procedure to dump the table data to .csv file.But the problem is i have 20 tables which is holding 75 studies data. Means every table will have 75 studies related data.what i supposed to do is i need to export the data from 20 tables for each study. but this procedures requires me to run the procedure 75(studies)*20(tables) times. is there any technique instead of i manually giving the table name and study name , will it take from any text file where we defined 75 studies in that. or easy there any better way .
create or replace procedure dump_table_to_csv1(p_tname in varchar2, p_dir in varchar2, p_filename in varchar2) is l_output utl_file.file_type; l_theCursor integer default dbms_sql.open_cursor; [code]........
I had a task to reschedule database jobs to a different date as they were Running Statistics job running during business hours..
I used dbms_job.change procedure for the same On Thursday i.e. 17th June i changed a job as follows exec dbms_job.change(11, NULL, TRUNC(SYSDATE+2), 'TRUNC(SYSDATE+7)');
When i checked the status today querying user_jobsi found that the job was started today at 00:57 hrs.
I checked the alert logs as well but couldn't find any errors. How to proceed with the troubleshooting for this issue.
When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value
SQL> drop table precision_test; Table dropped SQL> create table precision_test(name number(2,5)); Table created SQL> insert into precision_test values (1); insert into precision_test values (1) [code]....
2114 rows I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn). I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).
I have a table T1.In that table i have a column id, i gave a number datatype for id as number(2,2). when i try to insert the value im getting an error.
SQL> desc t1; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(2,2) NAME VARCHAR2(10) NAME1 NUMBER
SQL> insert into t1(id) values(2); insert into t1(id) values(2) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
SQL> insert into t1(id) values(2.5); insert into t1(id) values(2.5) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
SQL> insert into t1(id) values(10.15); insert into t1(id) values(10.15) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
SQL> insert into t1(id) values(10.5); insert into t1(id) values(10.5) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
Procedure countyname(i_lat IN Number, i_lon IN NUMBER, o_countyname OUT VARCHAR2);
The procedure works fine when the input parameter values are small precision like 30.653, -618.765 etc.it fails if the Input parameter values have more precise like 35.694872140886...I think the IN Number can only take upto certain precission.
Is there any way I can specify the precision for a NUMBER input parameter in a procedure?
ex: Procedure countyname(i_lat IN Number(30,10), i_lon IN NUMBER(30,10), o_countyname OUT VARCHAR2);
when I tried the above statement it doesn't compile it gives PLS-00103 error.
We have truncated number based on the decimal value. i tried to truncate number based on the precision using cast function. i got an error "value larger than specified precision allowed for this colum".
create table TEST_NUMBER (id number(4,1)); insert into TEST_NUMBER values(1234.789888888888);
[code]...
ORA-01438: value larger than specified precision allowed for this column 01438. 00000 - "value larger than specified precision allowed for this column" *Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column. *Action: Enter a value that complies with the numeric column's precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.
POWER(47.3616, 27.1261) returns: 2.80403309600359E45 which causes "number precision too large" error in my variable which is defined as NUMBER(20,20).
I've tried ROUNDing and TRUNCing the product, but still get the same long output. The only thing that seems to work is SUBSTR.
Is that the ONLY way to deal with this? It seems like there would be a better way or is there another type I should use these super long decimal results?
SQL> SELECT POWER(47.3616, 27.1261) AS Exp_Val 2 FROM dual; EXP_VAL ---------- 2.8040E+45 SQL>
I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.
When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).
When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:
select to_number(field_name, '99999999.999') field_name from view
SQL> insert into t51 values (100000000000000000.00000); insert into t51 values (100000000000000000.00000) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
I have the task to migrate the total databases(Exact copy to be moved to another server).The current server is going for format.After I did the following steps I am getting the tablespaces(databases)-4 sizes same ,but I am facing issue like some default tablespaces i.e temp,system are not matching.
temp tablespace *************** current server - 4.0(approximately) Migrating server - 160 MB
System tablespace ***************** current server - 580 MB Migrating server - 220 MB
Also I checked the tables are also matching for the 4 databases.Also Provide the solution or method which is correct.
steps done for migrating(By me) ******************************** EXPORTING DATA USING DATAPUMP *********************************
1 From command prompt MKDIR 'c:oraclexeapp mp';
2 From SQL prompt conn system/kotak;
3 create or replace directory dmpdir as 'c:oraclexeapp mp';
4 grant read,write on directory dmpdir to kotak;
5 From command prompt
expdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=expdpxe.log; IMPORTING DATA USING DATAPUMP ***************************** in another server machine
1 From SQL prompt conn system/kotak;
2 create or replace directory dmpdir as 'c:oraclexeapp mp';
I want to revoke some privileges (given from user, say A, on table TABLE to user B) with SYS, and i've got an error : It says that I can't revoke a privilege that I didn't give.
In DBA_TAB_PRIVS, there's no information about that : I can't know who gives the privilege (when I try to perform the REVOKE statment with OWNER, GRANTOR, I've got the same error).
I'm trying to configure and run replication. I have two PC's running as Oracle servers, having both of them the same configuration:
Windows XP Pro Oracle 11g Enterprise Edition
First server is called Oracle1, and database name is orcl1. Second server name is Oracle2 and database name is orcl2..I'm running the following
SYSTEM/oracle1 alter system set global_names=true; alter system set job_queue_processes=100; ALTER DATABASE RENAME global_name TO orcl1.world;
SYSTEM/oracle2 alter system set global_names=true; alter system set job_queue_processes=100; ALTER DATABASE RENAME global_name TO orcl2.world;
SYSTEM/oracle1
CREATE PUBLIC DATABASE LINK orcl2.world USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle2)(Port=1521)))(CONNECT_DATA=(SID=orcl2.world)))';
CREATE USER repadmin IDENTIFIED BY clave DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; [code]...
I am not able to register the database with 12cR1 CRS . Gettting the error as follows,
bash-3.2$ /opt/oracle/product/11.2.0/db3/bin/srvctl add database -d 11gr2tstdb -o /opt/oracle/product/11.2.0/db3PRCR-1006 : Failed to add resource ora.nishtest.db for nishtestPRCD-1184 : Failed to upgrade configuration of database type to version 11.2.0.3.0PRCR-1071 : Failed to register or update resource type ora.database.typeCRS-0245: User doesn't have enough privilege to perform the operation
Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
We are into pre-production stage for Oracle RAC implementation. I have setup Oracle 10g 2 node RAC, currently i have been experiencing TAF failures for TOAD client. TOAD client session simply disconnects rather than failing over to the next node, It is noticed that TAF succesfully works for SQL client from the same client machine. Attached herewith are following from both nodes :
1) Listener file node1, node2 2) TNS file node1, node2 3) TNS file client 4) Output of show parameter listener from node1, node2 5) Output of lsnrctl services node1, node2
Summary of environment :
OS - Windows 2003 entp edition sp2, 32 bit Database server - 10.2.0.5.0
I have a large 10G database, with records being inserted almost constantly. In order to "UPDATE" records, I need to be able to DELETE the old record and INSERT the new record (even while records are being inserted into the table). It appears that DELETES won't work while records are being inserted (1.5 million records per day). Is there a way around this (which I'm assuming at this point is a contention issue)? The INSERT uses the NOPARALLEL hint; no hints on the DELETE.
