Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: crystal reports and 10gR2
cptkirkh wrote:
> I recently upgraded to 10.2.0.3 and I have an applicatuion that runs
> crystal reports. it seems now one of the reports as it starts up the
> report which logs on the report to each table starts running sql code
> i have never seen before and causes a lot of CPU usage. Does anyone
> know what this could be? This wasn't happening in 10.1.0.5.
>
> /* Formatted on 2007/10/22 21:44 (Formatter Plus v4.8.8) */
> SELECT COUNT (*)
> FROM xdb.xdb$schema s
> WHERE BITAND (TO_NUMBER (s.xmldata.flags, 'xxxxxxxx'), 16) = 16
> AND xdb.xdb$extname2intname (s.xmldata.schema_url,
> s.xmldata.schema_owner) =
> :
> 1
> AND s.xmldata.schema_url NOT IN (
> SELECT s2.xmldata.schema_url
> FROM xdb.xdb$schema s2, user$ u2
> WHERE u2.user# = :2
> AND u2.NAME = s.xmldata.schema_owner)
>
> /* Formatted on 2007/10/22 21:44 (Formatter Plus v4.8.8) */
> SELECT *
> FROM (SELECT NULL table_catalog,
> DECODE (owner, 'PUBLIC', NULL, owner) table_schema,
> table_name table_name, column_name column_name,
> NULL column_guid, NULL column_propid,
> column_id ordinal_position, 0 column_hasdefault,
> NULL column_default,
> DECODE (nullable,
> 'N', DECODE (data_type,
> 'BFILE', 128,
> 'BLOB', 136,
> 'CHAR', 24,
> 'CLOB', 136,
> 'DATE', 24,
> 'FLOAT', 24,
> 'LONG', 136,
> 'LONG RAW', 136,
> 'NCHAR', 24,
> 'NCLOB', 136,
> 'NUMBER', 24,
> 'NVARCHAR2', 8,
> 'RAW', 8,
> 'ROWID', 272,
> 'VARCHAR', 8,
> 'VARCHAR2', 8,
> 0
> ),
> DECODE (data_type,
> 'BFILE', 224,
> 'BLOB', 232,
> 'CHAR', 120,
> 'CLOB', 232,
> 'DATE', 120,
> 'FLOAT', 120,
> 'LONG', 232,
> 'LONG RAW', 232,
> 'NCHAR', 120,
> 'NCLOB', 232,
> 'NUMBER', 120,
> 'NVARCHAR2', 104,
> 'RAW', 104,
> 'ROWID', 272,
> 'VARCHAR', 104,
> 'VARCHAR2', 104,
> 0
> )
> ) column_flags,
> DECODE (nullable, 'N', 0, -1) is_nullable,
> DECODE (data_type,
> 'BFILE', 128,
> 'BLOB', 128,
> 'CHAR', 129,
> 'CLOB', 129,
> 'DATE', 135,
> 'FLOAT', 5,
> 'LONG', 129,
> 'LONG RAW', 128,
> 'NCHAR', 130,
> 'NCLOB', 130,
> 'NUMBER', DECODE (data_precision,
> NULL, (DECODE (data_scale,
> NULL, 139,
> 0, 131,
> 139
> )
> ),
> 126, 5,
> 131
> ),
> 'NVARCHAR2', 130,
> 'RAW', 128,
> 'ROWID', 128,
> 'VARCHAR', 129,
> 'VARCHAR2', 129,
> 13
> ) data_type,
> NULL type_guid,
> DECODE (data_type,
> 'BFILE', 2147483647,
> 'BLOB', 2147483647,
> 'CHAR', char_length,
> 'CLOB', 2147483647,
> 'LONG', 2147483647,
> 'LONG RAW', 2147483647,
> 'NCHAR', char_length,
> 'NCLOB', 2147483647,
> 'NVARCHAR2', char_length,
> 'RAW', data_length,
> 'ROWID', data_length,
> 'VARCHAR', char_length,
> 'VARCHAR2', char_length,
> NULL
> ) character_maximum_length,
> DECODE (data_type,
> 'BFILE', 2147483647,
> 'BLOB', 2147483647,
> 'CHAR', data_length,
> 'CLOB', 2147483647,
> 'LONG', 2147483647,
> 'LONG RAW', 2147483647,
> 'NCHAR', data_length,
> 'NCLOB', 2147483647,
> 'NVARCHAR2', data_length,
> 'RAW', data_length,
> 'ROWID', data_length,
> 'VARCHAR', data_length,
> 'VARCHAR2', data_length,
> NULL
> ) character_octet_length,
> DECODE (data_type,
> 'FLOAT', 15,
> 'NUMBER', DECODE (data_precision,
> NULL, DECODE (data_scale,
> NULL, 38,
> 0, 38,
> 38
> ),
> 126, 15,
> data_precision
> ),
> NULL
> ) numeric_precision,
> DECODE (data_type,
> 'NUMBER', data_scale,
> NULL
> ) numeric_scale,
> DECODE (data_type, 'DATE', 0, NULL)
> datetime_precision,
> NULL character_set_catalog, NULL
> character_set_schema,
> NULL character_set_name, NULL collation_catalog,
> NULL collation_schema, NULL collation_name,
> NULL domain_catalog, NULL domain_schema, NULL
> domain_name,
> NULL description
> FROM all_tab_columns) dbschema_columns
> WHERE table_schema = 'test' AND table_name = C_and_f
> ORDER BY 2, 3, 7
Possibly you have run into a bug that I found during the 11g Beta test
related to XML schemas ... they may not have existed in 10.1.0.5. If
so there is nothing you can do about it until 11gR1 plus one patch.
The bug isn't serious but it does cause a few queries to take a long
time to complete.
I would suggest you open an SR at metalink and see if they will back port the solution to 10.1 while it is still supported.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Tue Oct 23 2007 - 10:13:47 CDT