Oracle Database Miscellanous

By Sandeep Desai (http://www.thedesai.net)

email: s_desai@hotmail.com

Extract DDL

SELECT DBMS_METADATA.GET_DDL('VIEW','DBA_TABLES') FROM dual;

DBMS_METADATA.GET_DEPENDENT_DDL will generate grant etc

Dynamic tables

v$controlfile	name, status
v$controlfile_record_section
v$database	dbid, name, controlfile_change#	General info about database also control files
v$datafile	name, bytes	files created by the database, see v$logfile also
v$fixed_table	name	points to table containing performance statistic
v$log	group#, thread#, sequence#, bytes	Information about online redo log files
v$logfile	member, type	Information about online redo log files. Member has log file name
v$open_cursor	sid, user_name, sql_text	list of SQL that has been run, alter system flush shared_pool; will clear all closed SQL queries
v$option	parameter, value	Which Database options are installed
v$parameter v$system_parameter	name, value, isses_modifiable, issys_modifiable, ismodified, isdefault	init.ora parameters. issys_modifiable (alter system) isses_modifiable (alter session) alter system deferred
v$process	program, pid
v$pwfile_users	username, sysdba, sysoper	users who have sysdba/sysoper privilege
v$session	username, command, status, machine, sid, serial#	alter system kill session <sid, serial#>
v$sessstat	sid, statistic#, value	statistics for session
v$sga	name, value
v$sqlarea	sql_text	lots of info on sql being executed
v$sysstat	name, class, value, statistic#	various system settings e.g name='opened cursors current'
v$version	banner	Version of components in the database e.g Server, PL/SQL Core etc

Database settings

alter database set time_zone='US/Central'; shutdown; startup

alter database backup controlfile to 'bak/c1.ctl'

alter system kill session '7,421' – sid, serial_number

alter system set db_create_file_dest='/oh/oradata/db1'

alter system switch logfile -- manually switch log file

alter system checkpoint

alter database add logfile group 3 '/oh/oracle/dbs/t_log3a.f' -- create new redo log group

alter database add logfile member '/oh/oracle/dbs/t_log3b.f' to group 3 -- add file to redo log group

init.ora parameters for Oracle (init<sid>.ora)

Oracle Enterprise Manager 9.x. Click on Instance Node has good explanation of each init.ora paramter

Located in <ORACLE_HOME>/dbs on UNIX and <ORACLE_HOME>\database on Windows

View parameters and values

· init.ora file (does not show all the parameters)

· v$parameter table

· Instance node in OEM 9i Click on All Initialization Parameters

· Using show parameter command in SQL*Plus

To change parameter value

· edit init.ora (requires database shutdown/startup)

· alter system set (e.g alter system set db_create_file_dest='/oh/oradata/db1')

· alter system deferred

· alter session set

To turn on SQL tracing

user_dump_dest=<path to strore trace files>

sql_trace=true

timed_statistics=true

No way to find out list of truly open cursors only to find out list of cached cursrors

_close_cached_open_cursors	TRUE	Will close all open cursors on every commit and rollback

background_dump_dest	?/rdbms/log	directory to dump trace files in typically	Diagnostics

db_block_size	8192	block size in bytes for data files	Cache and I/O
db_create_file_dest	/oh/db	OMF (Oracle Managed Files) directory where Oracle should create and manage data files. Omit directory while creating tablespace	File Configuration
db_create_online_log_<n>		(OMF) replace n with number, points to default directory for online redo log directory. Set two for improved performance and fault tolerance	File Configuration
log_checkpoint_interval	0..n	number of OS blocks that must be written before checkpoint occurs	Redo log and Recovery
log_checkpoint_timeout	1800	seconds to wait before checkpoint occurs	Redo log and Recovery
open_cursors	50

remote_login_passwordfile	none\|exclusive\|shared	Default none. Configure OS authentication database startup shutdown down cannot be done remotely, set to exclusive or shared to allow users other than sys to connect as sysdba remotely. Then run orapwd utility	Security and Auditing
session_cached_cursors
sql_trace	false	Also set user_dump_dest and timed_statistics	Diagnostics and Statistics
timed_statistics	true	Put time statistics for SQL trace in log file (sql_trace)	Diagnostics and Statistics
user_dump_dest	?/rdbms/log	directory to dump SQL trace log files in file created per session	Diagnostics and Statistics

Limitations

A table partition can be as large as the tablespace

A partitioned table can span multiple tablespaces

Database size	no limit, limited by disk space and tablespace, in theory 1024 * 64 terabyte
Database name	8 characters
Database objects	30 characters
Database object names	'$'.'_','#' and alphanumeric characters can be used for object names
Columns in a table	1000
Tablespace	64,000 per database
datafiles in tablspace	1023, varies by Operating System each datafiles max 64GB with 16K block size
Table/Partition	So largest table/partition can be 64GB * 1022 (approx 64 terabyte)
Database link	128 characters

Oracle Misc

To preinitialize Oracle JVM

select dbms_java.longname('foo') from dual;

Oracle 9i Installation

Enterprise Manager 9.2 website is on port 3339

Apache port is 7778

Apache SSL port is 443

Default database port is 1521

Default ORACLE sid is ORCL

e.g JDBC Url, note username/password is optional

Thin JDBC jdbc:oracle:thin:scott/tiger@localhost:1521:ORCL
Thick JDBC jdbc:oracle:oci8:scott/tiger@orcl
Java in Database connection jdbc:oracle:kprb

iSQLPlus is http://localhost:7778/isqlplus

Set ORACLE_HOME to start Apache from <OH>/Apache/Apache/bin/startJServ.sh for UNIX and <OH>\Apache\Apache\bin\startJSv

Enterprise Manager 8.x and 9.x

<OH>/bin/oemapp console (start EM Console, can be started standalone or OMS (Oracle Management Server mode)

<OH>/bin/emca (Will create EM repository)

<OH>/bin/oemctl start oms (Will start Oracle Management Server used for creating jobs/events etc)

<OH>/bin/oemctl stop oms

default OMS userid/password is sysman/oem_temp

Install 9.2.0.3 Database Patch

· shutdown database

· Download patch

· Start Oracle Universal Installer and install patch

· cd $ORACLE_HOME

· sqlplus sys as sysdba

o startup migrate

o @rdbms/admin/catpatch.sql

o shutdown

o startup

Install 9.2.0.3.1 OLAP Patch

· Install 9.2.0.3 Database Patch

· cd $ORACLE_HOME/olap/admin

· sqlplus sys as sysdba

o @olapu2877945.sql

· cd $ORACLE_HOME/cwmlite/admin

· sqlplus olapsys/manager

o @postamd.sql

Oracle 10g installation

http://localhost:5620/ultrasearch

http://localhost:5620/ultrasearch/admin

http://localhost:5560/isqlplus

http://localhost:5500/em

<ORACLE_HOME>/bin/emctl start dbconsole // starts EM Web Service

Oracle Apache Installation

Oracle installs Apache Server under <OH>/Apache with support for iSQL*Plus and Enterprise Manager

Default port is 7778,

Configuration files

· <OH>/Apache/Apache/conf/httpd.conf (has web listener port)

· <OH>/Apache/Apache/conf/oracle_apache.conf (includes conf files for all apps installed for Apache e.g EM)

Oracle Misc

Object oriented

·         Nested table

· Varray

· Large Objects (BLOB, CLOB, NCLOB, BFILE)

· References (REFs)

Create type ADDRESS_TY as object (Street varchar2(50), city varchar2(25), state char(2), zip number)

Create type PERSON_TY as object (Name varchar2(50), Address ADDRESS_TY)

Create table CUSTOMER (Customer_ID Number, Person PERSON_TY)

insert into customer values (1, PERSON_TY('LARRY', ADDRESS_TY('1 RG DR', 'WOB', 'MA', 111))

select customer_id, c.person.name from customer c

update customer c set c.person_ty.name = 'BILL'

Two phase commit is a protocol for updating distributed databases, it consists of 2 phases a prepare phase and a commit phase

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Scripts

script to set database to japanese character set

SET ECHO ON
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP PFILE=t_init1.ora MOUNT
ALTER SYSTEM ENABLE RESTRICTED SESSION ;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 ;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 ;
ALTER DATABASE OPEN ;
ALTER DATABASE CHARACTER SET INTERNAL_USE JA16SJIS ;
SELECT VALUE FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER='NLS_CHARACTERSET' ;
SELECT VALUE FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER='NLS_NCHAR_CHARACTERSET' ;
SHUTDOWN IMMEDIATE
STARTUP PFILE=t_init1.ora
EXIT

load table from a flat file

SQL> drop table xt;
Table dropped.
SQL> create table xt (c1 varchar2(10),c2 varchar2(10),c3 varchar2(10),c4 varchar2(10),c5 varchar2(10))
            organization external (type oracle_loader default directory def_dir1 access parameters (fields terminated by '\t') location ('a.dat')

Table created.
SQL> select * from xt where c4=' ';
C1         C2         C3         C4         C5
---------- ---------- ---------- ----------- ----------
col1       col2       col3                  col5
SQL> !od -xc /tmp/a.dat
0000000    636f    6c31    0963    6f6c    3209    636f    6c33    0920
           c   o   l   1 \t   c   o   l   2 \t   c   o   l   3 \t
0000020    0963    6f6c    350a
          \t   c   o   l   5 \n
0000026

How to find 90^th percentile in dataset

Have you looked at the PERCENTILE_DESC and PERCENTILE_CONT functions?

Yours truly, should be able to use something like:
select b.tdr_data_desc ,
percentile_disc(0.9) within group (order by tdr_data_value)
from tdr_trx_data a , tdr_data_description b
where tdr_test_id =1 and
a.tdr_data_desc_id = b.tdr_data_desc_id
group by (b.tdr_data_desc)
/

Data Warehousing

Star schema: Consists of one or more fact table related to one or more dimension tables. The relationship is defined through foreign keys, metadata or both.

e.g SALES TABLE (units as number, cost as number, city as varchar, product as varchar) would be a fact table. Dimension table would be Geography (country as varchar, state as varchar, city as varchar)

SALES TABLE

UNITS	COST	CITY	PRODUCT
10	11	BOSTON	VCR
13	12	TORONTO	SPEAKERS

GEOGRAPHY

COUNTRY	STATE	CITY
USA	MASSACHUSETTS	BOSTON
CANADA	ONTARIO	TORONTO

Snowflake schema: A star schema that has been fully normalized to reduce the number of duplicate values in the dimension table. A snowflake schema requires more joins hence reducing performance.

e.g In the example above the Geography table would be split to two tables one with city and state and the other with state and country

Grouping ID (GID): Used for figuring out how to group data in a table e.g

USA 1

INDIA 1

NEVADA 2

MAINE 2

PORTLAND 3

LAS VEGA 3

Snow Flake: In a star schema the hierarchy for a dimension is one table, whereas in a snow flake the hierarchy for a dimension is one level per table (e.g city, state in one table, state, country in another table)

Oracle PL/SQL

-- This is a comment

/* This is also a comment */

dbms_utility.port_string returns platform string

select sys_guid() from dual; returns a globaly unique sequence number

dbms_output.put_line(userenv('sessionid'));

CREATE OR REPLACE FUNCTION TEST RETURN VARCHAR2 IS BEGIN RETURN TRUE; END

CREATE TABLE os_child_instances (

host_name VARCHAR2(32),

service_name VARCHAR2(32),

is_default_instance CHAR(1), -- 'Y' or 'N'

express_agent_port NUMBER(5),

olapi_ior varchar2(4000), -- Not sure if required

attribute varchar2(2048) -- For Future use

constraint host_service_uq unique (host_name, service_name, express_agent_port)

);

CREATE TABLE os_osa_privileges (

privilege_name VARCHAR(100) primary key,

id_name VARCHAR2(30),

id_type CHAR(1) -- 'R' is a role and 'U' is userID

);

INSERT INTO os_osa_privileges values ( 'OSA', 'olap_dba', 'R');

-- Will be used by OSIM to get list of child instances

CREATE OR REPLACE VIEW os_all_child_instances_list AS

SELECT host_name, service_name, express_agent_port

FROM os_child_instances WITH READ ONLY;

CREATE OR REPLACE PACKAGE CWM_OLAP_SERVER_LIST AS

PROCEDURE add_olap_child_instance (

p_host_name IN VARCHAR2,

p_service_name IN VARCHAR2,

p_express_agent_port IN NUMBER);

PROCEDURE remove_olap_child_instance (

p_host_name IN VARCHAR2,

p_service_name IN VARCHAR2,

p_express_agent_port IN NUMBER);

END CWM_OLAP_SERVER_LIST;

CREATE OR REPLACE PACKAGE BODY CWM_OLAP_SERVER_LIST AS

-- Will be used by the Express Agent to add a OLAP Server Instance

PROCEDURE add_olap_child_instance (

p_host_name IN VARCHAR2,

p_service_name IN VARCHAR2,

p_express_agent_port IN NUMBER) AS

BEGIN

INSERT INTO os_child_instances

(host_name, service_name, express_agent_port)

values (p_host_name, p_service_name, p_express_agent_port);

END os_add_olap_child_instance;

PROCEDURE remove_olap_child_instance (

p_host_name IN VARCHAR2,

p_service_name IN VARCHAR2,

p_express_agent_port IN NUMBER) AS

BEGIN

DELETE FROM os_child_instances

WHERE host_name = p_host_name AND

service_name = p_service_name AND

express_agent_port = p_express_agent_port;

END os_remove_olap_child_instance;

END CWM_OLAP_SERVER_LIST;

-- Examples below

-- Add an instance

EXECUTE CWM_OLAP_SERVER_LIST.add_olap_child_instance('sbdesai-pc', 'olapserver', 6789);

-- or if the above does not work

insert into os_child_instances (host_name, service_name, express_agent_port) values ('sbdesai-pc', 'olapserver', 6789);

-- remove an instance

EXECUTE CWM_OLAP_SERVER_LIST.remove_olap_child_instance('sbdesai-pc', 'olapserver', 6789);

-- get list of instances

select host_name, service_name, express_agent_port from os_child_instances;

-- add a privilege

insert into os_osa_privileges values('OSA', 'sbdesai', 'U');

-- remove a privilege

delete from os_osa_privileges where privilege_name = 'OSA' and id_name = 'sbdesai' id_type = 'U';

-- get list of privileges

select privilege_name, id_name, id_type from os_osa_privileges;

-- Create a dynamic cursor for any table

PROCEDURE TestCursorForAnyTable(p_vSQLStmt VARCHAR2) IS
vPL_SQLCmd VARCHAR(1990) := '';

    BEGIN
        vPL_SQLCmd := '';
       vPL_SQLCmd := vPL_SQLCmd || 'DECLARE ';
       vPL_SQLCmd := vPL_SQLCmd || '    TYPE RefCurTyp IS REF CURSOR; ';
       vPL_SQLCmd := vPL_SQLCmd || '    cGenericCursor RefCurTyp; ';
       vPL_SQLCmd := vPL_SQLCmd || '    vTextColumn VARCHAR2(30); ';
       vPL_SQLCmd := vPL_SQLCmd || 'BEGIN ';
       vPL_SQLCmd := vPL_SQLCmd || '    OPEN cGenericCursor FOR ''' ||
        p_vSQLStmt || '''; ';
       vPL_SQLCmd := vPL_SQLCmd || '    LOOP ';
       vPL_SQLCmd := vPL_SQLCmd || '        FETCH cGenericCursor INTO vTextColumn; ';
       vPL_SQLCmd := vPL_SQLCmd || '        dbms_output.put_line(vTextColumn); ';
       vPL_SQLCmd := vPL_SQLCmd || '        EXIT WHEN cGenericCursor%NOTFOUND; ';
       vPL_SQLCmd := vPL_SQLCmd || '    END LOOP; ';
       vPL_SQLCmd := vPL_SQLCmd || '    CLOSE cGenericCursor; ';
       vPL_SQLCmd := vPL_SQLCmd || 'END; ';
       EXECUTE IMMEDIATE vPL_SQLCmd;
   END TestCursorForAnyTable;

VPD Example

connect sys as sysdba

Prompt Create a new security administrator

drop user oe_security cascade;

create user oe_security identified by oe_security;
grant create session to oe_security;
grant create user to oe_security;

grant execute on dbms_rls to oe_security;
grant create procedure to oe_security;

Prompt Connect as the application owner and create an application table

connect oe/oe

drop table acme_orders;

create table acme_orders
(order_id number (12),
customer_id number(12),
credit_card varchar2(16));

insert into acme_orders values (10,400,'1234123412341234');
insert into acme_orders values (11,500,'1234123412341234');
insert into acme_orders values (12,430,'1234123412341234');
insert into acme_orders values (13,420,'1234123412341234');
insert into acme_orders values (14,450,'1234123412341234');
insert into acme_orders values (15,460,'1234123412341234');
insert into acme_orders values (16,470,'1234123412341234');
insert into acme_orders values (17,480,'1234123412341234');
insert into acme_orders values (18,490,'1234123412341234');

Prompt Connect as the application security administrator

connect oe_security/oe_security

create or replace function c_policy (schema in varchar2, tab in varchar2)
return varchar2
as
begin

return ('1 = 2');

end;
/

Begin

dbms_rls.add_policy('oe','acme_orders','accesscontrol_orders',
'oe_security','c_policy',sec_relevant_cols=>'CREDIT_CARD',
sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
end;
/

connect oe/oe

show user

select * from acme_orders;

CLOB

CREATE TABLE ctest (

p1 VARCHAR(100),

p2 CLOB,

);

CREATE OR REPLACE FUNCTION PTEST RETURN clob

result clob;

BEGIN

select p2 into result from ctest;

return result;

END;

create or replace function atest return clob

result clob;

begin

result := '';

for i in 1..6000 loop

result := result || 'A';

end loop;

return result;

end;

Loading sales history database for 9.2

Cd $ORACLE_HOME/demo/schema/sales_history

Sqlplus system/manager

@sh_man

password: sh

tablespace: sh

temp tablespace: sh

$ORACLE_HOME/demo/schema/sales_history/

~/log

connect sh/sh

@sh_olp_c

OLAP Option

CWM/OLAP Catalog Analytic Workspace Manager AWM

olapsys/manager owns OLAP Catalog

set serveroutput on

set serverout size 999999

cwm2_olap_metadata_refresh.mr_refresh (will refresh table which contains CWM Metadata views not calling this will result in metadata not found in OLAPI code)

cwm2_olap_validate.validate_olap_catalog (validates the entire OLAP catalog)

execute cwm2_olap_manager.set_echo_on;

execute cwm2_olap_validate.validate_cube(owner, cube_name);

// turn logging on

cwm2_olap_manager.begin_log ('UTL_FILE_DIR','LOGFILE');

cwm2_olap_manager.end_log ();

In ADE $SRCHOME/cwmlite/admin/*.sql scripts create OLAP Catalog

cwm2awmd.sql for DBMS_AWM package

Also in $ORACLE_HOME/cwmlite/admin

DBMS_AWM PL/SQL package will create and Analytic Workspace from an OLAP Catalog Cube

OLAP DML code to Get list of Aggregation Plans created using DBMS_AWM.CREATE_AWCUBEAGG_SPEC() PL/SQL call

aw attach foo

show ___err.text

limit name to obj(property 'AW$ROLE') eq 'AGGDEF'

report w 80 name

' Do a full describe on the results of above command

fulldsc AW1_AGG1

' command below will get list of dimensions to be aggregated for aggplan AW1_AGG1

limit name to obj(property 'aw$role') eq 'AGGDEF_LEVELS' and obj(property 'AW$PARENT_NAME') eq 'AW1_AGG1'

rpr w 80 name

The command below will show levels to be precomputed for dimension listed below

show values(aw1_agg1_aw_channel)

OLAP Option Analytic Workspace (Multidimensional database)

Data is organized along dimensions e.g. a cube where the cube data could be units and the edges would be geography, product and time. A multi dimensional database will compute aggregations as required e.g user can ask how many units were sold in the USA.

Measure: is data in cube also known as facts

Dimensions are edges; dimensions can be hierarchical

Dimension Attributes: e.g end date

Levels: e.g State in geography where geography is heirarchy of country, state and city.

Level Attributes: Long Name for Level

Hierarchies: Defines parent child relations between levels

Cube: Cube consists of one or more measures which are qualified by the same dimension the same e.g units <geog, time, products>

Time Dimesion (special in OLAP Catalog for Oracle 9.2) should have an end-date attribute for each level such as week_enddate, quarter_enddate, these columns must have a DATE data type. A time-span for each level such as week_timespan

OLAP Catalog in 9.2

CWM2_OLAP_PC_TRANSFORM: Create views for dimension tables with parent child relations
CWM2_OLAP_AW_ACCESS: Creates SQL views to AW (creates AW Objects)
CWM2_OLAP_AW_OBJECT: Create OLAP Catalog metadata for AW objects
CWM2_OLAP_TABLE_MAP contains procedures that maps metadata entities to relational fact tables and dimension tables
CWM2_OLAP_AW_MAP contains procedures that map metadata entities to AW objects


all_olap2_aw_cubes	aw_owner, aw_name, aw_logical_name, source_owner, source_name	AW cubes in an AW aw_logical_name is AW cube name
all_olap2_cubes	owner, cube_name, invalid, mv_summary_code	list all relational cubes, mv_summary_code is grouping set or rollup
all_olap2_cube_dim_uses
all_olap2_dim_levels	owner, dimension_name, level_name, display_name, level_table_owner, level_table_name	levels in a dimension








dba_mview_relations

MVs for a cube

select distinct mview_name from dba_mview_detail_relations m, dba_olap_cube_measure_maps c where c.owner = '&owner' and c.cube_name = '&cubeName' and c.fact_table_owner = m.detailobj_owner and c.fact_table_name = m.detailobj_name and m.detailobj_type = 'TABLE'

MVs for a dimension

select distinct mview_name, dimension_name, level_table_name, detailobj_name from dba_mview_detail_relations m, all_olap2_dim_levels c

where c.owner = 'XADEMO' and c.dimension_name = 'CHANNEL' and c.level_table_owner = m.detailobj_owner

and c.level_table_name = m.detailobj_name

and m.detailobj_type = 'TABLE'

Analytic Workspace

Analytic Workspaces are multidimensional database objects inside the Oracle Database Engine. You need to create your database from the Data Warehouse Template using the Database Configuration Assistant. An example of an AW would be a three dimensional cube with Geography, product and time dimension. Geography would consist of a hierarchy of city, state, country, World. Time dimension would have a hierarchy of month, quarter, year. You could then have sales, profits data in a cube. Using AW's you can find out the top 10 cities with the most DVD players sold in a quarter.

You can get data into an AW from a Relational star schema source using first OEM to define a cube and then using Analytic Workspace Manager to create an AW from it. You can the enable the AW for OLAPI and use the BI Beans Analyzer to access the data inside the AW

Object types

· Dimensions

§ Hierarchies (e.g Geography with hierarchy of city, state, country, world)

· Variables (Fact data e.g sales data)

· Relations

· Formulas

· Programs

· Aggregation Maps: precompute aggregation to make queries run faster

· Models

· Valuesets

Query to get the size of the AW in bytes

Select extnum, dbms_lob.getlength(awlob) from AW$workspace

Where workspace is the name of the AW

Get list of AWs in a database

select owner, table_name from all_tables where table_name like 'AW$%';

select * from dba_aws; // needs DBA privileges

select * from user_aws; // queried by anybody

select * from all_aws; // list of all AWs

fixed tables

v$aw_aggregate_op contains list of aggregation operators (supported in 10i)

v$aw_allocate_op contains list of allocation operators (supported in 10i)

Aggregation operators:

· SUM

· WEIGHTED-SUM

· SCALED-SUM

· AND

· OR

· FIRST

· LAST

· HIERARCHICAL-FIRST

· HIERARCHICAL-LAST

· AVERAGE

· WEIGHTED-AVERAGE

· HIERARCHICAL-AVERAGE

· HIERARCHICAL-WEIGHTED-AVERAGE

· MIN

· MAX

· WEIGHTED-FIRST

· WEIGHTED-LAST

· HIERARCHICAL-WEIGHTED-FIRST

· HIERARCHICAL-WEIGHTED-LAST

· WEIGHTED-MIN

· WEIGHTED-MAX

· NONE

OLAP DML Examples for Oracle 9i Rel 2 (SPL)

OLAP DML was known as SPL in Oracle Express

SQL

The following should be done to setup a directory alias to do file commands with AW
sqlplus system/manger
Create or replace directory indir as '/users/sbdesai/inp'
Grant read on directory indir to public // possible permissions are read/write/all
Select dbms_aw.execute('show eversion') from dual;
Select dbms_aw.interp('show eversion') from dual;
Execute dbms_aw.execute('show eversion; show ''hello''');

OLAP DML

Comment start with "
Multiple lines can be separated by a ;
Do an update and commit to save your changes
Case sensitive?
AW Debugging Info will be sent to a trace file

dotf tracefile
prgtrace=yes
badline=yes

Command	Description
Aw list	List analytic workspaces
Aw attach <database>	Open database readonly e.g aw attach test
Aw attach test rw	Open database read/write
Aw detach test	Detach database
Aw create <database>	Create database
consider object	do all new operations on this object
Listnames	List objects in database
property name value	e.g consider a1 property 'apple' 'orange'
Update	Save changes made to analytic workspace

Show 'hello'	Display string
show obj	show values for a variable/dimension/formula
show values(valuesetobj)	show values in a valueset
Define hello program Program Show 'hello' End	SPL Program like PL/SQL stored procedure

Call hello	Run Program (run stored procedure)

"create program that returns value and takes argument define hello2 program text program argument param1 text return param1 end
define city dimension id	Create a city dimension
define _xwd_init variable Boolean	Create variable
Cda <directory alias> e.g. cda indir	Change to directory alias created using SQL create directory command. This command should be done for all file related operations with AW such as infile
Infile 'indir/hello.inp'	Load contents of file into AW
Import all from eif file 'test.eif'	Import eif file into Analytic Workspace
Import all from eif file 'test.eif' list only	List contents of eif file
Export all to eif file 'test.eif'	Export Database to eif file

Limit name to obj(type) eq Boolean	Show all Boolean variables
Limit name to obj(width) gt 0	Show all variable that have width > 0
Report name	Shows output after limit rpr down time w 50 sales_measure
statlen(obj)	statlen(geog) will show member count in dimension
Describe	Describes output after limit

BI Beans 9.0.3

Install Jdeveloper 9.0.3

Install BI Beans from bibeans.us.oracle.com

Create users tablespace

Install BI Beans schema by running

set JDEV_ORACLE_HOME

set JAVA_HOME

<JDEV_HOME>\bibeans\bin\bi_installcatauto localhost 1521 orcl

Copy < JDEV_HOME>\bibeans\bibdemo_schema to the database machine

run bibdemo.bat/bibdemo.sh <directory to create datafiles in>

Oracle Warehouse Builder 9.2

This tools allows you to visually design and deploy a Data Warehouse

· Install OWB

· Run OWB Runtime Repository

· Run OWB Repository to create Design time Repository in schema owbdesrep

· Use the above userid to login to OWB

· Design logical model using OWB Client (login as owbdesrep)

o Create Source Oracle Module

§ Add Source tables

o Create Target Oracle Module

§ Create Dimensions and Cube

§ Create Mappings (map source to targets)

§ Create Process Flow

· Create Runtime Repository in user/schema owbrunrep (aka Access User)

· Connect as owbrunrep for running Deployment Manager

· Create user owb_target (should have select access on source targets)

· Deploy to Target Schema

· Use Metadata Bridge to Deploy to OLAP

Analytic Workspace Manager

awm.properties

application.log_file=d:\10i\awm\demo\tools\zz.log

AWM_LOG.LOG_DIRNAME=D:\10i\AWM\Demo\tools

application.log_level=debug

olap_dml_log.enable=Y

AWM_LOG.LOG_GENERATE=YES

OLAP_DML_LOG.LOG_RESULTS=Y

olap_dml_log.log_filename=awm.log

OLAPI

SYS.OLAPIBOOTSTRAP is a PL/SQL procedure that is used to initialize the
OLAP API. This error indicates that something has gone wrong in the
installation.

The RDBMS must be installed with the OLAP option to use the OLAP API.
Also, configuration parameter "compatible" must be set to 9.2.0.0.0 or
higher.

If these are OK, check the procedure itself. Use sqlplus to connect to
the instance as SYS and execute the following anonymous PL/SQL:

declare
r1 number;
r2 varchar2(64);
begin
r1 := olapibootstrap(r2);
end;
/

OCP questions

The first datafile in the original System tablespace should be atleast 150m

Oracle can handle more datafiles than OS limits. Oracle will automatically close and open files as requires to stay under OS limit

In a temporary tablespace all sort operations share a single sort segment

Rman copies can be restored by OS Utilities and does not necessarily have to be restored by Rman. Rman Rman Backup can only be restored by RMan Backup

Oracle Database Miscellanous

Dynamic tables

Database settings

init.ora parameters for Oracle (init<sid>.ora)

Limitations

Oracle Misc

Oracle 9i Installation

Install 9.2.0.3 Database Patch

Install 9.2.0.3.1 OLAP Patch

Oracle 10g installation

Oracle Apache Installation

Oracle Misc

Object oriented

Scripts

Data Warehousing

SALES TABLE

Oracle PL/SQL

VPD Example

CLOB

Loading sales history database for 9.2

OLAP Option

CWM/OLAP Catalog Analytic Workspace Manager AWM

OLAP Option Analytic Workspace (Multidimensional database)

Analytic Workspace

OLAP DML Examples for Oracle 9i Rel 2 (SPL)

SQL

Command

Description

BI Beans 9.0.3

Oracle Warehouse Builder 9.2

Analytic Workspace Manager

OLAPI

OCP questions