OraFAQ Forum: Server Administration » csscan report advice

Home » RDBMS Server » Server Administration » csscan report advice (9.0.1,RH 7.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

csscan report advice [message #312145]

Tue, 08 April 2008 01:52

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?

Report message to a moderator

Re: csscan report advice [message #312152 is a reply to message #312145]

Tue, 08 April 2008 02:05

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312160 is a reply to message #312145]

Tue, 08 April 2008 02:19

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.

Report message to a moderator

Re: csscan report advice [message #312223 is a reply to message #312145]

Tue, 08 April 2008 04:25

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

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312271 is a reply to message #312145]

Tue, 08 April 2008 06:06

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.

Report message to a moderator

Re: csscan report advice [message #312282 is a reply to message #312271]

Tue, 08 April 2008 07:22

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312290 is a reply to message #312145]

Tue, 08 April 2008 07:50

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.

Report message to a moderator

Re: csscan report advice [message #312295 is a reply to message #312290]

Tue, 08 April 2008 08:04

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312348 is a reply to message #312145]

Tue, 08 April 2008 11:09

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.

Report message to a moderator

Re: csscan report advice [message #312357 is a reply to message #312348]

Tue, 08 April 2008 11:48

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312481 is a reply to message #312145]

Wed, 09 April 2008 00:43

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?

Report message to a moderator

Re: csscan report advice [message #312489 is a reply to message #312145]

Wed, 09 April 2008 00:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can i drop them?
I give up. Can you?

Report message to a moderator

Re: csscan report advice [message #312566 is a reply to message #312145]

Wed, 09 April 2008 05:16

varu123
Messages: 754
Registered: October 2007

Senior Member

These are system tables.
Is it safe to modify them?

Report message to a moderator

Re: csscan report advice [message #312571 is a reply to message #312566]

Wed, 09 April 2008 05:35

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312606 is a reply to message #312145]

Wed, 09 April 2008 07:28

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

Report message to a moderator

Re: csscan report advice [message #312615 is a reply to message #312606]

Wed, 09 April 2008 07:41

Michel Cadot
Messages: 68675
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

Report message to a moderator

Re: csscan report advice [message #312616 is a reply to message #312145]

Wed, 09 April 2008 07:47

varu123
Messages: 754
Registered: October 2007

Senior Member

So should i CTAS excluding CLOB and rename the tables,dropping old one?

Report message to a moderator

Re: csscan report advice [message #312620 is a reply to message #312616]

Wed, 09 April 2008 08:07

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Never ever touch dictionary tables.

Regards
Michel

Report message to a moderator

Re: csscan report advice [message #312625 is a reply to message #312145]

Wed, 09 April 2008 08:32

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.

Report message to a moderator

Re: csscan report advice [message #312627 is a reply to message #312625]

Wed, 09 April 2008 08:36

Michel Cadot
Messages: 68675
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

Report message to a moderator

Previous Topic:	Drop Master Detail Table
Next Topic:	Insert duration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 06 16:54:31 CDT 2024