Feed aggregator

SQLPLUS multi-line quoted text

Mike Moore - Fri, 2012-04-13 14:49
This document demonstrates some of the problems I've encountered when using SQLPlus to insert text that has multiple lines. You can run the INSERT statements below, to demonstrate the specific problem which I state in the actual text being inserted.

--Note that these problems occur only with SQLPlus, Using TOAD, (and I assume SQL Developer), there are no errors with any of the inserts.
I used SQLPLus 11g client on Windows 7. Different configurations may produce different results.

I'd be interested in hearing about any other anomalies you find with multi-line text.


CREATE TABLE TXT (str varchar2(500));


set echo on
set sqlblanklines off
set define off
spool multiline_text.log

-------- In these examples I am using Oracle 'Q' strings: aka Quoted Strings
-------- For example, instead of quoting a string like this:
--------   'Joe''s Garage is where the ''action'' is'
-------- I do it like this:
--------   q'~Joe's Garage is where the 'action' is~'
-------- In a quoted string, the character after the initial q'
-------- can be any character that is not in the actual string content.
-------- I've chosen to use the tilde character.

---------- THE BLANK LINE --------
INSERT INTO txt ( str) 
VALUES (q'~This shows it is NOT okay to have a 

blank line unless you 'set sqlblanklines on'.~');

set sqlblanklines on

INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 

blank line after you 'set sqlblanklines on'.~');


---------- THE SLASH CHARACTER --------

INSERT INTO txt ( str) 
VALUES (q'~This shows it is NOT okay to have a 
/
slash by itself on a line~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is [NOT] okay to have a 
slash followed by a space character. You can't see it, but it's there.~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 
/slash as a starting character provided that the
slash is [not the only] character~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 
/ slash as a starting character
even if it is followed by a blank.~');



-----------THE DOT CHARACTER----------

INSERT INTO txt ( str) 
VALUES (q'~This shows it is [not] okay to have a 
.
dot on a blank line by itself.~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is [not] okay to have a 
.  
dot plus some trailing blanks on a line by itself.~');


INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a 
. dot plus more text
even when dot is the first character~');


---------THE SEMI-COLON CHARACTER ------

INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a; 

blank line provided you set sqlblanklines on~');

INSERT INTO txt ( str) 
VALUES (q'~This shows it is okay to have a; plus more

blank line provided you set sqlblanklines on~');


---------THE POUND SIGN CHARACTER ------


INSERT INTO txt ( str) 
VALUES (q'~This shows it [IS] okay to have a 
#
pound-sign on a line by itself~');

INSERT INTO txt ( str) 
VALUES (q'~This shows it is [not] okay to have a 
# plus more
text when the pound sign is the first character~');

INSERT INTO txt ( str) 
VALUES (q'~This shows it is  okay to have a 
a pound-sign # so long
as the #is not the first character~');

-- ;

rollback;
spool off
--------------------------------RESULTS----------------------

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\mmoore>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 16 11:16:11 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: / @mydb

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> set sqlblanklines off
SQL> set define off
SQL> spool multiline_text.log
SQL>
SQL> -------- In these examples I am using Oracle 'Q' strings: aka Quoted Strings
SQL> -------- For example, instead of quoting a string like this:
SQL> --------   'Joe''s Garage is where the ''action'' is'
SQL> -------- I do it like this:
SQL> --------   q'~Joe's Garage is where the 'action' is~'
SQL> -------- In a quoted string, the character after the initial q'
SQL> -------- can be any character that is not in the actual string content.
SQL> -------- I've chosen to use the tilde character.
SQL>
SQL> ---------- THE BLANK LINE --------
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is NOT okay to have a
  3
SQL> blank line unless you 'set sqlblanklines on'.~');
SP2-0734: unknown command beginning "blank line..." - rest of line ignored.
SQL>
SQL> set sqlblanklines on
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3
  4  blank line after you 'set sqlblanklines on'.~');

1 row created.

SQL>
SQL>
SQL> ---------- THE SLASH CHARACTER --------
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is NOT okay to have a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> slash by itself on a line~');
SP2-0734: unknown command beginning "slash by i..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [NOT] okay to have a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> slash followed by a space character. You can't see it, but it's there.~');
SP2-0734: unknown command beginning "slash foll..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3  /slash as a starting character provided that the
  4  slash is [not the only] character~');

1 row created.

SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3  / slash as a starting character
  4  even if it is followed by a blank.~');

1 row created.

SQL>
SQL>
SQL>
SQL> -----------THE DOT CHARACTER----------
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [not] okay to have a
  3  .
SQL> dot on a blank line by itself.~');
SP2-0734: unknown command beginning "dot on a b..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [not] okay to have a
  3  .
SQL> dot plus some trailing blanks on a line by itself.~');
SP2-0734: unknown command beginning "dot plus s..." - rest of line ignored.
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a
  3  . dot plus more text
  4  even when dot is the first character~');

1 row created.

SQL>
SQL>
SQL> ---------THE SEMI-COLON CHARACTER ------
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a;
ERROR:
ORA-01756: quoted string not properly terminated


SQL>
SQL> blank line provided you set sqlblanklines on~');
SP2-0734: unknown command beginning "blank line..." - rest of line ignored.
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is okay to have a; plus more
  3
  4  blank line provided you set sqlblanklines on~');

1 row created.

SQL>
SQL>
SQL> ---------THE POUND SIGN CHARACTER ------
SQL>
SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it [IS] okay to have a
  3  #
  3  pound-sign on a line by itself~');

1 row created.

SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is [not] okay to have a
  3  # plus more
SP2-0042: unknown command "plus more" - rest of line ignored.
  3  text when the pound sign is the first character~');

1 row created.

SQL>
SQL> INSERT INTO txt ( str)
  2  VALUES (q'~This shows it is  okay to have a
  3  a pound-sign # so long
  4  as the #is not the first character~');

1 row created.

SQL>
SQL> -- ;
SQL>
SQL> rollback;

Rollback complete.

SQL> spool off

Fine Tuning Incremental Updates using LOAD PRUNE

Keith Laker - Fri, 2012-04-13 10:57
If you are like most people, you probably use the LOAD_AND_AGGREGATE cube script that is automatically created by Analytic Workspace Manager. Fine tuning the update process simply involves filtering the fact table for new or changed rows.

With a little bit of effort, you can improve update times by writing your own cube processing script. You can also use MV log tables to automatically captured changes made to the fact table and use them as the data sources to cube updates.

AWM defines and makes the LOAD_AND_AGGREGATE script the default script of the cube. If you don’t specify a different script, LOAD_AND_AGGREGATE is automatically used as shown in the following example (note that the script references the OLAPTRAIN.SALES_CUBE but does not including the USING clause).

BEGIN
DBMS_CUBE.BUILD('OLAPTRAIN.SALES_CUBE','C',false,4,true,true,false);
END;
/

This script will run the LOAD PARALLEL and SOLVE PARALLEL commands. What this means is that for each partition, the database will LOAD data from the fact table/view and then SOLVE (aggregate) data. If you have specified a value for parallel that is greater than 1, partitions will be processed in parallel (in the example above, 4 processes). AWM also provides the ability to set the refresh method (C, or complete, in the above example).

LOAD_AND_AGGREGATE is a good choice for a full build, but it might not be the best choice for an incremental update. If you are simply updating the cube with changes within a few recent partitions (e.g., yesterday or this month), the LOAD PRUNE command is probably better than LOAD PARALLEL.

LOAD PRUNE will first query the fact table or view to first determine which partition will have new data using a SELECT DISTINCT. It will then only generate LOAD commands for those partitions that will have records loaded into them.
Let’s run through an update scenario. Make the following assumptions:

* The time dimension has months for 2008 through 2012 and the cube is partitioned by month. The cube will have 60 partitions.

* You have loaded data into the cube for January 2008 through March 2012.

* It’s now time to load data for April 2012. This data has been inserted into the fact table.

* You have mapped the cube to a view. For the April 2012 update, you have added a filter to the view so that it returns data only for April.

If you use the LOAD_AND_AGGREGATE script and choose the FAST SOLVE refresh method, the database will really to the following:

BEGIN
DBMS_CUBE.BUILD('OLAPTRAIN.SALES_CUBE USING (LOAD PARALLEL, SOLVE PARALLEL)','S',false,4,true,true,false);
END;
/

With LOAD PARALLEL, the database will process the LOAD command for each partition (all 60). Since it’s selecting from a view that’s filtered out all but April 2012, 59 partitions will have no new or changed data. Although it doesn’t take a long time to load 0 rows and figure out that a SOLVE is not required, it still adds up if there are a lot of partitions.

With LOAD PRUNE, the database will determine that a LOAD is only required for April 2012. The LOAD step is skipped for all other partitions. While you will still see the SOLVE for all partitions, it doesn’t really do any work because no rows were loaded into the partition. An example using LOAD PRUNE follows.

BEGIN
DBMS_CUBE.BUILD('OLAPTRAIN.SALES_CUBE USING (LOAD PRUNE, SOLVE PARALLEL)','S',false,2,true,true,false);
END;
/

If you would like a script that walked through a complete example using the OLAPTRAIN schema, including the use of an MV log table to automatically capture changes to the fact table, send me an email william.endress@oracle.com with a link to this posting.
Categories: BI & Warehousing

Do You Need to Learn to Write SQL Queries?

Sue Harper - Fri, 2012-04-13 05:00
I'm an old timer... when I started in the industry years ago, I learned how to create Data Models and write SQL. Yes, yes, I admit, it wasn't the first thing, and it was after I dropped the punch cards on the floor at university...! Didn't we all? In fact I taught SQL for quite a few years before I moved on to teaching application development using the various tools offered by Oracle. By the time I left Oracle, nearly 20 years on, the developers were all learning Java, but we all still need SQL.

Generally, if we're putting data into a relational, SQL-based, database and then querying the results, we probably need to know some SQL.  I know that's a pretty broad statement, but if you're an application developer and you need to get at data by writing ad hoc queries, it's key to be able to write good SQL.

Most of the people I know in the tech industry are old timers too, by that I mean they've been writing SQL for so long they can't remember when they started and it's easy for them, but what about those new to the technology?  How do you get up to speed?  How do you learn SQL? Do you know the data model of the tables you are querying?  Do have access to tools that help you look at the data model? What about tools that help you learn to write SQL?

Yup, you know what's coming - if you're starting out, we have a tool that can help you learn SQL.  The graphical interface in DBClarity Developer allows you to create, what we call SQL Rules to interact with the database, by building a visual structure of the query.  You don't need to know SQL.  The great news is, that you can see the SQL created and reuse the SQL once its created and the feedback we've had is that new users are learning SQL.

Why not download the product today and see what you can do? There lots of shorts videos on the website to help yo get started and on Tuesday the 17th April, we're running a webinar showing you how to create  SQL Rules.  Register and learn!

I'd love to hear your thoughts and feedback - I'm on twitter @MCGN_DBClarity or @SueHarps

Whitepaper : Enterprise Manager 12c Cloud Control Metering and Chargeback

Pankaj Chandiramani - Thu, 2012-04-12 22:52

Here is an interesting white paper on Metering and Charge-back using Enterprise Manager 12c . This covers Cloud, Database and Middleware.



http://www.oracle.com/technetwork/oem/cloud-mgmt/wp-em12c-chargeback-final-1585483.pdf

Categories: DBA Blogs

Looking to apply Bundle Patch 1 on Enterprise Manager 12c ? Here is a workbook to help you ....

Pankaj Chandiramani - Thu, 2012-04-12 22:45

Are you planning to apply Bundle patch 1 for EM 12c ? 


If yes , check this workbook which describes the complete flow .


Enterprise Manager Cloud Control Workbook for Applying Bundle Patch 1 (February 2012) and 12.1.0.2 Plugins [ID 1393173.1]


Applies to:
Enterprise Manager Base Platform - Version: 12.1.0.1.0 to 12.1.0.1.0 - Release: 12.1 to 12.1


Purpose
This document provides an overview of the installation steps needed to apply Bundle Patch 1 on the EM Cloud Control 12c Oracle Management Service OMS) and Management Agent.

Categories: DBA Blogs

Hello to the world of EM

Pankaj Chandiramani - Thu, 2012-04-12 22:25

Its been an year since i moved to my new role as Product Manager for Enterprise Manager & time flew like anything specially with activities like Product Beta's , Pre-launch Activity , Oracle Open World , Product Launch , Collateral creation (white-papers , video , demos etc)  & 100's of others things . Now finally i have decided to revive this blog & start sharing my experience on Em12 .

Categories: DBA Blogs

Remote Debugging with SQL Developer revisited.

Barry McGillin - Thu, 2012-04-12 16:41
As part of the development process, we all have to work out the bugs in our code.  For all of us who use SQLDeveloper , we know how to debug with SQL Developer. Compile for Debug, breakpoint and go.  However, People still get confused by what remote debugging is and how it works.  At its most basic, it allows us to run a procedure in a session and debug if from another.

So, Lets say we have a simple procedure on employees table like this.

create or replace
FUNCTION GET_EMP_NAME
(
ID IN NUMBER
) RETURN VARCHAR2 AS
name varchar2(100);
BEGIN
select first_name||' '||last_name into name from employees
where employee_id = ID;
RETURN name;
END GET_EMP_NAME;


We can compile this for debug in SQLDeveloper as normal.  Now, for remote debugging, we want to go to another session and run this function there.  For clarity, we can do it in SQL*Plus.  Before that however, we need to switch on the remote debugger listener so we can attach to a session.  So, firstly, right click on your connection and choose remote debug, which will pop up a little window

For our purposes, on localhost, we dont need to add any other information, but if you are connecting to another database on another machine, add the host name to the local address field and choose an appropriate port.  When you click ok on this, the Run manager is shown with the listener details on there as shown above.

Now, here we are with SQL*Plus, fire it up with our demo user and make sure to execute the command

 execute DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',4000);

and then we can run our function as described above.

[oracle@localhost ~]$ sqlplus hrdemo/hrdemo

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 12 19:16:37 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HRDEMO@ORCL> set serveroutput on
HRDEMO@ORCL> execute DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',4000);

PL/SQL procedure successfully completed.

HRDEMO@ORCL> begin
2 dbms_output.put_line(get_emp_name(100));
3 end;
4 /


Once we run the anonymous bock, the remote debugger kicks in and we stop at the appropriate breakpoint in the code.

On a last note, this works well in Application Express too so when you make a call to a function which you have remote debug switched on for, the debugger will break on the line as long as you have debug switched on in the developer toolbar.

Convert sqlplus spool output to CSV

Tyler Muth - Wed, 2012-04-11 15:47
I’ve been doing some work lately with sizing / capacity planning where the source are a few of Karl Arao’s excellent AWR Mining scripts. Had I been reading his blog more I would have known he was going down a parallel path using Tableau as I was moving in the direction of R.  Anyway, I […]
Categories: DBA Blogs, Development

Extract from SalesForce, Load to Oracle

Mike Moore - Wed, 2012-04-11 15:28

I did it this way for 2 reasons.
1)These are the tools I know how to use.
2)There are things I am not allowed to do at work due to access rights. 


This is not intended to be a perfect document, it's just intended to be helpful.
No doubt, you will find problems which I did not encounter. If you do, please leave notes in the comments to help the next guy out.


---------------------OVERVIEW--------------------------------------------------------
NOTE: When I mean something literally I use the left-bracket and right-bracket
       character like this:
      [that's literally what "I" mean]


This is the results of dozens of failed attempts due to SQLPLUS idiosyncrasies of dealing with 
multi-line text. If you don't have multi-line text, many of the following edits won't be applicable. 


The process I use is basically
  1) extract data from Salesforce into a file
  2) Use Excel to format salesforce data into Oracle INSERT statements
  3) edit all the things out of the file which SQLPLUS will find objectionable
   and put in a few additional script commands like COMMIT
  4) run SQLPLUS 


This document assumes that the columns you extract from SalesForce will match exactly with the Oracle
DB table you want to load. 


Software you will need.
  1) The Salesforce Data Loader program
  2) TextPad (get it off the web. It's the only text editor that won't screw up your files.)
  3) Excel 2007 or later (maybe earlier versions would work too)
  4) An Oracle database.


-----------------------INSTRUCTIONS---------------------------------------------------------------      
1) Use Salesforce Data Loader to create the extract .csv file (hence forth called 'the extract file').
   If you don't know how  to do this, Google it. There is much better documentation on this than
   I could ever provide. 


2) At this point, I copy all of the column headers in the .cvs file and use them to create an Oracle table. 
   I define all the columns as VARCHAR2(4000) just for ease but you can use whatever matches the data 
   if you want.


3) Use Excel to open the extract file, from step 1, and do SAVE AS sf_extract_edited.xlxs. In other words
   immediately save it back under a new name so that you don't overwrite the extract file in step 1.
   It does not really matter what you name it but it is a good idea to include the word "edited" so that
   you know that this is an edited version.


   3.1) Rename the first worksheet tab (at the bottom) to [raw_data]. 
   3.2) Do a FIND on [#NAME] using the following options ... 
        3.2.1) ctrl-f
        3.2.2) click on [Options] button on the FIND-dialog pop-up window.
        3.2.3) for [Look in:] dropdown, choose [Values]  <--IMPORTANT !
        3.2.4) make sure the entire sheet will be searched and click on Find Next
        3.2.5) if the find gets a hit, note that in for formula field you will
               probably see a value that starts with [=]
        3.2.6) on the formula field, insert a ['] before the [=] (put a single quote before the equal sign)
               this will prevent excel from interpreting the equal sign as a formula indicator. 
        3.2.7) repeat this process until no more hits on the FIND.
        
   3.3) Insert an empty column to the left of column A (this empty column will become the new column A)
        I'm doing this so that the cells line up with the cells of the next sheet which we are going to create


   3.4) create a new "Sheet", name the new sheet 'inserts'
   3.5) Create an insert statement that matches the table you will be loading then 
        position cursor at cell A2 and paste in the first part of your INSERT statement, for example:


INSERT INTO
 ZD_OPPORTUNITY (ID, ISDELETED, ACCOUNTID, RECORDTYPEID, NAME,
 DESCRIPTION, STAGENAME, AMOUNT, PROBABILITY, EXPECTEDREVENUE,
 CLOSEDATE, TYPE, NEXTSTEP, LEADSOURCE, ISCLOSED, ISWON, FORECASTCATEGORY,
 FORECASTCATEGORYNAME, HASOPPORTUNITYLINEITEM,
 PRICEBOOK2ID, OWNERID, CREATEDDATE, CREATEDBYID, LASTMODIFIEDDATE,
 LASTMODIFIEDBYID, SYSTEMMODSTAMP, LASTACTIVITYDATE,
 FISCALQUARTER, FISCALYEAR, FISCAL, SPLIT_TOTAL__C) VALUES (


 3.6) select cell B2 and enter the formula  [=CONCATENATE("q'~",raw_data!B2,"~',")] without
      the outer brackets, on the formula line
    3.6.1) If any text has more than 4000 characters, modify the above formula for that column to be ..
           [=CONCATENATE("q'~",  MID( raw_data!N11409,1,3200),"~',")]  
            excel and sqlplus count chars differently hence 3200 for a safe margin.


 3.7) drag the cell out to the right to cover all of the columns that have data in the raw_data sheet
 3.8) edit the right-most cell's formula so that your INSERT statement will end with [);] and not [,]
      3.8.1) i.e. change this: [=CONCATENATE("q'~",raw_data!CQ2,"~',")]
                      to this: [=CONCATENATE("q'~",raw_data!CQ2,"~');")]
 3.8) select all cells of column 2 and drag down to cover all rows in the raw_data sheet
 3.9) save your work
 4.0) open a new (empty) file using TextPad. You can get TextPad for free trial off the internet.
 4.1) copy-paste the entire excel content of the "inserts" tab to the empty TextPad window.
 4.2) insert the following at the top of the textpad window:
       set sqlblanklines on
       set define off
       spool opp.log
 4.3) insert SPOOL OFF at the bottom
 4.4) save and quit out of EXCEL, we won't need it any more after this.
 4.5) Save the TextPad file now, just for safety. Several edits will now need to be made to the TextPad file. 
   4.5.1) n/a
   4.5.2) Replace all ["INSERT] with [INSERT]  i.e. remove the quote mark
   4.5.3) Replace all [VALUES ("] with [VALUES (] i.e. remove the quote mark
   4.5.4) This one is a little tricky and requires using a regular expression for the Replace.
          The intent is that no text line should start with a pound-sign so we are inserting a blank.
          On the Replace dialog box, put a check mark in the Regular expression option, then do
          Replace all [^#] with [ #]
        Now un-check Regular express option.
   4.5.6) Replace all ["q'~] with [q'~]
   4.5.7) Replace all [~',"] with [~',] 
   4.5.8) Replace all [);"]  with [);]


   4.5.9) Next we will change all lines that end with [;], except for ends with [');],  to end with [:]
          This is to pick up cases such as the following multi-line text example:


                 q'~A Multi-line text;
                    where a text line ends with semi-colon
                    will cause SQLPLUS to think the line has ended and you will get
                    ERROR:
                    ORA-01756: quoted string not properly terminated~'


          1) first replace all [');] with [````]. So they won't be picked up by our next replace.


          2) then replace all [; *$] with [:] using regular expression
                  This says, If the line ends with semi-colon, or semi-colon and a bunch of spaces,
                  replace it with a colon. If it is not acceptable for you to make slight changes
                  like this, then you will have to figure something else out.
                  WARNING: If you already coded COMMIT; it is now COMMIT:, go fix it. 


          3) change all  [````] back to [');]


          4) Replace all [^/$] with [ ] using regular expression
                 SQLPLUS does not like a text line that only contains a slash.


          5) Replace all [^\.$] with [ ] using regular expression
                 SQLPLUS does not like a text line that only contains a dot.
     
   4.5.9) optional - remove all tab characters 
          1) check the Hex box and replace [09] with []
   4.5.10) Save the file and close TextPad
 5.0) Get into SQLPLUS and run the INSERTs you just created.


One last thing, after loading the table,  carefully check any columns that are supposed to have SalesForce dates. Excel sometimes turns these into Julian dates. If you end up with excel julian dates in your oracle tables, you can translate by doing:



SELECT   to_date(to_char(2415019+closedate),'J') as closedate


where closeddate is the Excel julian date.










      



OEM12c Patchset install: Updated cleanup script

Marc Kelderman - Tue, 2012-04-10 08:51
I have updated my script to force a clean-up of de repository of OEM12c with the latest patch set. There were some new roles and synonyms introduced. The old script of OEM12c is here, new one is here, logon as sys user with sysdba priveliges:

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Metadata Services';
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Authorization Policy Manager';
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Oracle Platform Security Services';
commit;

drop user SYSMAN cascade;
drop user SYSMAN_MDS cascade;
drop user MGMT_VIEW cascade;
drop user SYSMAN_RO cascade;
drop user SYSMAN_OPSS cascade;
drop user BIP cascade;
drop user SYSMAN_APM cascade;
drop role MGMT_USER;
drop role MGMT_ECM_VIEW;

drop public synonym SMP_EMD_TARGET_OBJ_ARRAY;
drop public synonym SMP_EMD_TARGET_OBJ;
drop public synonym SMP_EMD_STRING_ARRAY_ARRAY;
drop public synonym SMP_EMD_STRING_ARRAY;
drop public synonym SMP_EMD_NVPAIR_ARRAY;
drop public synonym SMP_EMD_NVPAIR;
drop public synonym SMP_EMD_INTEGER_ARRAY_ARRAY;
drop public synonym SMP_EMD_INTEGER_ARRAY;
drop public synonym SMP_EMD_DELETE_REC_ARRAY;
drop public synonym SMP_EMD_AVAIL_OBJ;
drop public synonym SETEMVIEWUSERCONTEXT;
drop public synonym MGMT_VIEW_UTIL;
drop public synonym MGMT_USER;
drop public synonym MGMT_TYPE_PROPERTIES;
drop public synonym MGMT_TARGET_PROPERTIES;
drop public synonym MGMT_TARGET_MEMBERSHIPS;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym MGMT_TARGETS;
drop public synonym MGMT_TARGET;
drop public synonym MGMT_STRING_METRIC_HISTORY;
drop public synonym MGMT_SEVERITY_OBJ;
drop public synonym MGMT_SEVERITY_ARRAY;
drop public synonym MGMT_SEVERITY;
drop public synonym MGMT_PREFERENCES;
drop public synonym MGMT_PAF_PROCS_LATEST;
drop public synonym MGMT_PAF_JOBS;
drop public synonym MGMT_PAF$STATES;
drop public synonym MGMT_PAF$PROCEDURES;
drop public synonym MGMT_PAF$INSTANCES;
drop public synonym MGMT_PAF$APPLICATIONS;
drop public synonym MGMT_NAME_VALUES;
drop public synonym MGMT_NAME_VALUE;
drop public synonym MGMT_METRIC_THRESHOLDS;
drop public synonym MGMT_METRIC_ERRORS;
drop public synonym MGMT_METRIC_COLLECTIONS;
drop public synonym MGMT_METRICS_RAW;
drop public synonym MGMT_METRICS_COMPOSITE_KEYS;
drop public synonym MGMT_METRICS_1HOUR;
drop public synonym MGMT_METRICS_1DAY;
drop public synonym MGMT_METRICS;
drop public synonym MGMT_MESSAGES;
drop public synonym MGMT_LONG_TEXT;
drop public synonym MGMT_LOG;
drop public synonym MGMT_JOB_TARGET;
drop public synonym MGMT_JOB_SCHEDULE;
drop public synonym MGMT_JOB_PARAMETER;
drop public synonym MGMT_JOB_OUTPUT;
drop public synonym MGMT_JOB_EXEC_SUMMARY;
drop public synonym MGMT_JOB_EXECUTION;
drop public synonym MGMT_JOB_EXECPLAN;
drop public synonym MGMT_JOBS;
drop public synonym MGMT_JOB;
drop public synonym MGMT_IP_TGT_GUID_ARRAY;
drop public synonym MGMT_GUID_OBJ;
drop public synonym MGMT_GUID_ARRAY;
drop public synonym MGMT_GLOBAL;
drop public synonym MGMT_DELTA_VALUES;
drop public synonym MGMT_DELTA_VALUE;
drop public synonym MGMT_DELTA_ID_VALUES;
drop public synonym MGMT_DELTA_IDS;
drop public synonym MGMT_DELTA_ENTRY_VALUES;
drop public synonym MGMT_DELTA_ENTRY;
drop public synonym MGMT_DELTA;
drop public synonym MGMT_CURRENT_SEVERITY;
drop public synonym MGMT_CURRENT_METRIC_ERRORS;
drop public synonym MGMT_CURRENT_METRICS;
drop public synonym MGMT_CURRENT_AVAILABILITY;
drop public synonym MGMT_CREDENTIAL;
drop public synonym MGMT_COLLECTION_PROPERTIES;
drop public synonym MGMT_AVAILABILITY;
drop public synonym MGMT_AS_ECM_UTIL;
drop public synonym MGMT_ADMIN;
drop public synonym MGMT$WEBSPHERE_MODULES;
drop public synonym MGMT$WEBSPHERE_JDBCDATASOURCE;
drop public synonym MGMT$WEBSPHERE_APPLICATION;
drop public synonym MGMT$WEBLOGIC_SERVER;
drop public synonym MGMT$WEBLOGIC_MODULES;
drop public synonym MGMT$WEBLOGIC_JDBCDATASOURCE;
drop public synonym MGMT$WEBLOGIC_APPLICATION;
drop public synonym MGMT$WEBCACHE_TO_OHS_ROUTING;
drop public synonym MGMT$WEBCACHE_SITEMAPPINGS;
drop public synonym MGMT$WEBCACHE_SITEALIASES;
drop public synonym MGMT$WEBCACHE_PERFRELATED;
drop public synonym MGMT$WEBCACHE_ORIGIN_SERVERS;
drop public synonym MGMT$WEBCACHE_ORIGINSERVERS;
drop public synonym MGMT$WEBCACHE_OPERATIONPORTS;
drop public synonym MGMT$WEBCACHE_LISTENPORTS;
drop public synonym MGMT$WEBCACHE_CONFIGFILES;
drop public synonym MGMT$WEBCACHE_CLUSTERMEMBERS;
drop public synonym MGMT$WEBCACHE_CLUSTERINFO;
drop public synonym MGMT$TXN_PERF_RAW;
drop public synonym MGMT$TXN_PERF_HOUR;
drop public synonym MGMT$TXN_PERF_DAY;
drop public synonym MGMT$TEMPLATE_POLICY_SETTINGS;
drop public synonym MGMT$TEMPLATE_METRIC_SETTINGS;
drop public synonym MGMT$TEMPLATE_METRICCOLLECTION;
drop public synonym MGMT$TEMPLATES;
drop public synonym MGMT$TARGET_TYPE_PROPERTIES;
drop public synonym MGMT$TARGET_TYPE_DEF;
drop public synonym MGMT$TARGET_TYPE;
drop public synonym MGMT$TARGET_PROPERTIES;
drop public synonym MGMT$TARGET_POLICY_SETTINGS;
drop public synonym MGMT$TARGET_POLICY_EVAL_SUMM;
drop public synonym MGMT$TARGET_POLICIES;
drop public synonym MGMT$TARGET_NOTIF_METR_NOTCOV;
drop public synonym MGMT$TARGET_NOTIFICATION_RULES;
drop public synonym MGMT$TARGET_METRIC_SETTINGS;
drop public synonym MGMT$TARGET_METRIC_COLLECTIONS;
drop public synonym MGMT$TARGET_MEMBERS;
drop public synonym MGMT$TARGET_FLAT_MEMBERS;
drop public synonym MGMT$TARGET_COMPOSITE;
drop public synonym MGMT$TARGET_COMPONENTS;
drop public synonym MGMT$TARGET_ASSOCIATIONS;
drop public synonym MGMT$TARGET;
drop public synonym MGMT$STORAGE_REPORT_VOLUME;
drop public synonym MGMT$STORAGE_REPORT_PATHS;
drop public synonym MGMT$STORAGE_REPORT_NFS;
drop public synonym MGMT$STORAGE_REPORT_LOCALFS;
drop public synonym MGMT$STORAGE_REPORT_KEYS;
drop public synonym MGMT$STORAGE_REPORT_ISSUES;
drop public synonym MGMT$STORAGE_REPORT_DISK;
drop public synonym MGMT$STORAGE_REPORT_DATA;
drop public synonym MGMT$STEP_METRICS_RAW;
drop public synonym MGMT$STEP_METRICS_HOURLY;
drop public synonym MGMT$STEP_METRICS_DAILY;
drop public synonym MGMT$STEP_GROUPS;
drop public synonym MGMT$STEPS;
drop public synonym MGMT$SOFTWARE_PATCHSETS;
drop public synonym MGMT$SOFTWARE_PATCHES_IN_HOMES;
drop public synonym MGMT$SOFTWARE_OTHERS;
drop public synonym MGMT$SOFTWARE_ONEOFF_PATCHES;
drop public synonym MGMT$SOFTWARE_HOME_PROPERTIES;
drop public synonym MGMT$SOFTWARE_HOMES;
drop public synonym MGMT$SOFTWARE_DEPENDENCIES;
drop public synonym MGMT$SOFTWARE_COMP_PATCHSET;
drop public synonym MGMT$SOFTWARE_COMPONENT_PSU;
drop public synonym MGMT$SOFTWARE_COMPONENT_PATCH;
drop public synonym MGMT$SOFTWARE_COMPONENT_ONEOFF;
drop public synonym MGMT$SOFTWARE_COMPONENTS;
drop public synonym MGMT$RAC_TOPOLOGY;
drop public synonym MGMT$RACDB_INTERCONNECTS;
drop public synonym MGMT$PR_HOME_INFO;
drop public synonym MGMT$PR_HOMES_AFFECTED_COUNT;
drop public synonym MGMT$PR_APPL_PATCHES_TO_OH_TGT;
drop public synonym MGMT$PR_APPL_PATCHES_TO_OH;
drop public synonym MGMT$POLICY_VIOL_NOTIF_LOG;
drop public synonym MGMT$POLICY_VIOL_ANNOTATIONS;
drop public synonym MGMT$POLICY_VIOLATION_HISTORY;
drop public synonym MGMT$POLICY_VIOLATION_CURRENT;
drop public synonym MGMT$POLICY_VIOLATION_CTXT;
drop public synonym MGMT$POLICY_VIOLATION_CONTEXT;
drop public synonym MGMT$POLICY_PARAMETERS;
drop public synonym MGMT$POLICIES;
drop public synonym MGMT$PA_RECOM_METRIC_SOURCE;
drop public synonym MGMT$PA_AFFTED_TARGETS;
drop public synonym MGMT$PA_AFFTED_HOST_OH;
drop public synonym MGMT$PA_AFFTED_HOST_GUID_OH;
drop public synonym MGMT$PATCH_ADVISORIES;
drop public synonym MGMT$OVM_VS_VSP_ASSOC;
drop public synonym MGMT$OVM_VS_PERF_SUMMARY;
drop public synonym MGMT$OVM_OS_HW_SUMMARY;
drop public synonym MGMT$OVM_HW_NIC;
drop public synonym MGMT$OVM_GVM_SUMMARY;
drop public synonym MGMT$OS_SUMMARY;
drop public synonym MGMT$OS_PROPERTIES;
drop public synonym MGMT$OS_PATCHES;
drop public synonym MGMT$OS_KERNEL_PARAMS;
drop public synonym MGMT$OS_HW_SUMMARY;
drop public synonym MGMT$OS_FS_MOUNT;
drop public synonym MGMT$OS_COMPONENTS;
drop public synonym MGMT$ORACLE_SW_GRP_TARGETS;
drop public synonym MGMT$ORACLE_SW_GRP_INSTALL;
drop public synonym MGMT$ORACLE_SW_ENT_TARGETS;
drop public synonym MGMT$ORACLE_SW_ENT_INSTALL;
drop public synonym MGMT$OHS_VIRTUALHOSTS;
drop public synonym MGMT$OHS_PERFRELATED;
drop public synonym MGMT$OHS_LISTENADDRESSES;
drop public synonym MGMT$OHS_GENERAL;
drop public synonym MGMT$OHS_CONFIGFILES;
drop public synonym MGMT$OHS_CIPHERSUITES;
drop public synonym MGMT$OC4J_WEBMODULEURLS;
drop public synonym MGMT$OC4J_WEBMODULESETTINGS;
drop public synonym MGMT$OC4J_RESOURCECONFIG;
drop public synonym MGMT$OC4J_PORTRANGES;
drop public synonym MGMT$OC4J_NATIVEDATASOURCES;
drop public synonym MGMT$OC4J_MODULES;
drop public synonym MGMT$OC4J_MANAGEDDATASOURCES;
drop public synonym MGMT$OC4J_J2EE_MODULES;
drop public synonym MGMT$OC4J_GLOBALWEBSETTINGS;
drop public synonym MGMT$OC4J_DEPLOYED_APPS;
drop public synonym MGMT$OC4J_DEPLOYEDAPPS;
drop public synonym MGMT$OC4J_DATASOURCES;
drop public synonym MGMT$OC4J_CONNECTIONPOOLS;
drop public synonym MGMT$OC4J_CONFIGFILES;
drop public synonym MGMT$OC4J_APPLICATIONINFO;
drop public synonym MGMT$OC4J_ALL_DATA_SOURCES;
drop public synonym MGMT$MS_LATEST_APPLIED;
drop public synonym MGMT$MS_APPLY_HISTORY;
drop public synonym MGMT$MISSING_TARGETS_IN_GROUPS;
drop public synonym MGMT$MISSING_TARGETS;
drop public synonym MGMT$METRIC_HOURLY;
drop public synonym MGMT$METRIC_ERROR_HISTORY;
drop public synonym MGMT$METRIC_ERROR_CURRENT;
drop public synonym MGMT$METRIC_DETAILS_SINGLE_STR;
drop public synonym MGMT$METRIC_DETAILS_SINGLE_NUM;
drop public synonym MGMT$METRIC_DETAILS_MULTI_STR;
drop public synonym MGMT$METRIC_DETAILS_MULTI_NUM;
drop public synonym MGMT$METRIC_DETAILS;
drop public synonym MGMT$METRIC_DAILY;
drop public synonym MGMT$METRIC_CURRENT;
drop public synonym MGMT$METRIC_COLLECTION;
drop public synonym MGMT$METRIC_CATEGORIES;
drop public synonym MGMT$MESSAGES;
drop public synonym MGMT$JOB_TARGETS;
drop public synonym MGMT$JOB_STEP_HISTORY;
drop public synonym MGMT$JOB_NOTIFICATION_LOG;
drop public synonym MGMT$JOB_EXECUTION_HISTORY;
drop public synonym MGMT$JOB_ANNOTATIONS;
drop public synonym MGMT$JOBS;
drop public synonym MGMT$JBOSS_RESCONF;
drop public synonym MGMT$JBOSS_PROTOCONF;
drop public synonym MGMT$JBOSS_POOLSCONF;
drop public synonym MGMT$JBOSS_DSCONF;
drop public synonym MGMT$JBOSS_APPSCONF;
drop public synonym MGMT$INTERFACE_STATS;
drop public synonym MGMT$IAS_SIMPLE_PORTS;
drop public synonym MGMT$IAS_PORT_RANGES;
drop public synonym MGMT$IAS_PORTS;
drop public synonym MGMT$IAS_OPMNCONFIG;
drop public synonym MGMT$IAS_MEMBERTARGETS;
drop public synonym MGMT$IAS_INSTALL_SETTINGS;
drop public synonym MGMT$IAS_GENERAL;
drop public synonym MGMT$IAS_DEPLOYEDAPPS;
drop public synonym MGMT$IAS_DCMCONFIG;
drop public synonym MGMT$IAS_CONSOLECONFIG;
drop public synonym MGMT$IAS_CONFIG_PROPS;
drop public synonym MGMT$IAS_CONFIGFILES;
drop public synonym MGMT$HW_NIC;
drop public synonym MGMT$HOSTPATCH_HOST_COMPL;
drop public synonym MGMT$HOSTPATCH_HOSTS;
drop public synonym MGMT$HOSTPATCH_GRP_COMPL_HIST;
drop public synonym MGMT$HOSTPATCH_GROUPS;
drop public synonym MGMT$HOMES_AFFECTED;
drop public synonym MGMT$HA_RMAN_CONFIG_ALL;
drop public synonym MGMT$HA_RMAN_CONFIG;
drop public synonym MGMT$HA_MTTR;
drop public synonym MGMT$HA_INIT_PARAMS_ALL;
drop public synonym MGMT$HA_INIT_PARAMS;
drop public synonym MGMT$HA_INFO_ALL;
drop public synonym MGMT$HA_INFO;
drop public synonym MGMT$HA_FILES_ALL;
drop public synonym MGMT$HA_FILES;
drop public synonym MGMT$HA_BACKUP;
drop public synonym MGMT$GRP_METRICS_RAW;
drop public synonym MGMT$GRP_METRICS_HOURLY;
drop public synonym MGMT$GRP_METRICS_DAILY;
drop public synonym MGMT$GROUP_MEMBERS;
drop public synonym MGMT$GROUP_FLAT_MEMBERSHIPS;
drop public synonym MGMT$GROUP_DERIVED_MEMBERSHIPS;
drop public synonym MGMT$ESA_WITH_GRANT_REPORT;
drop public synonym MGMT$ESA_WITH_ADMIN_REPORT;
drop public synonym MGMT$ESA_TRC_AUD_PERM_REP_NT;
drop public synonym MGMT$ESA_TRC_AUD_PERM_REPORT;
drop public synonym MGMT$ESA_TABSP_OWNERS_REPORT;
drop public synonym MGMT$ESA_SYS_PUB_PKG_REPORT;
drop public synonym MGMT$ESA_PUB_PRIV_REPORT;
drop public synonym MGMT$ESA_POWER_PRIV_REPORT;
drop public synonym MGMT$ESA_OH_PERMISSION_REPORT;
drop public synonym MGMT$ESA_OH_OWNERSHIP_REPORT;
drop public synonym MGMT$ESA_KEY_OBJECTS_REPORT;
drop public synonym MGMT$ESA_EXMPT_ACCESS_REPORT;
drop public synonym MGMT$ESA_DIRECT_PRIV_REPORT;
drop public synonym MGMT$ESA_DBA_ROLE_REPORT;
drop public synonym MGMT$ESA_DBA_GROUP_REPORT;
drop public synonym MGMT$ESA_CREATE_PRIV_REPORT;
drop public synonym MGMT$ESA_CONN_PRIV_REPORT;
drop public synonym MGMT$ESA_CATALOG_REPORT;
drop public synonym MGMT$ESA_BECOME_USER_REPORT;
drop public synonym MGMT$ESA_AUDIT_SYSTEM_REPORT;
drop public synonym MGMT$ESA_ANY_PRIV_REPORT;
drop public synonym MGMT$ESA_ANY_DICT_REPORT;
drop public synonym MGMT$ESA_ALL_PRIVS_REPORT;
drop public synonym MGMT$EM_HOMES_PLATFORM;
drop public synonym MGMT$ECM_VISIBLE_SNAPSHOTS;
drop public synonym MGMT$ECM_CURRENT_SNAPSHOTS;
drop public synonym MGMT$ECM_CONFIG_HISTORY_KEY6;
drop public synonym MGMT$ECM_CONFIG_HISTORY_KEY5;
drop public synonym MGMT$ECM_CONFIG_HISTORY_KEY4;
drop public synonym MGMT$ECM_CONFIG_HISTORY_KEY3;
drop public synonym MGMT$ECM_CONFIG_HISTORY_KEY2;
drop public synonym MGMT$ECM_CONFIG_HISTORY_KEY1;
drop public synonym MGMT$ECM_CONFIG_HISTORY;
drop public synonym MGMT$E2E_RAW;
drop public synonym MGMT$E2E_HOURLY;
drop public synonym MGMT$E2E_1DAY;
drop public synonym MGMT$DELTA_VIEW_DETAILS;
drop public synonym MGMT$DELTA_VIEW;
drop public synonym MGMT$DELTA_VENDOR_SW;
drop public synonym MGMT$DELTA_PATCHSET_DETAILS;
drop public synonym MGMT$DELTA_PATCHSETS;
drop public synonym MGMT$DELTA_OS_KERNEL_PARAMS;
drop public synonym MGMT$DELTA_OS_COMP_DETAILS;
drop public synonym MGMT$DELTA_OS_COMPONENTS;
drop public synonym MGMT$DELTA_ORACLE_HOME;
drop public synonym MGMT$DELTA_ONEOFF_PATCHES;
drop public synonym MGMT$DELTA_INIT;
drop public synonym MGMT$DELTA_HOST_CONFIG;
drop public synonym MGMT$DELTA_HARDWARE;
drop public synonym MGMT$DELTA_FS_MOUNT;
drop public synonym MGMT$DELTA_COMPONENT_DETAILS;
drop public synonym MGMT$DELTA_COMPONENTS;
drop public synonym MGMT$DB_TABLESPACES_ALL;
drop public synonym MGMT$DB_TABLESPACES;
drop public synonym MGMT$DB_SGA_ALL;
drop public synonym MGMT$DB_SGA;
drop public synonym MGMT$DB_ROLLBACK_SEGS_ALL;
drop public synonym MGMT$DB_ROLLBACK_SEGS;
drop public synonym MGMT$DB_REDOLOGS_ALL;
drop public synonym MGMT$DB_REDOLOGS;
drop public synonym MGMT$DB_OPTIONS_ALL;
drop public synonym MGMT$DB_OPTIONS;
drop public synonym MGMT$DB_LICENSE_ALL;
drop public synonym MGMT$DB_LICENSE;
drop public synonym MGMT$DB_INIT_PARAMS_ALL;
drop public synonym MGMT$DB_INIT_PARAMS;
drop public synonym MGMT$DB_FEATUREUSAGE;
drop public synonym MGMT$DB_DBNINSTANCEINFO_ALL;
drop public synonym MGMT$DB_DBNINSTANCEINFO;
drop public synonym MGMT$DB_DATAFILES_ALL;
drop public synonym MGMT$DB_DATAFILES;
drop public synonym MGMT$DB_CONTROLFILES_ALL;
drop public synonym MGMT$DB_CONTROLFILES;
drop public synonym MGMT$CS_EVAL_SUMMARY_STANDARD;
drop public synonym MGMT$CS_EVAL_SUMMARY_RULE;
drop public synonym MGMT$CS_CONFIG_STANDARDS;
drop public synonym MGMT$CSM_WATCHLIST;
drop public synonym MGMT$CSM_URL_HOURLY;
drop public synonym MGMT$CSM_URL_DIST_HOURLY;
drop public synonym MGMT$CSM_URL_DIST_DAILY;
drop public synonym MGMT$CSM_URL_DAILY;
drop public synonym MGMT$CSM_SUBNET_HOURLY;
drop public synonym MGMT$CSM_SUBNET_DIST_HOURLY;
drop public synonym MGMT$CSM_SUBNET_DIST_DAILY;
drop public synonym MGMT$CSM_SUBNET_DAILY;
drop public synonym MGMT$CSM_REGION_HOURLY;
drop public synonym MGMT$CSM_REGION_DIST_HOURLY;
drop public synonym MGMT$CSM_REGION_DIST_DAILY;
drop public synonym MGMT$CSM_REGION_DAILY;
drop public synonym MGMT$CSM_REGION;
drop public synonym MGMT$CSM_MT_URL_HOURLY;
drop public synonym MGMT$CSM_MT_URL_DIST_HOURLY;
drop public synonym MGMT$CSM_MT_URL_DIST_DAILY;
drop public synonym MGMT$CSM_MT_URL_DAILY;
drop public synonym MGMT$CSM_MT_METRIC_DETAILS;
drop public synonym MGMT$CSM_MT_IP_HOURLY;
drop public synonym MGMT$CSM_MT_IP_DIST_HOURLY;
drop public synonym MGMT$CSM_MT_IP_DIST_DAILY;
drop public synonym MGMT$CSM_MT_IP_DAILY;
drop public synonym MGMT$CSM_MT_DSR_HOURLY;
drop public synonym MGMT$CSM_MT_DSR_DIST_HOURLY;
drop public synonym MGMT$CSM_MT_DSR_DIST_DAILY;
drop public synonym MGMT$CSM_MT_DSR_DAILY;
drop public synonym MGMT$CSM_METRIC_DETAILS;
drop public synonym MGMT$CSM_IP_HOURLY;
drop public synonym MGMT$CSM_IP_DIST_HOURLY;
drop public synonym MGMT$CSM_IP_DIST_DAILY;
drop public synonym MGMT$CSM_IP_DAILY;
drop public synonym MGMT$CSM_DOMAIN_HOURLY;
drop public synonym MGMT$CSM_DOMAIN_DIST_HOURLY;
drop public synonym MGMT$CSM_DOMAIN_DIST_DAILY;
drop public synonym MGMT$CSM_DOMAIN_DAILY;
drop public synonym MGMT$CSA_HOST_SW;
drop public synonym MGMT$CSA_HOST_RULES;
drop public synonym MGMT$CSA_HOST_OS_PROPERTIES;
drop public synonym MGMT$CSA_HOST_OS_FILESYSTEMS;
drop public synonym MGMT$CSA_HOST_OS_COMPONENTS;
drop public synonym MGMT$CSA_HOST_NICS;
drop public synonym MGMT$CSA_HOST_IOCARDS;
drop public synonym MGMT$CSA_HOST_CUSTOM;
drop public synonym MGMT$CSA_HOST_CPUS;
drop public synonym MGMT$CSA_HOST_COOKIES;
drop public synonym MGMT$CSA_FAILED;
drop public synonym MGMT$CSA_COLLECTIONS;
drop public synonym MGMT$CSA_CLIENT_RULE_VIOLS;
drop public synonym MGMT$CSA_CLIENTS;
drop public synonym MGMT$CPF_PATCH_INFO;
drop public synonym MGMT$CPF_PATCH_DATA;
drop public synonym MGMT$CPF_HOMES_INFO;
drop public synonym MGMT$CPF_ADVISORY_INFO;
drop public synonym MGMT$CONNECTOR_TYPE;
drop public synonym MGMT$CONNECTOR;
drop public synonym MGMT$CLUSTER_INTERCONNECTS;
drop public synonym MGMT$BLACKOUT_HISTORY;
drop public synonym MGMT$BLACKOUTS;
drop public synonym MGMT$AVAIL_ALERT_HISTORY;
drop public synonym MGMT$AVAILABILITY_HISTORY;
drop public synonym MGMT$AVAILABILITY_CURRENT;
drop public synonym MGMT$AUDIT_LOG;
drop public synonym MGMT$APPL_PATCH_AND_PATCHSET;
drop public synonym MGMT$APPLIED_PATCHSETS;
drop public synonym MGMT$APPLIED_PATCHES;
drop public synonym MGMT$ALERT_NOTIF_LOG;
drop public synonym MGMT$ALERT_HISTORY;
drop public synonym MGMT$ALERT_CURRENT;
drop public synonym MGMT$ALERT_ANNOTATIONS;
drop public synonym EMD_MNTR;
drop public synonym ECM_UTIL;
drop synonym DBSNMP.MGMT_EMREP_OOB_MONITORING;

drop public synonym MGMT$ADR_INFO;
drop public synonym MGMT$ARU_FAMILY_PRODUCT_MAP;
drop public synonym MGMT$ARU_LANGUAGES;
drop public synonym MGMT$ARU_PATCH_RECOM;
drop public synonym MGMT$ARU_PATCH_RECOMMENDATIONS;
drop public synonym MGMT$ARU_PLATFORMS;
drop public synonym MGMT$ARU_PRODUCTS;
drop public synonym MGMT$ARU_PRODUCT_RELEASE_MAP;
drop public synonym MGMT$ARU_RELEASES;
drop public synonym MGMT$ARU_TARGETS;
drop public synonym MGMT$CCC_ALL_OBSERVATIONS;
drop public synonym MGMT$CCC_ALL_OBS_BUNDLES;
drop public synonym MGMT$CCC_DIAG_ANALYTICS;
drop public synonym MGMT$CCC_DIAG_QUEUEBACKLOG;
drop public synonym MGMT$CCS_DATA;
drop public synonym MGMT$CCS_DATA_SOURCE;
drop public synonym MGMT$CCS_DATA_SOURCE_VISIBLE;
drop public synonym MGMT$CCS_DATA_VISIBLE;
drop public synonym MGMT$CM_CHANGE_PLANS;
drop public synonym MGMT$CM_CP_SYNCH_CONFLICTS;
drop public synonym MGMT$CM_C_P_CHNG_ITEMS;
drop public synonym MGMT$COMPLIANCE_STANDARD;
drop public synonym MGMT$COMPLIANCE_STANDARD_GROUP;
drop public synonym MGMT$COMPLIANCE_STANDARD_RULE;
drop public synonym MGMT$COMPOSITE_CS_EVAL_SUMMARY;
drop public synonym MGMT$CONFIG_VERSION;
drop public synonym MGMT$CS_ASM_DISKGRP_SETTINGS;
drop public synonym MGMT$CS_DBM_CELL_GROUPS;
drop public synonym MGMT$CS_DBM_GRIDDISK_MISCONF;
drop public synonym MGMT$CS_DB_CONTROL_FILE_COUNT;
drop public synonym MGMT$CS_DB_INIT_PARAMS;
drop public synonym MGMT$CS_DB_RBS_IN_SYSTEM;
drop public synonym MGMT$CS_DB_REC_SEG_SETTINGS;
drop public synonym MGMT$CS_DB_REC_TS_SETTINGS;
drop public synonym MGMT$CS_DB_REC_USER_SETTINGS;
drop public synonym MGMT$CS_DB_REDO_LOGS;
drop public synonym MGMT$CS_DB_REDO_LOG_COUNT;
drop public synonym MGMT$CS_DB_TABLESPACES;
drop public synonym MGMT$CS_DB_TBSP_SEGSPACE_MGMT;
drop public synonym MGMT$CS_EVAL_SUMMARY;
drop public synonym MGMT$CS_FEATURE_USAGE;
drop public synonym MGMT$CS_GROUP_EVAL_SUMMARY;
drop public synonym CM$COMPOSITE_WIRES_CONFIG;
drop public synonym CM$COREID_ACCESS_CONFIG_INFO;
drop public synonym CM$COREID_ACS_PROFILECONFIG_IN;
drop public synonym CM$COREID_ACS_SERVERCONFIG_INF;
drop public synonym CM$COREID_IDENTITY_CONFIG_INFO;
drop public synonym CM$EM$ECM_CSA_CUSTOM;
drop public synonym CM$EM$ECM_HW_CPU;
drop public synonym CM$EM$ECM_HW_IOCARD;
drop public synonym CM$EM$ECM_HW_NIC;
drop public synonym CM$EMAS_COH_CACHE_CONFIG_ECM;
drop public synonym CM$EMAS_COH_CLUSTER_CONFIG_ECM;
drop public synonym CM$EMAS_COH_CMGR_CONFIG_ECM;
drop public synonym CM$EMAS_COH_CONN_CONFIG_ECM;
drop public synonym CM$EMAS_COH_GCAFTER_CONFIG_ECM;
drop public synonym CM$EMAS_COH_GCBEF_CONFIG_ECM;
drop public synonym CM$EMAS_COH_HTTP_SESSION_ECM;
drop public synonym CM$EMAS_COH_MEMORY_CONFIG_ECM;
drop public synonym CM$EMAS_COH_NODE_CONFIG_ECM;
drop public synonym CM$EMAS_COH_OS_CONFIG_ECM;
drop public synonym CM$EMAS_COH_RUNTIME_CONFIG_ECM;
drop public synonym CM$EMAS_COH_SERVICE_CONFIG_ECM;
drop public synonym CM$EMAS_COH_WKA_CONFIG_ECM;
drop public synonym CM$EMAS_TM_CONFIG_ECM;
drop public synonym CM$EM_CCS_DATA_SOURCE;
drop public synonym CM$EM_CCS_PARSED_DATA;
drop public synonym CM$EM_CELL_HCA_CONFIG;
drop public synonym CM$EM_CELL_HCA_PORT_CONFIG;
drop public synonym CM$EM_DBM_COMPUTE_NODES;
drop public synonym CM$EM_ESM_FILESYS;
drop public synonym CM$EM_ESM_PORTS;
drop public synonym CM$EM_ESM_SERVICE;
drop public synonym CM$EM_ESM_STACK;
drop public synonym CM$EM_EXADATA_CELL;
drop public synonym CM$EM_EXADATA_CELLDISK;
drop public synonym CM$EM_EXADATA_FCACHE;
drop public synonym CM$EM_EXADATA_FCACHE_CDISK;
drop public synonym CM$EM_EXADATA_GD_CLIENTS;
drop public synonym CM$EM_EXADATA_GRIDDISK;
drop public synonym CM$EM_EXADATA_IORM;
drop public synonym CM$EM_EXADATA_IORM_CATPLAN;
drop public synonym CM$EM_EXADATA_IORM_DBPLAN;
drop public synonym CM$EM_EXADATA_LUN;
drop public synonym CM$EM_EXADATA_LUN_PDISKS;
drop public synonym CM$EM_EXADATA_PDISK_LUNS;
drop public synonym CM$EM_EXADATA_PHYS_DISK;
drop public synonym CM$EM_HOST_HCA_CONFIG;
drop public synonym CM$EM_HOST_HCA_PORT_CONFIG;
drop public synonym CM$EM_IB_NETWORK_CONNECTIONS;
drop public synonym CM$EM_IB_SWITCH_CONFIG_SUMMARY;
drop public synonym CM$EM_IB_SWITCH_PORT_CONFIG;
drop public synonym CM$EM_IB_SWITCH_VERSION;
drop public synonym CM$EM_ILOM_SYS_INFO;
drop public synonym CM$EM_TPROPS_ECM_VIEW;
drop public synonym CM$ESM_COLLECTION;
drop public synonym CM$ESS_DISPATCHER_CONFIG;
drop public synonym CM$ESS_PROCESSOR_CONFIG;
drop public synonym CM$MGMT_ASM_CLUSTER_ECM;
drop public synonym CM$MGMT_ASM_DG_ATTR_ECM;
drop public synonym CM$MGMT_ASM_DISKGROUP_ECM;
drop public synonym CM$MGMT_ASM_DISK_ECM;
drop public synonym CM$MGMT_ASM_INIT_PARAMS_ECM;
drop public synonym CM$MGMT_ASM_INSTANCE_ECM;
drop public synonym CM$MGMT_BI_ANS_CONFIG;
drop public synonym CM$MGMT_BI_CC_CONFIG;
drop public synonym CM$MGMT_BI_DAC_CONFIG;
drop public synonym CM$MGMT_BI_DAC_ETL_CONFIG;
drop public synonym CM$MGMT_BI_PS_ALERTS_CONFIG;
drop public synonym CM$MGMT_BI_PS_CACHE_CONFIG;
drop public synonym CM$MGMT_BI_PS_CHARTS_CONFIG;
drop public synonym CM$MGMT_BI_PS_CLIENT_CONFIG;
drop public synonym CM$MGMT_BI_PS_CONFIG;
drop public synonym CM$MGMT_BI_PS_DISPLAY_CONFIG;
drop public synonym CM$MGMT_BI_PS_MARKETING_CONFIG;
drop public synonym CM$MGMT_BI_PS_OTHERS_CONFIG;
drop public synonym CM$MGMT_BI_PS_REP_CACHE_CONFIG;
drop public synonym CM$MGMT_BI_PS_STATE_POOL_CONFI;
drop public synonym CM$MGMT_BI_PS_THREADPOOL_CONFI;
drop public synonym CM$MGMT_BI_PS_XML_CACHEDEF_CON;
drop public synonym CM$MGMT_BI_SCH_IBOTS_CONFIG;
drop public synonym CM$MGMT_BI_SCH_MAIL_CONFIG;
drop public synonym CM$MGMT_BPELPROCESS_CONFIG;
drop public synonym CM$MGMT_BPEL_CLUSTER_CONFIG;
drop public synonym CM$MGMT_BPEL_CONFIG;
drop public synonym CM$MGMT_BPEL_CONFIGFILES;
drop public synonym CM$MGMT_BPEL_DOMAIN_CONFIG;
drop public synonym CM$MGMT_BPEL_DSTORE_CONFIG;
drop public synonym CM$MGMT_BPEL_GENERAL_CONFIG;
drop public synonym CM$MGMT_BPEL_PROCESS_CONFIGFIL;
drop public synonym CM$MGMT_BPMN_CONFIG;
drop public synonym CM$MGMT_CLUSTER_ACTV_SRVS_ECM;
drop public synonym CM$MGMT_CLUSTER_CONFIG;
drop public synonym CM$MGMT_CLUSTER_CSS_NODES_ECM;
drop public synonym CM$MGMT_CLUSTER_PSRVPOOLS_ECM;
drop public synonym CM$MGMT_CLUSTER_RESOURCES_ECM;
drop public synonym CM$MGMT_CLUSTER_SRVPOOLS_ECM;
drop public synonym CM$MGMT_COMPOSITE_CONFIG;
drop public synonym CM$MGMT_COMPOSITE_SENSOR_CONFI;
drop public synonym CM$MGMT_CONFIG_VERSION_ECM;
drop public synonym CM$MGMT_COREID_ACCESS_SYS_INFO;
drop public synonym CM$MGMT_COREID_IDENTITY_SYS_IN;
drop public synonym CM$MGMT_DB_ASM_DISK_ECM;
drop public synonym CM$MGMT_DB_AUTOTASK_CLIENT_ECM;
drop public synonym CM$MGMT_DB_COMPONENTS_ECM;
drop public synonym CM$MGMT_DB_CONTROLFILES_ECM;
drop public synonym CM$MGMT_DB_CPU_USAGE_ECM;
drop public synonym CM$MGMT_DB_DATAFILES_ECM;
drop public synonym CM$MGMT_DB_DBNINSTANCEINFO_ECM;
drop public synonym CM$MGMT_DB_HIGHWATERMARK_ECM;
drop public synonym CM$MGMT_DB_INIT_PARAMS_ECM;
drop public synonym CM$MGMT_DB_INST_ONLY_INFO_ECM;
drop public synonym CM$MGMT_DB_INVOBJS_ECM;
drop public synonym CM$MGMT_DB_INVOBJ_CNT_ECM;
drop public synonym CM$MGMT_DB_LICENSE_ECM;
drop public synonym CM$MGMT_DB_LSNR_CONFIG_ECM;
drop public synonym CM$MGMT_DB_OPTIONS_ECM;
drop public synonym CM$MGMT_DB_RECSEGMENTSETTINGS_;
drop public synonym CM$MGMT_DB_RECTSSETTINGS_ECM;
drop public synonym CM$MGMT_DB_RECUSERSETTINGS_ECM;
drop public synonym CM$MGMT_DB_REDOLOGS_ECM;
drop public synonym CM$MGMT_DB_ROLLBACK_SEGS_ECM;
drop public synonym CM$MGMT_DB_SCHEDULER_JOBS_ECM;
drop public synonym CM$MGMT_DB_SERVICES_ECM;
drop public synonym CM$MGMT_DB_SGA_ECM;
drop public synonym CM$MGMT_DB_TABLESPACES_ECM;
drop public synonym CM$MGMT_DB_USERS_ECM;
drop public synonym CM$MGMT_DB_VOTING_DISK;
drop public synonym CM$MGMT_ECM_ADR_INFO;
drop public synonym CM$MGMT_ECM_CSA;
drop public synonym CM$MGMT_ECM_CSA_GENERAL_INFO;
drop public synonym CM$MGMT_ECM_CSA_RULES;
drop public synonym CM$MGMT_ECM_EMD_ALL_PROPERTIES;
drop public synonym CM$MGMT_ECM_EMD_CONFIG_PARAMS;
drop public synonym CM$MGMT_ECM_EMD_JAVA_PARAMS;
drop public synonym CM$MGMT_ECM_EMD_TARGETS;
drop public synonym CM$MGMT_LDAP_LDAPSERVERDBINFOC;
drop public synonym CM$MGMT_LDAP_OIDREPLTOPOLOGY;
drop public synonym CM$MGMT_LISTENER_PORTS_ECM;
drop public synonym CM$MGMT_LISTENER_SERVICES_ECM;
drop public synonym CM$MGMT_LL_HOME_INFO;
drop public synonym CM$MGMT_LL_INV_CLONE_PROPS;
drop public synonym CM$MGMT_LL_INV_COMPONENT;
drop public synonym CM$MGMT_LL_INV_COMP_INST_TYPE;
drop public synonym CM$MGMT_LL_INV_CRS_NODES;
drop public synonym CM$MGMT_LL_INV_DEPENDENCY_RULE;
drop public synonym CM$MGMT_LL_INV_DEP_HOMES;
drop public synonym CM$MGMT_LL_INV_PATCHED_FILE;
drop public synonym CM$MGMT_LL_INV_PATCHES;
drop public synonym CM$MGMT_LL_INV_PATCHSET;
drop public synonym CM$MGMT_LL_INV_PATCH_BUGFIX;
drop public synonym CM$MGMT_LL_INV_PATCH_COMP;
drop public synonym CM$MGMT_LL_INV_SUMMARY;
drop public synonym CM$MGMT_LL_INV_VERSIONED_PATCH;
drop public synonym CM$MGMT_MEDIATOR_CONFIG;
drop public synonym CM$MGMT_OC4J_ADAPTER_CONFIGFIL;
drop public synonym CM$MGMT_OC4J_ADAPTER_CONFIGPAR;
drop public synonym CM$MGMT_OC4J_APPLICATIONINFO;
drop public synonym CM$MGMT_OC4J_CONFIGFILES;
drop public synonym CM$MGMT_OC4J_CONNECTIONPOOLS;
drop public synonym CM$MGMT_OC4J_DATASOURCES;
drop public synonym CM$MGMT_OC4J_DEPLOYEDAPPS;
drop public synonym CM$MGMT_OC4J_GLOBALWEBSETTINGS;
drop public synonym CM$MGMT_OC4J_MANAGEDDATASOURCE;
drop public synonym CM$MGMT_OC4J_MODULES;
drop public synonym CM$MGMT_OC4J_NATIVEDATASOURCES;
drop public synonym CM$MGMT_OC4J_PORTRANGES;
drop public synonym CM$MGMT_OC4J_RESOURCECONFIG;
drop public synonym CM$MGMT_OC4J_WEBMODULESETTINGS;
drop public synonym CM$MGMT_OC4J_WEBMODULEURLS;
drop public synonym CM$MGMT_OHS_CIPHERSUITES;
drop public synonym CM$MGMT_OHS_CONFIGFILES;
drop public synonym CM$MGMT_OHS_GENERAL;
drop public synonym CM$MGMT_OHS_LISTENADDRESSES;
drop public synonym CM$MGMT_OHS_PERFRELATED;
drop public synonym CM$MGMT_OHS_ROUTINGGENERAL;
drop public synonym CM$MGMT_OHS_ROUTINGLOCATIONS;
drop public synonym CM$MGMT_OHS_VIRTUALHOSTS;
drop public synonym CM$MGMT_OID_GENERAL_CONFIG;
drop public synonym CM$MGMT_OID_LOG_CONFIG;
drop public synonym CM$MGMT_OID_PERFORMANCE_CONFIG;
drop public synonym CM$MGMT_OID_SASL_CONFIG;
drop public synonym CM$MGMT_OID_STATS_CONFIG;
drop public synonym CM$MGMT_OIF_IDPSAML1X_ASSERTPR;
drop public synonym CM$MGMT_OIF_SPSAML1X_ASSERTPRO;
drop public synonym CM$MGMT_ORACLE_BI_SCH_CONFIG;
drop public synonym CM$MGMT_ORACLE_FED_SYS_INFO;
drop public synonym CM$MGMT_ORACLE_HAS_CONFIG;
drop public synonym CM$MGMT_ORACLE_HAS_IC_ECM;
drop public synonym CM$MGMT_ORACLE_IDM_SYSTEM_INFO;
drop public synonym CM$MGMT_OSB_BIZ_RESOURCE_CONFI;
drop public synonym CM$MGMT_OSB_BIZ_TRANSPORT_CONF;
drop public synonym CM$MGMT_OSB_FE_DETAIL_CONFIG;
drop public synonym CM$MGMT_OSB_GLOBAL_CONFIG;
drop public synonym CM$MGMT_OSB_JMSQ_CONFIG;
drop public synonym CM$MGMT_OSB_PROXY_RESOURCE_CON;
drop public synonym CM$MGMT_OSB_REPORTING_DBDETAIL;
drop public synonym CM$MGMT_OSM_DISK_GROUP_ECM;
drop public synonym CM$MGMT_RACDB_CONFIG_ECM;
drop public synonym CM$MGMT_RACDB_SRVPOOLS_ECM;
drop public synonym CM$MGMT_RAC_SERVICES;
drop public synonym CM$MGMT_SOACOMPONENT_CONFIG;
drop public synonym CM$MGMT_SOAINFRA_CONFIG;
drop public synonym CM$MGMT_SOAREFERENCE_CONFIG;
drop public synonym CM$MGMT_SOASERVICE_CONFIG;
drop public synonym CM$MGMT_SOA_ENGINE_CONFIG;
drop public synonym CM$MGMT_SSO_SSOSERVER_DB_INFOC;
drop public synonym CM$MGMT_SSO_SSOSERVER_LDAP_INF;
drop public synonym CM$MGMT_STATSPACK_CONFIG_ECM;
drop public synonym CM$MGMT_STORAGE_REPORT_ALIAS;
drop public synonym CM$MGMT_STORAGE_REPORT_DATA;
drop public synonym CM$MGMT_STORAGE_REPORT_ISSUES;
drop public synonym CM$MGMT_STORAGE_REPORT_KEYS;
drop public synonym CM$MGMT_SYSTEM_CHANGES;
drop public synonym CM$MGMT_WEBCACHE_CLUSTERINFO;
drop public synonym CM$MGMT_WEBCACHE_CLUSTERMEMBER;
drop public synonym CM$MGMT_WEBCACHE_CONFIGFILES;
drop public synonym CM$MGMT_WEBCACHE_LISTENPORTS;
drop public synonym CM$MGMT_WEBCACHE_OPERATIONPORT;
drop public synonym CM$MGMT_WEBCACHE_ORIGINSERVERS;
drop public synonym CM$MGMT_WEBCACHE_PERFRELATED;
drop public synonym CM$MGMT_WEBCACHE_SITEALIASES;
drop public synonym CM$MGMT_WEBCACHE_SITEMAPPINGS;
drop public synonym CM$MGMT_WEBSPHERE_APPLICATION;
drop public synonym CM$MGMT_WEBSPHERE_EJBCOMPONENT;
drop public synonym CM$MGMT_WEBSPHERE_JDBCDATASOUR;
drop public synonym CM$MGMT_WEBSPHERE_JDBCPROVIDER;
drop public synonym CM$MGMT_WEBSPHERE_PORTCONFIG;
drop public synonym CM$MGMT_WEBSPHERE_RESOURCECONF;
drop public synonym CM$MGMT_WEBSPHERE_WEBAPPCOMPON;
drop public synonym CM$MGMT_WORKFLOW_CONFIG;
drop public synonym CM$MGMT_WORKFLOW_DAF_CONFIG;
drop public synonym CM$MGMT_ECM_HW;
drop public synonym CM$MGMT_ECM_HW_CPU;
drop public synonym CM$MGMT_ECM_HW_IOCARD;
drop public synonym CM$MGMT_ECM_HW_NIC;
drop public synonym CM$MGMT_ECM_HW_NIC_BONDS;
drop public synonym CM$MGMT_ECM_NON_OUI_BUGS_FIXED;
drop public synonym CM$MGMT_ECM_OMS_CONFIG_PARAMS;
drop public synonym CM$MGMT_ECM_OS;
drop public synonym CM$MGMT_ECM_OS_COMPONENT;
drop public synonym CM$MGMT_ECM_OS_FILESYSTEM;
drop public synonym CM$MGMT_ECM_OS_INIT_SERVICES;
drop public synonym CM$MGMT_ECM_OS_MODULES;
drop public synonym CM$MGMT_ECM_OS_PROPERTY;
drop public synonym CM$MGMT_ECM_OS_REGISTERED_SW;
drop public synonym CM$MGMT_ECM_OS_ULIMITS;
drop public synonym CM$MGMT_ECM_SERVICETAG_INSTANC;
drop public synonym CM$MGMT_ECM_SERVICETAG_REGISTR;
drop public synonym CM$MGMT_EMREP_DBA_TAB_PRIVS;
drop public synonym CM$MGMT_EMREP_DB_INFO;
drop public synonym CM$MGMT_EMREP_OMS_NAME;
drop public synonym CM$MGMT_EMREP_OMS_SECURE_ECM;
drop public synonym CM$MGMT_ESA_REPORT;
drop public synonym CM$MGMT_FEATURE_USAGE_ECM;
drop public synonym CM$MGMT_GEN_APACHE_CIPHERSUITE;
drop public synonym CM$MGMT_GEN_APACHE_GENERAL;
drop public synonym CM$MGMT_GEN_APACHE_LISTENADDRE;
drop public synonym CM$MGMT_GEN_APACHE_PERFRELATED;
drop public synonym CM$MGMT_GEN_APACHE_ROUTINGINFO;
drop public synonym CM$MGMT_GEN_APACHE_VIRTUALHOST;
drop public synonym CM$MGMT_HAS_MANAGED_ASM_ECM;
drop public synonym CM$MGMT_HAS_MANAGED_DB_ECM;
drop public synonym CM$MGMT_HAS_MANAGED_LSNR_ECM;
drop public synonym CM$MGMT_HA_CLS_INTR_CONN;
drop public synonym CM$MGMT_HA_FILES_ECM;
drop public synonym CM$MGMT_HA_INFO_ECM;
drop public synonym CM$MGMT_HA_INIT_PARAMS_ECM;
drop public synonym CM$MGMT_HA_RAC_INTR_CONN;
drop public synonym CM$MGMT_HA_RMAN_CONFIG_ECM;
drop public synonym CM$MGMT_IAS_CONFIGFILES;
drop public synonym CM$MGMT_IAS_CONFIG_PROPS;
drop public synonym CM$MGMT_IAS_CONSOLECONFIG;
drop public synonym CM$MGMT_IAS_DCMCONFIG;
drop public synonym CM$MGMT_IAS_DEPLOYEDAPPS;
drop public synonym CM$MGMT_IAS_DIP_CONFIG;
drop public synonym CM$MGMT_IAS_GENERAL;
drop public synonym CM$MGMT_IAS_MEMBERTARGETS;
drop public synonym CM$MGMT_IAS_OIF_DATASTORE_CONF;
drop public synonym CM$MGMT_IAS_OIF_IDPSAML20_ASSE;
drop public synonym CM$MGMT_IAS_OIF_IDPSAML20_PROT;
drop public synonym CM$MGMT_IAS_OIF_IDP_CONFIG;
drop public synonym CM$MGMT_IAS_OIF_IDP_WSFED11PRO;
drop public synonym CM$MGMT_IAS_OIF_SERVER_CONFIG;
drop public synonym CM$MGMT_IAS_OIF_SPSAML20_ASSER;
drop public synonym CM$MGMT_IAS_OIF_SPSAML20_PROTO;
drop public synonym CM$MGMT_IAS_OIF_SP_CONFIG;
drop public synonym CM$MGMT_IAS_OIF_SP_WSFED11PROP;
drop public synonym CM$MGMT_IAS_OPMNCONFIG;
drop public synonym CM$MGMT_IAS_OVD_LSNR_CONFIG;
drop public synonym CM$MGMT_IAS_OVD_SCHEMA_LOC_CON;
drop public synonym CM$MGMT_IAS_OVD_SEARCH_CONFIG;
drop public synonym CM$MGMT_IAS_OVD_SERVER_CONFIG;
drop public synonym CM$MGMT_JBOSS_APPSCONFIG;
drop public synonym CM$MGMT_JBOSS_DSCONFIG;
drop public synonym CM$MGMT_JBOSS_GENERALCONFIG;
drop public synonym CM$MGMT_JBOSS_POOLCONFIG;
drop public synonym CM$MGMT_JBOSS_PROTOCONFIG;
drop public synonym CM$MGMT_JBOSS_RESOURCECONFIG;
drop public synonym CM$MGMT_JVM_CONFIG_ECM;
drop public synonym MGMT$WEBLOGIC_APPLICATIONS;
drop public synonym MGMT$WEBLOGIC_CLUSTER;
drop public synonym MGMT$WEBLOGIC_DOMAIN;
drop public synonym MGMT$WEBLOGIC_EJBCOMPONENT;
drop public synonym MGMT$WEBLOGIC_JDBCMULTIDS;
drop public synonym MGMT$WEBLOGIC_JMSCONNFACTORY;
drop public synonym MGMT$WEBLOGIC_JMSQUEUE;
drop public synonym MGMT$WEBLOGIC_JMSTOPIC;
drop public synonym MGMT$WEBLOGIC_JOLTCONNPOOL;
drop public synonym MGMT$WEBLOGIC_MACHINE;
drop public synonym MGMT$WEBLOGIC_NETWORK_CHANNELS;
drop public synonym MGMT$WEBLOGIC_NODEMANAGER;
drop public synonym MGMT$WEBLOGIC_OPSSSYSPROP;
drop public synonym MGMT$WEBLOGIC_RACONFIG;
drop public synonym MGMT$WEBLOGIC_RAOUTBOUNDCONFIG;
drop public synonym MGMT$WEBLOGIC_RESOURCECONFIG;
drop public synonym MGMT$WEBLOGIC_STARTSHUTCLASSES;
drop public synonym MGMT$WEBLOGIC_VIRTUALHOST;
drop public synonym MGMT$WEBLOGIC_WEBAPPCOMPONENT;
drop public synonym MGMT$WEBLOGIC_WORKMANAGER;
drop public synonym MGMT$WEBLOGIC_WSCONFIG;
drop public synonym MGMT$WEBLOGIC_WSPORTCONFIG;
drop public synonym MGMT$CS_HA_INFO;
drop public synonym MGMT$CS_RULE_EVAL_SUMMARY;
drop public synonym MGMT$CS_TARGET_ASSOC;
drop public synonym MGMT$DB_ASSOC_DATA;
drop public synonym MGMT$DB_AUTOTASK_CLIENT;
drop public synonym MGMT$DB_CPU_USAGE;
drop public synonym MGMT$DB_FEATURE_USAGE;
drop public synonym MGMT$DB_HA_BACKUP_REPORT;
drop public synonym MGMT$DB_HA_CONFIG;
drop public synonym MGMT$DB_HIGHWATERMARK;
drop public synonym MGMT$DB_INST_ONLY_INFO;
drop public synonym MGMT$DB_INVOBJ_CNT;
drop public synonym MGMT$DB_SCHEDULER_JOBS;
drop public synonym MGMT$DB_SERVICES_ECM;
drop public synonym MGMT$DB_USERS;
drop public synonym MGMT$DIAGNOSTIC_CHECK_ERRORS;
drop public synonym MGMT$DIAGNOSTIC_CHECK_ROWS;
drop public synonym MGMT$ESM_COLLECTION_LATEST;
drop public synonym MGMT$ESM_FILE_SYSTEM_LATEST;
drop public synonym MGMT$ESM_PORTS_LATEST;
drop public synonym MGMT$ESM_SERVICE_LATEST;
drop public synonym MGMT$ESM_STACK_LATEST;
drop public synonym MGMT$HA_DG_TARGET_SUMMARY;
drop public synonym MGMT$HW_CPU_DETAILS;
drop public synonym MGMT$HW_IO_DEVICES;
drop public synonym MGMT$HW_NIC_BONDS;
drop public synonym MGMT$J2EEAPP_EJBCOMPONENT;
drop public synonym MGMT$J2EEAPP_JRFWS;
drop public synonym MGMT$J2EEAPP_JRFWSOPER;
drop public synonym MGMT$J2EEAPP_JRFWSPOLICY;
drop public synonym MGMT$J2EEAPP_JRFWSPORT;
drop public synonym MGMT$J2EEAPP_WEBAPPCOMPONENT;
drop public synonym MGMT$J2EEAPP_WSCONFIG;
drop public synonym MGMT$J2EEAPP_WSPORTCONFIG;
drop public synonym MGMT$J2EE_APPLICATION;
drop public synonym MGMT$OH_CLONE_PROPERTIES;
drop public synonym MGMT$OH_COMPONENT;
drop public synonym MGMT$OH_COMP_DEP_RULE;
drop public synonym MGMT$OH_COMP_INST_TYPE;
drop public synonym MGMT$OH_CRS_NODES;
drop public synonym MGMT$OH_DEP_HOMES;
drop public synonym MGMT$OH_FILE;
drop public synonym MGMT$OH_HOME_INFO;
drop public synonym MGMT$OH_INSTALLED_TARGETS;
drop public synonym MGMT$OH_INV_SUMMARY;
drop public synonym MGMT$OH_PATCH;
drop public synonym MGMT$OH_PATCHED_COMPONENT;
drop public synonym MGMT$OH_PATCHED_FILE;
drop public synonym MGMT$OH_PATCHSET;
drop public synonym MGMT$OH_PATCH_FIXED_BUG;
drop public synonym MGMT$OH_VERSIONED_PATCH;
drop public synonym MGMT$OSB_ADMIN_CLIENT_DB;
drop public synonym MGMT$OS_INIT_SERVICES;
drop public synonym MGMT$OS_LIMITS;
drop public synonym MGMT$OS_MODULES;
drop public synonym MGMT$OS_PATCH_SUMMARY;
drop public synonym MGMT$PA_FINAL_PATCH_LIST;
drop public synonym MGMT$PA_TARGET_MAPPING;
drop public synonym MGMT$RAC_MEMBERSHIPS;
drop public synonym MGMT$RECOM_METRIC_SOURCE;
drop public synonym MGMT$SERVICETAG_INSTANCES;
drop public synonym MGMT$SERVICETAG_REGISTRY;
drop public synonym MGMT$STATSPACK_CONFIG;
drop public synonym MGMT$SWLIB_UPG_REPORT;
drop public synonym MGMT$UNPATCHABLE_DB_TARGETS;
drop public synonym MGMT$VIEW_MT_TC_REPORT;

drop tablespace mgmt_tablespace including contents and datafiles cascade constraints;
drop tablespace mgmt_ecm_depot_ts including contents and datafiles cascade constraints;
drop tablespace mgmt_ad4j_ts including contents and datafiles cascade constraints;

How Many Times Will you Pivot?

Bradley Brown - Sun, 2012-04-08 10:32
When you start a business, you better have a clear vision in mind.  It's critical to get your elevator pitch down.  You need to be able to explain to others, exactly what you're business is all about in 30 seconds.

When we started TUSC, I would to people that we were in the business of building custom software for organizations...and the pitch went on for 2-5 minutes.  One day I was sitting in a board meeting with a group of CEOs.  One of my competitors was in the meeting that day.  One of my CEO friends, who was a banker, said to my competitor - "so what do you guys do?"  It was time for his elevator pitch.  He said "the same thing as Brad, but for Microsoft."  The banker looked my competitor very confused and said, "if you can explain to me what Brad's business does, that would be great, I've been trying to figure that out for 2 years now."  That's not a good sign!  In fact, that was a HUGE wake-up call for me.  Clearly we did OK at explaining what we did to our customers, who were technical people, but CEOs couldn't figure out what we did.  So your elevator pitch has to be relevant to those you give it to.  My CEO elevator pitch then was refined to "we do Oracle, anything with regards to Oracle software, so if you know anyone who has Oracle - please have them contact me."

Our original vision was to be the best consultants in the world around the Oracle software.  To us that meant we were going to build software from the ground up.  We wanted large projects that would allow us to do things right.  However, the market was in need of consultants would could tune their Oracle applications and make them run faster.  That's not what we had in mind.  We didn't want to be firefighters.  But we listened to the market.  We didn't get stuck on "no, our vision was to do big projects, now give us a big project or we're walking..."  Those little projects became our foot in the door...that later (about 5 years later) resulted in us landing large, from the ground up projects.  Which was about the same time that developing custom software was no longer in vogue.  So again, we had to pivot and listen to our customers.  We build an Oracle Apps (ERP) business.

It's not just about listening to customers, but listening to those who can help you.  There was a recent Shark Tank where the guy had wine and a great glass (individual serving of wine for sporting events, weddings, etc) to store wine in.  The sharks were only interested in the glass.  They didn't seem to like his wine.  They tried to explain that the market is WAY bigger than his wine.  But...he didn't want to sell that off because he wanted to sell more of his wine with the glass.  I get it - he came up with the concept so he could sell more wine...but...what does it matter who's wine it is?

As I always say, you can stick to your vision and end up with 100% of nothing or you partner and listen to people and end up with a whole lot more!  At the same time, you don't want to change your direction every week or for that matter very often.  You can't turn a large ship very quickly...you need to make minor adjustments.  Businesses that pivot are generally the most successful business, but the optimal pivots is about 2.  So come up with a vision, run it by others, listen, refine your vision and then go try to sell your visions to customers...and LISTEN!  What is it they are asking for?

ODI 11g – How to override SQL at runtime?

Antonio Romero - Fri, 2012-04-06 11:31

Following on from the posting some time back entitled ‘ODI 11g – Simple, Powerful, Flexible’ here we push the envelope even further. Rather than just having the SQL we override defined statically in the interface design we will have it configurable via a variable….at runtime.

Imagine you have a well defined interface shape that you want to be fulfilled and that shape can be satisfied from a number of different sources that is what this allows - or the ability for one interface to consume data from many different places using variables. The cool thing about ODI’s reference API and this is that it can be fantastically flexible and useful.

When I use the variable as the option value, and I execute the top level scenario that uses this temporary interface I get prompted (or can get prompted to be correct) for the value of the variable.

Note I am using the <@=odiRef.getObjectName("L","EMP", "SCOTT","D")@> notation for the table reference, since this is done at runtime, then the context will resolve to the correct table name etc.

Each time I execute, I could use a different source provider (obviously some dependencies on KMs/technologies here). For example, the following groovy snippet first executes and the query uses SCOTT model with EMP, the next time it is from BOB model and the datastore OTHERS.

m=new Properties();
m.put("DEMO.SQLSTR", "select empno, deptno from <@=odiRef.getObjectName("L","EMP", "SCOTT","D")@>");
s=new StartupParams(m);
runtimeAgent.startScenario("TOP", null, s, null, "GLOBAL", 5, null, true);

m2=new Properties();
m2.put("DEMO.SQLSTR", "select empno, deptno from <@=odiRef.getObjectName("L","OTHERS", "BOB","D")@>");
s2=new StartupParams(m);
runtimeAgent.startScenario("TOP", null, s2, null, "GLOBAL", 5, null, true);

You’ll need a patch to 11.1.1.6 for this type of capability, thanks to my ole buddy Ron Gonzalez from the Enterprise Management group for help pushing the envelope!

OWB – How to update OWB after Database Cloning

Antonio Romero - Wed, 2012-04-04 05:10

One of the most commonly asked questions led to one of the most commonly accessed support documents (strange that) for OWB is the document describing how to update the OWB repository details after cloning the Oracle database. The document in the Oracle support site has id 434272.1, and is titled 'How To Update Warehouse Builder After A Database Cloning (Doc ID 434272.1)'. This post is really for me to remember the document id;-)

Excel and OLAP: ODBC vs. MDX

Keith Laker - Tue, 2012-04-03 11:42
A question that I often get is "what is the difference between using ODBC and the MDX Provider for Oracle OLAP (from Simba Technologies) to query Oracle cubes"? Given that the Oracle cube is easily queried with SQL, it's a reasonable question.
The answer really boils down to leveraging meta data and automatic query generation.
With ODBC, it's up to the Excel user to write a SQL query to fetch data from the cube. Data can be returned in tabular format or a pivot table. When the data is viewed in a pivot table Excel will aggregate data, sometimes with unexpected results. For example Excel might choose to aggregate a measure such as Sales with COUNT or might try to SUM a measure such as Sales YTD Percent Change. Neither make any sense. It's up to the user to get it right.
With the MDX Provider, Excel understands what all the columns mean. It understands dimensions, hierarchies and levels. It's understand the difference between a key and a label. It knows what a measure is. It allows the server to calculate the data. Query generation is automatic. Business users just choose hierarchies and measures and the MDX Provider does the rest.
Here's a list of some of the advantages of using the MDX Provider for Oracle OLAP as compared to using ODBC and writing your own SQL.


Categories: BI & Warehousing

Finally!

Nuno Souto - Tue, 2012-04-03 06:16
Regular readers of this irregular blog will recall it's a loooong time since I had anything positive to say about Oracle and its marketing and support organization.Mind you: it's not a problem with the folks that man the fort at MOS - or whatever the blessed thing is called this week!In general I've found them competent and helpful and have actually recommended quite a few for service awards. Noonshttp://www.blogger.com/profile/04285930853937157148noreply@blogger.com0

Oracle OLAP Exadata Performance Demonstration

Keith Laker - Mon, 2012-04-02 08:39
For a great paper on Oracle OLAP running on Exadata, see:

http://www.oracle.com/technetwork/database/options/olap/olap-exadata-x2-2-performance-1429042.pdf

The Executive Overview section of this paper provides an introduction:

This paper describes a performance demonstration of the OLAP Option to the Oracle Database running on an X2-2 Exadata Database Machine half rack. It shows how Oracle OLAP cubes can be used to enhance the performance and analytic content of the data warehouse and business intelligence solutions, supporting a demanding user community with ultrafast query and rich analytic content.

The demonstration represents users of a business intelligence application using SQL to query an Oracle OLAP cube that has been enhanced with a variety of analytic measures. The cube contains data loaded from a fact table with more than 1 billion rows.

Utilizing Exadata features such as Smart Flash Cache, Oracle Database supported a community of 50 concurrent users querying the cube with queries that are typical of those executed from a business intelligence tool such as Oracle Business Intelligence Enterprise Edition.

With each user querying the database non-stop (without waits between queries) with median query times ranged from .03 to .58 seconds, average query times ranged from .26 to 2.32 seconds, and 95 percent of queries returned in 1.5 to 5.5 seconds, depending on the type of query.

Query performance can be attributed to highly optimized data types and Exadata Smart Flash Cache. Cubes are designed for fast access to random data points, using features such as array-based storage, cost-based aggregation, and joined cube scans. Exadata Smart Flash Cache contributes significantly to cube query performance, virtually eliminating IO wait for the high volume, random IO typically seen with cube queries.
Categories: BI & Warehousing

The String Puzzle

Cary Millsap - Thu, 2012-03-29 12:29
I gave my two boys an old puzzle to solve yesterday. I told them that I’d give them each $10 if they could solve it for me. It’s one of the ways we do the “allowance” thing around the house sometimes.

Here’s the puzzle. A piece of string is stretched tightly around the Earth along its equator. Imagine that this string along the equator forms a perfect circle, and imagine that to reach around that perfect circle, the string has to be exactly 25,000 miles long. Now imagine that you wanted to suspend this string 4 inches above the surface of the Earth, all the way around it. How much longer would the string have to be do do this?


Before you read any further, guess the answer. How much longer would the string have to be? A few inches? Several miles? What do you think?

Now, my older son Alex was more interested in the problem than I thought he would be. He knows the formula for computing the circumference of a circle as a function of its diameter, and he knew that raising the string 4 inches above the surface constituted a diameter change. So the kernel of a solution had begun to formulate in his head. And he had a calculator handy, which he loves to use.

We were at Chipotle for dinner. The rest of the family went in to order, and Alex waited in the truck to solve the problem “where he could have some peace and quiet.” He came into the restaurant in time to order, and he gave me a number that he had cooked up on his calculator in the truck. I had no idea whether it was correct or not (I haven’t worked the problem in many years), so I told him to explain to me how he got it.

When he explained to me what he had done, he pretty quickly discovered that he had made a unit conversion error. He had manipulated the ‘25,000’ and the ‘4’ as if they had been expressed in the same units, so his answer was wrong, but it sounded like conceptually he got what he needed to do to solve the problem. So I had him write it down. On a napkin, of course:


The first thing he did was draw a sphere (top center) and tell me that the diameter of this sphere is 25,000 miles divided by 3.14 (the approximation of π that they use at school). He started dividing that out on his calculator when I pulled the “Whoa, wait” thing where I asked him why he was dividing those two quantities, which caused him, grudgingly, to write out that C = 25,000 mi, that C = πd, and that therefore d = C/π. So I let him figure out that d ≈ 7,961 mi. There’s loss of precision there, because of the 3.14 approximation, and because there are lots of digits to the right of the decimal point after ‘7961’, but more about that later.

I told him to call the length of the original string C (for circumference) and to call the 4-inch suspension distance of the string h (for height), and then write me the formula for the length of the 4-inch high string, without worrying about any unit conversion issues. He got the formula pretty close on the first shot. He added 4 inches to the diameter of the circle instead of adding 4 inches to the radius (you can see the ‘4’ scratched out and replaced with an ‘8’ in the “8 in/63360 in” expression in the middle of the napkin. Where did the ‘63360’ come from, I asked? He explained that this is the number of inches in a mile (5,280 × 12). Good.

But I asked him to hold off on the unit conversion stuff until the very end. He wrote the correct formula for the length of the new string, which is [(C/π) + 2h]·π (bottom left). Then I let him run the formula out on his calculator. It came out to something bigger than exactly 25,000; I didn’t even look at what he got. This number he had produced minus 25,000 would be the answer we were looking for, but I knew there would be at least two problems with getting the answer this way:
  • The value of π is approximately 3.14, but it’s not exactly 3.14.
  • Whenever he had to transfer a precise number from one calculation to the next, I knew Alex was either rounding or truncating liberally.
So, I told him we were going to work this problem out completely symbolically, and only plug the numbers in at the very end. It turns out that doing the problem this way yields a very nice little surprise.

Here’s my half of the napkin:


I called the new string length cʹ and the old string length c. The answer to the puzzle is the value of cʹ − c.

The new circumference cʹ will be π times the new diameter, which is c/π + 2h, as Alex figured out. The second step distributes the π factor through the addition, resulting in cʹ − c = πc/π + 2πh − c. The πc/π term simplifies to just c, and it’s the final step where the magic happens: cʹ − c = c + 2πhc reduces simply to cʹ − c = 2πh. The difference between the new string length and the old one is 2πh, which in our case (where h = 4 inches) is roughly 25.133 inches.

So, problem solved. The string will have to be about 25.133 inches longer if we want to suspend it 4 inches above the surface.

Notice how simple the solution is: the only error we have to worry about is how precisely we want to represent π in our calculation.

Here’s the even cooler part, though: there is no ‘c’ in the formula for the answer. Did you notice that? What does that mean?

It means that the original circumference doesn’t matter. It means that if we have a string around the Moon that we want to raise 4 inches off the surface, we just need another 25.133 inches. How about a string stretched around Jupiter? just 25.133 more inches. Betelgeuse, a star whose diameter is about the same size as Jupiter’s orbit? Just 25.133 more inches. The whole solar system? Just 25.133 more inches. The entire Milky Way galaxy? Just 25.133 more inches. A golf ball? Again, 25.133 more inches. A single electron? Still 25.133 inches.

This is the kind of insight that solving a problem symbolically provides. A numerical solution tends to answer a question and halt the conversation. A symbolic formula answers our question and invites us to ask more.

The calculator answer is just a fish (pardon the analogy, but a potentially tainted fish at that). The symbolic answer is a fishing pole with a stock pond.

So, did I pay Alex for his answer? No. Giving two or three different answers doesn’t close the deal, even if one of the answers is correct. He doesn’t get paid for blurting out possible answers. He doesn’t even get paid for answering the question correctly; he gets paid for convincing me that he has created a correct answer. In the professional world, that is the key: the convincing.

Imagine that a consultant or a salesman told you that you needed to execute a $250,000 procedure to make your computer application run faster. Would you do it? Under what circumstances? If you just trusted him and did it, but it didn’t do what you had hoped, would you ever trust him again? I would argue that you shouldn’t trust an answer without a compelling rationale, and that the recommender’s reputation alone is not a compelling rationale.

The deal is, whenever Alex can show me the right answer and convince me that he’s done the problem correctly, that’s when I’ll give him the $10. I’m guessing it’ll happen within the next three days or so. The interesting bet is going to be whether his little brother beats him to it.

GC Tuning Tips for your FMW

Ramkumar Menon - Thu, 2012-03-29 12:24
Rupesh is one of our in-house JVM Experts and has a valuable blog-entry on GC tuning here.

GC Tuning Tips for your FMW

Ramkumar Menon - Thu, 2012-03-29 12:24
Rupesh is one of our in-house JVM Experts and has a valuable blog-entry on GC tuning here.

Mastering Oracle Trace Data with Cary Millsap

Rob van Wijk - Wed, 2012-03-28 15:42
At CIBER we are very proud to announce that Cary Millsap will give his one day seminar called Mastering Oracle Trace Data in the Netherlands. The event will take place at the Carlton President Hotel at Utrecht on Wednesday, May 23. You can register and read more about this event here.The seminar is aimed at DBA's, database application developers, data warehouse specialists and anyone caring aboutRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2

Pages

Subscribe to Oracle FAQ aggregator