May 10, 2012

Workspace Error : ORA-20229

EXEC DBMS_WM.ENABLEVERSIONING('FACEBOOKBOUNDARY');

ORA-20229: statement 'delete from mdsys.sdo_geom_metadata_table' failed during EnableVersioning. Error:
ORA-20229: statement 'select count(*)
       from mdsys.sdo_geom_metadata_table
       where upper(
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1



when this error occurs


delete the FACEBOOKBOUNDARY_lt  entry from user_sdo_geom_metadata table
and then execute

EXEC DBMS_WM.ENABLEVERSIONING('FACEBOOKBOUNDARY');

it will works

:)
---------

Oracle Discoverer - I

     If you want to create new worksheets and analyze data from both relational and multi-dimensional data sources across the Web, you will use Discoverer Plus  if you want to create new worksheets and analyze data from relational data sources using a Windows application on a PC, you will use Discoverer Desktop
 if you want to analyze data in existing worksheets, you will use Discoverer Viewer or Discoverer portlets (that have been added to an Oracle Portal page using Discoverer Portlet Provider)

: If you want to display Discoverer worksheets as gauges in dashboard-style portals, you will use Discoverer Portlet Provider and Discoverer portlets  if you want to manage the end user view of relational data, you will use Discoverer Administrator


 Oracle BI Discoverer’s powerful and intuitive user interface enables you to:  find data that you know is in the database  access data quickly without waiting for the computer to search through the entire database
 view data in a familiar spreadsheet-style format that is easy to read and understand  analyze data using a variety of powerful techniques including:  drilling up and down through data  finding data that meets certain conditions or that falls within ranges that you specify  sorting data  prepare reports showing the results of your analysis  share data with other people, and in other applications (e.g. Microsoft Excel)

Your ultimate goal in using OracleBI Discoverer is to analyze data to arrive at a profitable business decision

Which telephone lines need greater bandwidth?
pivoting data to create comparisons  drilling up and down in data to see consolidated or more detailed information  drilling out to analyze data in other applications  creating totals, calculating percentages, and creating custom calculations  displaying data visually in graphs and charts

A business area is a collection of related information in the database. The Discoverer manager works with the different departments in your organization to identify the information that each department requires from the database. A folder is similar to a database table or view. Indeed, a folder can be based directly on a database table or view an item is similar to a column in a database table.

In a relational data source, data is organized in tables. A table is a data structure with columns and rows. Multidimensional data is data that is organized by one or more dimensions. These multidimensional data structures are often referred to as cubes.

A cube in a multidimensional data source has the following components:

A measure, which is the name given to the data itself; that is, to the data that you track such as sales figures or cost numbers.

 One or more dimensions. Dimension is the name given to the parts of the cube that categorize the data, such as Product, Geography, and Time. Dimensions have dimension members, dimension hierarchies, and attributes.

In OLAP metadata, measures represent data that can be examined and analyzed in crosstabs and graphs.

The Discoverer Catalog is a repository for storing and retrieving definitions of objects for Discoverer Plus OLAP and can be used by applications that are built with Oracle Business Intelligence Beans (BI Beans). With Discoverer Plus OLAP, you use the Catalog to store objects such as workbooks, calculations, and saved selections and to share objects with others who have access to the Catalog. For example, you can use a BI Beans application to create a graph and store it in the Catalog. If another user has appropriate access, that user can retrieve the graph that you stored in the Catalog and insert the graph into a new worksheet in Discoverer Plus OLAP.

You can maximize Discoverer performance.

 use tabular reports rather than cross-tabular reports
 minimize the number of page items in reports
 avoid wide cross tabular report
 avoid creating reports that return tens of thousands of rows
 provide parameters to reduce the amount of data produced
 minimize the number of worksheets in workbooks
 remove extraneous worksheets from workbooks

Dec 21, 2011

NOT IN / NOT EXISTS



 It is recommend to use NOT EXIST instead of NOT IN ; do you know/thought  why ?  ! .




You dont see any differences here because the tables are small with less data and also there will not be any difference in the explain plan too.For very large tables performance improvement are seen by using NOT EXISTS instead  of  NOT IN ; so test it and see whats the difference .


  EXAMPLE

   create table oracle4u ( orclid number(10), constraint pk primary key(orclid) ,s_name varchar2(100) );


   create table "oracle4u.com" ( orcl4id number(10), constraint opp primary key(orcl4id) ,s_name varchar2(100) );


   insert into oracle4u select rownum,OBJECT_NAME  from SYS.ALL_OBJECTS;


   insert into oracle4ucom select rownum,TABLE_NAME  from SYS.ALL_tables union select null,'TEST'  from dual

commit;
-------------------


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from oracle4ucom fg)


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from   oracle4ucom fg where fg.orcl4id is not null)


select * from oracle4u pp where  not exists  (select fg.orcl4id  from oracle4ucom fg where pp.orclid=fg.orcl4id )


select *  from oracle4u pp  Left Outer JOIN oracle4ucom fg ON pp.orclid=fg.orcl4id  WHERE fg.orcl4id IS NULL


Dec 3, 2011

ORA-20229: Workspace Manager Error

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

ORA-20229: statement 'delete from mdsys.sdo_geom_metadata_table' failed during EnableVersioning. Error:
ORA-20229: statement 'select count(*)
       from mdsys.sdo_geom_metadata_table
       where upper(
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1



when this error occurs


delete the GOLF_YARD_lt  entry from user_sdo_geom_metadata table
and then execute

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

Even if  its not working !

Then check whether a trigger exist on GOLF_YARD table or GOLF_YARD view ie : ( when
disabling GOLF_YARD that trigger may not be dropped) .That trigger is now restricting you to version enable your table GOLF_YARD drop the trigger/triggers from GOLF_YARD

and then execute


EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

it will works sure

---------