Home » RDBMS Server » Server Administration » csscan report advice (9.0.1,RH 7.3)
csscan report advice [message #312145] Tue, 08 April 2008 01:52 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Current charctaer set: US7ASCII
NEW charcater set: AL32UTF8

I ran cscan utility and found 90 exceptional user tables's varchar2 type columns.

[Distribution of Convertible Data per Column]
USER.TABLE|COLUMN                 Convertible    Exceptional
-------------------------------- ---------------- ----------------
SYS.METASTYLESHEET|STYLESHEET        25               0
SYS.EXTERNAL_TAB$|PARAM_CLOB         1                0
OE.CUSTOMERS|CUST_FIRST_NAME         0                1
OE.CUSTOMERS|CUST_EMAIL              0                1
TRET.OPER|OP_PASSWD                  0               34
TRET.OP_MAST|OP_PASSWD               0               52
BATB.SC_CODE|GRANT_NO                0                1
BATB.SC_CODE|H_CODE                  0                1


As all these columns are of varchar2 type,How could i remove them from exceptional list.
As in AL32UTF8,a 1 character is of 3 bytes,do ineed to increase the values of the columns and then rerun sscan?
Re: csscan report advice [message #312152 is a reply to message #312145] Tue, 08 April 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See detailed report to get what can of exception it is.
But in your case, as you start from US7ASCII there should not be no exceptional values, this means you record in your tables data that are out of the original character set.

Yes, you may have to enlarge some columns but it is better to change the length semantics from BYTE to CHAR.
By the way, a character set may have up to 4 bytes (or may be 8 in very very exceptional cases) in AL32UTF8.

Regards
Michel
Re: csscan report advice [message #312160 is a reply to message #312145] Tue, 08 April 2008 02:19 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
There is one more column shown in the docs
"Application Data Individual Exceptions"

This could tell whats the reason behind these exceptions.

How to activate it?
I don't see any option in csscan to use to apply it.
Re: csscan report advice [message #312223 is a reply to message #312145] Tue, 08 April 2008 04:25 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Ok, all the 90 exceptions are lossy conversions.

How to overcome these exceptions?

The columns op_passwd contains encoded passwords in the database which might not be in the range of US7ASCII and hence the exception.

[Updated on: Tue, 08 April 2008 04:35]

Report message to a moderator

Re: csscan report advice [message #312238 is a reply to message #312223] Tue, 08 April 2008 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to overcome these exceptions?

Change the characters to ones that are supported in the current character or backup the data, truncate them and reinsert them afterward. These are the 2 ways. In your case only the second one can be applied.

Regards
Michel
Re: csscan report advice [message #312271 is a reply to message #312145] Tue, 08 April 2008 06:06 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So These should be the final steps:
1)truncate table stmt
2)alter database character set AL32UTF8
3)imp back the truncate records.
Re: csscan report advice [message #312282 is a reply to message #312271] Tue, 08 April 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but... if you password field is binary one I recommend you to change the type to RAW or to VARCHAR2 but converting the binary to string as Oracle does it for user passords.

Regards
Michel
Re: csscan report advice [message #312290 is a reply to message #312145] Tue, 08 April 2008 07:50 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
The fields are in varchar2 type.
Whne i issued alter database character set command,
It failed
reason CLOB datatype exists.
There are many $ tables that have clob datatype.

What should i do to them?
Truncate them also.
CSSCAN did not report any such thing.
Re: csscan report advice [message #312295 is a reply to message #312290] Tue, 08 April 2008 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What should i do to them?
Truncate them also.

This is what I do (export/truncate/import, not necessarily with Oracle exp/imp tools) maybe not the smartest thing.
Other times I full export, recreate the database and the full import.
It depends on the size of data, exceptional and overall.

Have a look at CSALTER script, I never used it but it may worth to see what it does.

Regards
Michel
Re: csscan report advice [message #312348 is a reply to message #312145] Tue, 08 April 2008 11:09 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
CSALTER is i guess new in 10g.
I did not find CSALTER.plb in rdbms/admin directory of 9.0.1.

Quote:
This is what I do (export/truncate/import, not necessarily with Oracle exp/imp tools) maybe not the smartest thing


But what would truncate do since clob is a datatype and the table structure remains the same after truncation.

It seems the only option left is to recreate the database and import all the data.
Re: csscan report advice [message #312357 is a reply to message #312348] Tue, 08 April 2008 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"truncate" was a way of speaking. You can drop the column (does this exists in 9.0? I don't remember). If it does not exist use CTAS to recreate the table without the column...
As I said it depends on how much data you have.

Regards
Michel
Re: csscan report advice [message #312481 is a reply to message #312145] Wed, 09 April 2008 00:43 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
ALL_REPCAT_TEMPLATE_OBJECTS
ALL_REPCAT_TEMPLATE_PARMS
ALL_REPCAT_USER_PARM_VALUES
ALL_REPCAT_USER_PARM_VALUES
ATTRIBUTE_TRANSFORMATIONS$
DBA_IAS_GEN_STMTS
DBA_IAS_GEN_STMTS_EXP
DBA_IAS_POSTGEN_STMTS
DBA_IAS_PREGEN_STMTS
DBA_REPCAT_EXCEPTIONS
DBA_REPCAT_TEMPLATE_OBJECTS
DBA_REPCAT_TEMPLATE_PARMS
DBA_REPCAT_USER_PARM_VALUES
DBA_REPCAT_USER_PARM_VALUES
DEFLOB
EXTERNAL_TAB$
EXU9XTB
JIREFRESHSQL$
METASTYLESHEET
SNAP$
USER_REPCAT_TEMPLATE_OBJECTS
USER_REPCAT_TEMPLATE_PARMS
USER_REPCAT_USER_PARM_VALUES
USER_REPCAT_USER_PARM_VALUES
VIEWCON$
_ALL_INSTANTIATION_DDL
DEF$_LOB
DEF$_TEMP$LOB
REPCAT$_EXCEPTIONS
REPCAT$_INSTANTIATION_DDL
REPCAT$_RUNTIME_PARMS
REPCAT$_TEMPLATE_OBJECTS
REPCAT$_TEMPLATE_PARMS
REPCAT$_USER_PARM_VALUES
WM$LOCKROWS_INFO
ONLINE_MEDIA
ONLINE_MEDIA
ONLINE_MEDIA
ONLINE_MEDIA
PRINT_MEDIA
PRINT_MEDIA
WAREHOUSES

These are the tables that have clob datatype.
I don't know which schema they belong to.
DO they hold any significance in normal database operation?
Can i drop them?
Re: csscan report advice [message #312489 is a reply to message #312145] Wed, 09 April 2008 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Can i drop them?
I give up. Can you?
Re: csscan report advice [message #312566 is a reply to message #312145] Wed, 09 April 2008 05:16 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
These are system tables.
Is it safe to modify them?
Re: csscan report advice [message #312571 is a reply to message #312566] Wed, 09 April 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, of course not.
See what is inside if you can get rid of the data or workaround this.
Otherwise, full export/import.

Regards
Michel
Re: csscan report advice [message #312606 is a reply to message #312145] Wed, 09 April 2008 07:28 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
All these tables are empty.
SHAll idrop them?

I don't know for waht purpose they are used
Re: csscan report advice [message #312615 is a reply to message #312606] Wed, 09 April 2008 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are part of the catalog and more dictionary.
If you can delete catalog views (you can always recreate them), dropping dictionary tables will lead to a corrupted database.
So the answer is: no.

Regards
Michel
Re: csscan report advice [message #312616 is a reply to message #312145] Wed, 09 April 2008 07:47 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So should i CTAS excluding CLOB and rename the tables,dropping old one?
Re: csscan report advice [message #312620 is a reply to message #312616] Wed, 09 April 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never ever touch dictionary tables.

Regards
Michel
Re: csscan report advice [message #312625 is a reply to message #312145] Wed, 09 April 2008 08:32 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So before CSALTER,the only option to migrate database character set was to recreate the database and exp/imp even if the new character set is a super set of old one.
Since Dictionary tables are maintained by oracle,so they ought to contain tables with CLOB datatype.
Re: csscan report advice [message #312627 is a reply to message #312625] Wed, 09 April 2008 08:36 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is UTF8 specific, I never succeeded to upgrade the character set to UTF8 without using exp/imp (but using internal and not supported stuff).

Regards
Michel
Previous Topic: Drop Master Detail Table
Next Topic: Insert duration
Goto Forum:
  


Current Time: Mon Dec 05 08:41:45 CST 2016

Total time taken to generate the page: 0.18513 seconds