Technotes

Buiness objects checking changes in the CMS for universe

select * from detail_type where detail_type_description like ‘%Univer%’

select count(distinct objectid) from CMS_InfoObjects6

–select * into CMS_TEMP from CMS_InfoObjects6

select prev.* from

CMS_TEMP prev join CMS_InfoObjects6 cr on prev.objectid = cr.objectid

where prev.lastmodifyTime <> cr.lastmodifyTime

select * From CMS_TEMP where typeid=266 

SELECT [TableName] = so.name,[RowCount] = MAX(si.rows)  

FROM sysobjects so,sysindexes si  

WHERE so.xtype = ‘U’

AND si.id = OBJECT_ID(so.name)

GROUP BY so.name

ORDER BY 2 DESC

Add comment February 16th, 2012

How to set universe query limits?

setting universe default limit

default rows 5000

default timeout 10 pages

Add comment February 14th, 2012

OWB how to change prd user password for the target schema?

Here is how to change password in oracle data warehouse prd environment.

OMBCONNECT USER_TGT/password@host_name:1521:service_name

puts “Changing context to the production project”

OMBCC ‘USER’

puts “Connecting to the DEFAULT_CONTROL_CENTER”

OMBCONNECT CONTROL_CENTER

OMBCOMMIT

puts “setting connection details for USER_TGT_LOC location”

OMBALTER LOCATION ‘USER_TGT_LOC’ SET PROPERTIES (PASSWORD)  VALUES (’password’)

OMBCOMMIT

OMBREGISTER LOCATION ‘USER_TGT_LOC’

OMBCOMMIT
OMBDISCONNECT

Add comment February 3rd, 2012

Oracle PL/SQL how to create a db link?

Here is how to create a db link

CREATE DATABASE LINK
servicename@db_LOC_1
CONNECT TO user_name IDENTIFIED BY password
USING ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) 
(HOST=db_host_name)(PORT=db_port)))
(CONNECT_DATA=(SERVICE_NAME=db_service_name)))’;

(run under sqlplus will work while in sql developer won’t) 

Add comment January 27th, 2012

Oracle data warehouse builder how to switch workspace?

Here is how you can switch workspace in oracle data warehouse builder if you have more than one workspace under the same user

exec OWBsys.wb_workspace_management.set_workspace(’WORKSPACE_NAME’);

Add comment January 17th, 2012

checking Oracle warehouse builder audit info in the owb audit table/view

here is how you can check the audit info in the owb audit table/view

this will give you the same info as the one provided by oracle warehouse builder repository browser tools

select * from owbsys.wb_rtv_audit_executions
where return_result like ‘OK_WITH_WARNINGS’

select * from OWBSYS.all_rt_audit_exec_messages
where execution_audit_id in (select audit_execution_id from owbsys.wb_rtv_audit_executions
where return_result like ‘OK_WITH_WARNINGS’ and return_code=2)

Add comment January 17th, 2012

Check Oracle Warehouse builder workspace user view and table

Here is how you can check owb workspace user in the view or table

check in the table owned by owbsys

select *  from CMPFCOClasses tbl
where s2_1 in (
‘CMPWBUser’
) order by s4_1

check in the view

select * from owbsys.WBA_IV_USERS

you have to be under the right workspace and has to log in as the workspace owner
 

Add comment January 17th, 2012

Oracle warehouse builder workspace owner and user errors

When an existing workspace user (owned by other user) create a workspace under its schema, it will mess up the workspace user admin in owb. To fix the issue, you need to drop that workspace new created, i.e, a workspace owner can’t be a user of any workspace otherthan its own workspace, a non-workspace user can be use of any workspace.

Add comment January 17th, 2012

recover a datafile when run out of disk

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> drop table run_stats;
drop table run_stats
*
ERROR at line 1:
ORA-01109: database not open
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                  1250380 bytes
Variable Size             171969460 bytes
Database Buffers          423624704 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF’
SQL> recover datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
ORA-02236: invalid file name
SQL> recover datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF’
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF’
SQL> recover datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF’
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

Add comment January 12th, 2012

Active mq configureation in Oracle SOA suite

config in the amq adapter

 Property Name  Property Type  Property Value 
BrokerXmlConfig java.lang.String 
Clientid java.lang.String 
Password java.lang.String 
ServerUrl java.lang.String tcp://hostname:61616?useInactivityMonitor=false
UseInboundSession java.lang.Boolean false
UserName java.lang.String

config in the JMS Adapter 

Property Name  Property Type  Property Value 
AcknowledgeMode| java.lang.String | AUTO_ACKNOWLEDGE
ConnectionFactoryLocation | java.lang.String | eis/activemq/Connection
FactoryProperties | java.lang.String | BrokerURL=tcp://hostname:61616?useInactivityMonitor=false;ThirdPartyJMSProvider=true
IsTopic |java.lang.Boolean | false
IsTransacted | java.lang.Boolean |false
Password |java.lang.String  |
Username | java.lang.String  |

Add comment January 11th, 2012