How can we dynamically get maximum value permitted in a number column? Is there any in-built function in oracle for this?
e.g. Accrued_Interest NUMBER(10,4)
In this case maximum value that can be inserted in this column is "999999.9999".
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?
View 1 Replies View Relatedhow do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies View RelatedHow many maximum number of bind variables,can we use in Execute Immediate.
View 2 Replies View RelatedWhat is the maximum number of failures a job can allow,when we are scheduling jobs using DBMS_JOB.
View 1 Replies View Relatedselect * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - ProductionI got below error on the datases.
ORA-00020: maximum number of processesBased on the suggestion got from previous posts, I did kill some (LOCAL=NO) processes to get myself in. When I looked at the number of processes the number is 150, I changed it to 1000. After a day I got the same error.
ORA-00020: maximum number of processes (1000) exceededI killed processes again to let my self in, bases on the post below, I queried database
ORA-00020: maximum number of processes (%s) exceededHere is the query to find out if there are any processes with out session attached to it.
select spid from v$process where addr not in (select paddr from v$session);Result
SPID
null
35259840
36897866
13369762
[Code]...
To determine the from where greatest number of processes originated
SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;Machine Count(*)
2
CAPITAL-D3F0092 1
DQAKDCDKLCSW01 14
desmond 20
DQAKDCDKLCSA01 52
My question is why I am keep getting this? Why do increasing process from 150 to 1000 did not resolve the issue?
What can I do to make sure that I don't get this error again?
I want to add number of zeros after a number dynamically. eg: If number is 2 and number of zeroes to be added is 5 then final output should be 200000;
View 12 Replies View Relatedi got this error..
ORA-00020: maximum number of processes (1100) exceeded
my oracle version is 10.2.0.2.0
when i was trying to login to oracle i got :ERROR at line 1: ORA-01012: not logged on
lucky i was having another sqlplus login ..
wht i should do to bring the number of process down without increasing the process value and shuting/restaring down db.
"Usually, when this happens, I want to get connected to the instance, so that I can try to figure out which user is causing the problem. One way to do that is to use the 'ps' command to identify some of the oldest server processes, and kill the 3-5 oldest processes, and then quickly try connecting to the database. Once you're in, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there."
this is what i got from [URL]....
1.if i am giving ps -ef in OS it will give lot of detail ...
2. in v$session wht i should look for ....
I need to get the maximum value of the VALUE field of each record with different POINTNUMBER, then do an update on the 2nd table.
table 1
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ
table 2 (idem structure)
UTCTime TIMESTAMP (6)
INTEGER POINTNUMBER
FLOAT VALUE (126)
INTEGER TLQ
Where POINTNUMBER is six thousand different values My query only returns me the maximum of a single record:
SELECT * FROM table1 WHERE value = (SELECT MAX (value) FROM table2);
And the update:
UPDATE table2 SET to a.value = (SELECT MAX (b.value) FROM table2 b);
so does on a single record.That needed to maximize each pointnumber different? I can use a cursor to do this easier?
Insertion should first make a one-time, and then updates every 1 hour
I don't know if there is a beginner forum out there for PL SQL, but I cannot find one.I just started taking an Intermediate Oracle course to learn PL SQL. Today is my first day and I just installed Oracle 11g. I was in SQL Plus, tried the edit command. I got the message "wrote file Afiedt.buf" and then it was just a flashing cursor in the line below. I could not type any commands. I just hit the X to close the program.
What could I have done to exit the program with that flashing cursor since I was unable to type anything?
I am using Oracle 11g R2. (11.2.0.3)
I know that in the profiles of the user I can set limit for the number of sessions per database user, using the below command:
alter profile <profile_name> limit sessions_per_user=<required_number>;
I need to set maximum number of processes for the application user so in case they opened too many number of processes on the database they do not block the automated jobs to be run.My questions:
1. set a limit on sessions_per_user is it the same as a limit on processes? 1 proccess= 1 session ? Is there any way to limit the number of processes (not sessions) for a database user?
2. I am using RAC so the number of sessions is per the whole database, while the max number of processes is by instance (not database), so even I set a limit on the number of sessions (above the limit of processes for one of the instances) one instance may face ORA-20 errors, while the max number of sessions did not hit the limit. is there any workaround for this case?
I read the error description. In my case there is only one full backup at a time running, hence it does not make any sense.
RMAN>
connected to target database: OTATEST (DBID=3130218754)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
SQL> select count(*) from V$RMAN_STATUS;
COUNT(*)
----------
560
SQL> select count(*) from V$RMAN_OUTPUT;
COUNT(*)
----------
32834
how many maximum connection, a particular user can have in oracle. and how many maximum connection if user has 'resource profile' as default.and i am using oracle 11.2.0.1.0
View 1 Replies View RelatedIve just tried running a simple
update BASIC set 'column name' = NULL
Which works fine if i specify a where clause that returns a low amount of values, but im trying to run this update for the whole column (1000's or records).
Ive had no experience of this error before and am unsure of where to start, ive had a quick read around but see something of removing triggers?
The full error is :
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "new.su_Table", line 61
if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
I have getting error will insert into table i.e
i have a trigger as below
create or replace trigger INS_ERRORS
before insert on MIG_STG_ERRORS
for each ROW
declare
V VARCHAR2(22);
[code]........
when i insert into MIG_STG_ERRORS getting error message like 00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded"
*Cause: An attempt was made to go more than the specified number of recursive SQL levels.
*Action: Remove the recursive SQL, possibly a recursive trigger.
I want to pass Number of columns dynamically to a query. I got success in SQL.
SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.
To investigate further on this error ,i want to find how many user connections got established during the error time and their details like machine,username,program,status etc in database ?
View 6 Replies View RelatedIn Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.Is it possible to use a number that is selected from another query? When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number
View 10 Replies View Relatedi am getting ORA-00059:maximum number of DB_FILES exceeded, so i used following on one node
SQL> alter system set db_files=700 scope=spfile; system altered.
now do i need to re-bounce(restart) the db.
we are using database 11g.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
i am getting one error while selecting the data from table.
SELECT interco_type, entity, chapter_entity, tran_currency, source_id_entity,
tran_id_entity, mirror_id, gaap_type, counterpart
FROM t_gri_reconid_dtl_agg_gcr;
or
select * from t_gri_reconid_dtl_agg_gcr where rownum = 1 ;
Error:- ORA-01795: maximum number of expression in a list is 1000 ( error is same in both the case )
From front end the user will enter VACANCY_ID in the label box and once he saves that transaction, in background VACANCY_CD filed(sequence) willautomatically generated
.below is my code:{code}create table tmp1(vac_id number,vac_cd number);create sequence tmp1_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;select tmp1_seq.nextval from dual; ---initiating seqselect tmp1_seq.currval from dual; --checking the current val--trigger creationCREATE OR REPLACE TRIGGER TRIGGER1 AFTER INSERT ON TMP1 FOR EACH ROW DECLARE seqval
[code]...
Error: ORA-00036: maximum number of recursive SQL levels (50) exceededORA-00036: maximum number of recursive SQL levels (50) exceeded
I am currently on a project which has a requirement of changing the number of records dynamically. Oracle Forms version 10g.
View 2 Replies View RelatedI have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.
e.g Start Number : 1 and End Number : 1001.
Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.
Means
Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001
Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.
SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(
[Code].....
I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.
In our development database, we have created 5 dimensions and a cube with 2 measures in OLAP. All these are mapped to relational tables. When we try to load this cube (MAINTAIN CUBE option from AWM) we are getting this below error:
An error has occurred on the server
Error class: Express Failure
Server error descriptions:
INI: error creating a definition manager, Generic at TxsOqConnection::generic<BuildProcess>
INI: XOQ-01600: OLAP DML error "ORA-35571: The maximum number of load errors has occured." while executing DML
[code].....
What are the database parameter i should look for as a DBA ?
Oracle 11g R2 on WIndows 20o8 R2 Standard edition
I have a test server that keeps running into ORA-00020: maximum number of processes error each night. Our current setting is '600' processes to be allowed. There are no users to this database except myself. No front end app connects to it either.
I found that ORACLE.EXE (SHAD) process coming from SYSMAN user is the one eating up all the processes and eventually causing that error.
From v$session, the program is OMS.
From v$process, the hostname is the server itself.
I narrowed it down to restarting the ConsoleService, if i do that, then the number of processes drops down. So now im not sure why something within the ConsoleService is logging in, taking a process and not releasing it...What i can check?
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
View 4 Replies View RelatedWhat is the Maximum Charter length can be given as a column name in a table?
View 3 Replies View RelatedI'm trying to update a number field on an apex page by summing up the values of multiple page items. I've tried to follow this tutorial
[URL]........
especially the "Create a Set Value Dynamic Action Using PL/SQL" part.
In the tutorial example they return an page item value P3_SAL multiplied by a multiplier they've determined by another page item's case. What I want to do is much simpler in that I just want to add up multiple page item values and then display them in a number field at the bottom of the page.
so the code I tried to use based on the tutorial is in the tab: Home >Application Builder>Application 103>Page 3>Edit Dynamic Action>Create / Edit Action
Set Type: PL/SQL Function Body
PL/SQL Function Body:
BEGIN
return :P3_ITEM1+P3_ITEM2+P3_ITEM3;
END;
Page Items to Submit: P3_ITEM1,P3_ITEM2,P3_ITEM3
But it gives me an error that I need to declare the identifiers, yet it works if I only use one item and I can perform any arithmetic.
i.e.
BEGIN
return :P3_ITEM1+1000;
END;
