Re: Workspace Manager Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 01 Aug 2006 11:24:09 -0700
Message-ID: <1154456650.38059@bubbleator.drizzle.com>

wrgptfan wrote:
> DA Morgan wrote:

>> wrgptfan wrote:
>>> I have a schema of about 200 version enabled tables.  What I would like
>>> to do is to modify one of the tables using DBMS_WM.BEGINDDL, however
>>> the table name is 25 characters in length.  Although the documentation
>>> states that a table name cannot exceed 25 characters (because of adding
>>> _HIST and the like) it seems as if the limit is fewer than 25 if you
>>> ever would like to alter the table.
>>>
>>> Am I doing something wrong or is there a workaround to my problem.
>>>
>>> TIA...Dave Kent
>>>
>>>
>>>
>>> permit_at_devdb> create table WCP_ISSUING_LOCATION_CODE (pk number(5));
>>>
>>> Table created.
>>>
>>> permit_at_devdb> alter table WCP_ISSUING_LOCATION_CODE add constraint
>>> WCP_ISSUING_LOCATION_CODE_pk
>>>   2  primary key(pk);
>>>
>>> Table altered.
>>>
>>> permit_at_devdb> exec
>>> dbms_wm.enableversioning('WCP_ISSUING_LOCATION_CODE');
>>>
>>> PL/SQL procedure successfully completed.
>>>
>>> permit_at_devdb> exec dbms_wm.beginddl('WCP_ISSUING_LOCATION_CODE')
>>> BEGIN dbms_wm.beginddl('WCP_ISSUING_LOCATION_CODE'); END;
>>>
>>> *
>>> ERROR at line 1:
>>> ORA-00972: identifier is too long
>>> ORA-06512: at "SYS.OWM_DDL_PKG", line 1878
>>> ORA-06512: at "SYS.LT", line 10257
>>> ORA-06512: at line 1
>>>
>>>
>>> permit_at_devdb> select * from v$version;
>>>
>>> BANNER
>>> ----------------------------------------------------------------
>>> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
>>> PL/SQL Release 9.2.0.1.0 - Production
>>> CORE    9.2.0.1.0       Production
>>> TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
>>> NLSRTL Version 9.2.0.1.0 - Production
>>>
>>> permit_at_devdb>
>> The first thing that jumps out to me is 9.2.0.1.0. Why?
>>
>> Oracle will say it to you so I'll save you the trip to metalink.
>> Apply patches.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

>
> Doh! I was doing the test on my Windows laptop version instead of the
> Unix version. Sorry about that. Here is the actual output:
>
> permit_at_devdb> @conn permit_at_devdb
> Enter password: ******
> Connected.
>
> Session altered.
>
> permit_at_devdb> exec dbms_wm.BeginDDL('WCP_ISSUING_LOCATION_CODE');
> BEGIN dbms_wm.BeginDDL('WCP_ISSUING_LOCATION_CODE'); END;
>
> *
> ERROR at line 1:
> ORA-00972: identifier is too long
> ORA-06512: at "SYS.OWM_DDL_PKG", line 1878
> ORA-06512: at "SYS.LT", line 10257
> ORA-06512: at line 1
>
>
> permit_at_devdb> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
> PL/SQL Release 9.2.0.5.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for HPUX: Version 9.2.0.5.0 - Production
> NLSRTL Version 9.2.0.5.0 - Production
>
> permit_at_devdb>

Look at the following and compare with what you posted.

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Aug 1 11:23:35 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create table WCP_ISSUING_LOCATION_CODE

2 (testcol VARCHAR2(5));

Table created.

SQL> alter table WCP_ISSUING_LOCATION_CODE

2 add constraint pk_test
3 primary key (testcol);

Table altered.

SQL> exec dbms_wm.enableversioning('WCP_ISSUING_LOCATION_CODE', 'VIEW_WO_OVERWRITE') PL/SQL procedure successfully completed.

SQL> exec dbms_wm.BeginDDL('WCP_ISSUING_LOCATION_CODE');

PL/SQL procedure successfully completed.

SQL> col object_name format a30
SQL> select object_name, object_type

2 from user_objects order by 1;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
WCP_ISSUING_LOCATION_CODE      VIEW
WCP_ISSUING_LOCATION_CODE_AP1$ INDEX
WCP_ISSUING_LOCATION_CODE_AP2$ INDEX
WCP_ISSUING_LOCATION_CODE_AUX  TABLE
WCP_ISSUING_LOCATION_CODE_BASE VIEW
WCP_ISSUING_LOCATION_CODE_BPKC VIEW
WCP_ISSUING_LOCATION_CODE_CONF VIEW
WCP_ISSUING_LOCATION_CODE_CONS VIEW
WCP_ISSUING_LOCATION_CODE_DIFF VIEW
WCP_ISSUING_LOCATION_CODE_HIST VIEW
WCP_ISSUING_LOCATION_CODE_LOCK VIEW
WCP_ISSUING_LOCATION_CODE_LT   TABLE
WCP_ISSUING_LOCATION_CODE_LTS  TABLE
WCP_ISSUING_LOCATION_CODE_MW   VIEW
WCP_ISSUING_LOCATION_CODE_PKC  VIEW
WCP_ISSUING_LOCATION_CODE_PKD  VIEW
WCP_ISSUING_LOCATION_CODE_PKDB VIEW

WCP_ISSUING_LOCATION_CODE_PKDC VIEW
WCP_ISSUING_LOCATION_CODE_PKI$ INDEX Try this in your version.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Aug 01 2006 - 13:24:09 CDT