Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01012: not logged on

user597936Oct 2 2007 — edited Mar 30 2012

[b]At times I am not able to execute any Select statements after logging in. Does this has to do with SGA size? How to troubleshoot this issue

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 1 10:30:32 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01012: not logged on

=======================================

This is the o/p taken from already connected session

Name Bytes % Used Var. Size
-------------------------- ------------ ------- ------------
free memory 25,165,824 40.00 62,914,560
free memory 8,189,984 13.02 62,914,560
library cache 5,911,848 9.40 62,914,560
miscellaneous 5,895,664 9.37 62,914,560
dictionary cache 3,221,760 5.12 62,914,560
event statistics per sess 2,287,800 3.64 62,914,560
kgl simulator 2,145,024 3.41 62,914,560
1M buffer 2,098,176 3.33 62,914,560
sql area 1,324,880 2.11 62,914,560
KSXR receive buffers 1,034,000 1.64 62,914,560
message pool freequeue 940,944 1.50 62,914,560
KSXR pending messages que 853,952 1.36 62,914,560
sessions 608,400 .97 62,914,560
transaction 399,696 .64 62,914,560
FileOpenBlock 393,232 .63 62,914,560
enqueue 378,016 .60 62,914,560
character set object 274,528 .44 62,914,560
processes 257,600 .41 62,914,560
krvxrr 253,056 .40 62,914,560
KQR L PO 250,240 .40 62,914,560
db_handles 232,000 .37 62,914,560
KGLS heap 231,680 .37 62,914,560
KSXR large reply queue 166,824 .27 62,914,560
KQR M PO 150,624 .24 62,914,560
PX subheap 20,872 .03 62,914,560
KQR S SO 6,672 .01 62,914,560
joxs heap init 4,240 .01 62,914,560
KGK heap 2,200 .00 62,914,560
PLS non-lib hp 2,088 .00 62,914,560
table definiti 992 .00 62,914,560
fixed allocation callback 544 .00 62,914,560

============================================================

Message was edited by:
user597936

Frank Kulash

Answer

Hi,

When you say "CONNECT BY LEVEL <= x" , make sure at least one of the following is true:
(1) the table in the query has only one row, or
(2) you know what you're doing.

In other situations, create a Counter Table that has all the numbers you'll ever need, and join to the Counter Table where you otherwise would do a CONNECT BY.
In the definition for the Counter Table, the CONNECT BY clause will resemble your original CONNECT BY clause, but instead of "CONNECT BY LEVEL <= x" it will be "CONNECT LEVEL <= *(SELECT MAX (x) ...)* ".
When you use the counter table, the join condition will be similar to your original CONNECT BY condition.

WITH	cntr	AS
(
	SELECT	LEVEL	AS n
	FROM	dual
	CONNECT BY	LEVEL <= ( SELECT MAX (LENGTH (a_id))
			      	   FROM	  a
				 )
)
,	split_a_id	AS
(
	SELECT	  a.a_text,
--		  a.a_id,	-- For debugging only
--		  c.n,	-- For debugging only
		  SUM (TO_NUMBER ( SUBSTR ( a.a_id || '0'
	    	       	 	 	  , -c.n
					  , 1
					  )
				  )
	    	      ) OVER ( PARTITION BY  a.a_text
		      	       ORDER BY      c.n
			     )	AS cut_length
	FROM      a     a
	JOIN	  cntr	c	ON	c.n	<= LENGTH (a.a_id)
)
SELECT    SUBSTR ( a_text
	         , 1
	         , LENGTH (a_text) - cut_length
	         ) 	AS cut_text
FROM      split_a_id
ORDER BY  a_text
,     	  cut_text	DESC
;

Now that you're dealing with multiple rows, you need a PARTITION BY clause in the analytic SUM function.

Thanks for including the CREATE TABLE and INSERT statements.
Please format your code, and post it between

 tags so this site won't remove any spaces.
The link was a good idea, but it seems to be a link to the wrong thread.

Edited by: Frank Kulash on Nov 17, 2009 2:46 PM
Commented out 2 line I forgot to delete after testing.

Marked as Answer by Ludy · Sep 27 2020

Ludy

Thanks Frank Kulash,
now got better understanding in using the LEVEL in CONNECT BY.
Now things are getting clearer to me.

sure will post code between

 tags, and sorry about the link.

Aketi Jyuuzou

I like recursive with clause B-)
I do Not have Oracle11gR2
Therefore I made below solution using PostgreSQL8.4 :8}

CREATE TABLE TTT(ID text,Val text);
INSERT INTO TTT(ID,Val) VALUES('13233424','ABCDEFGHIJKLMNOPQRSTUV');
INSERT INTO TTT(ID,Val) VALUES('13233424','WXYZABCDEFGHIJKLMNOPQR');

with recursive rec(ID,Val,LV,cuts) as(
select ID,Val,1   ,substr(ID,1,1)    from TTT
union all
select ID,Val,LV+1,substr(ID,LV+1,1) from rec
 where LV < length(ID))
select substr(Val,1,(sum(cuts::integer) over(partition by Val order by LV))::integer) as CUT_TEXT
  from rec
order by Val,LV desc;

       cut_text
-----------------------
ABCDEFGHIJKLMNOPQRSTUV
ABCDEFGHIJKLMNOPQR
ABCDEFGHIJKLMNOP
ABCDEFGHIJKL
ABCDEFGHI
ABCDEF
ABCD
A
WXYZABCDEFGHIJKLMNOPQR
WXYZABCDEFGHIJKLMN
WXYZABCDEFGHIJKL
WXYZABCDEFGH
WXYZABCDE
WXYZAB
WXYZ
W

I like model clause solution too :D

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 27 2012

Added on Oct 2 2007

#general-database-discussions

21 comments

224,233 views

Oracle Database Discussions

ORA-01012: not logged on

Comments

Post Details