Skip to Main Content
Forums

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.

Problems with generating replication support for tables

407048Jun 12 2006 — edited Jul 29 2006
(A pretty frustating day so far having spent about 2 hours so far troubleshooting this problem with no luck...hence posting it here for some help)
Oracle 9.2.0.6 running on sun solaris.

We have an advanced replication setup. I have about 200 tables in a schema called VISTA that I am replicating. The replication group name is VISTA.
Currently replication group VISTA is in a suspended(quiesced) status.
For example, I have a table called REPLICATION_VERIFY in the VISTA schemas of both primary and secondary databases that are being replicated.

Having logged into OEM, Advanced Replication->Multimaster Replication->Master Groups->VISTA, I can see all the objects listed. About 30 of the objects are having a status of ERROR(instead of VALID) and generation status column shows as 'Need Generation'.
Now when I select one of those tables that has ERROR, and click the button 'Generate Replication Support', it fails and gives me the following pop-up error in OEM :

Statement ---
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname=>"VISTA",
oname=>"REPLICATION_VERIFY",
type=>"TABLE",
min_communication=>TRUE,
generate_80_compatible=>FALSE);
END;

Stack Trace :-------------------
ORA-23308: object VISTA.REPLICATION_VERIFY does not exist or is invalid.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_MAS" , line 2906
ORA-06512: at "SYS.DBMS_REPCAT", line 766
ORA-06512: at line 2

Just doesn't let me get thru. REPLICATION_VERIFY in VISTA schema does exist. Also due to this error, it wouldn't let me resume the replication. Attempts to do so will give me an error asking me to fix the generation of replication support and the group remains in QUIESCED status. Please help....

Comments

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.

Post Details

Locked on Aug 26 2006
Added on Jun 12 2006
#general-database-discussions
3 comments
1,367 views