Creating Oracle Table Varray Data Type Columns

Best explained reference to this with easy examples can be found here:

PART I
http://blogs.ittoolbox.com/database/solutions/archives/working-with-varrays-in-oracle-part-i-9111

PART II

http://blogs.ittoolbox.com/database/solutions/archives/working-with-varrays-in-oracle-part-ii-9229

PART III
http://blogs.ittoolbox.com/database/solutions/archives/working-with-varrays-in-oracle-part-iii-9349

I tried creating an attribute in MicroStrategy which uses this column as its Form but it seems MicroStrategy does not support this data type. Even Freeform Sql failed to generate the sql.

Installing a Loopback Adapter on Windows 2000

Dynamic Host Configuration Protocol (DHCP) assigns dynamic IP addresses on a network. Dynamic addressing allows a computer to have a different IP address each time it connects to the network. In some cases, the IP address can change while the computer is still connected.

You can have a mixture of static and dynamic IP addressing in a DHCP system. In a DHCP setup, the software tracks IP addresses, which simplifies network administration. This lets you add a new computer to the network without having to manually assign that computer a unique IP address. However, before installing Oracle Database onto a computer that uses the DHCP protocol, you need to install a loopback adapter to assign a local IP address to that computer.

Loopback adapter approach is recommended particularly for laptops (presumably used only for learning purposes!) which connect and disconnect from the real, corporate network. The loopback adapter means that Oracle will function regardless of whether the laptop is connected to the network or not. It gives Oracle a static, always-there, point of reference independent of what shenannigans the real NIC gets up to.

For instructions regarding the Loopback Adapter on Windows 2000 see the instructions from 2.4.5.3 onwards:

http://download-west.oracle.com/docs/html/B10130_02/reqs.htm#BABDJJFF

Transparent Data Encryption (TDE)

You can find all the basic stuff about Transparent Data Encryption here:
http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html

and here:
http://www.oracle.com/technology/oramag/oracle/05-jan/o15security.html

We needed to issue the statement in MicroStrategy before running the report:
alter system set encryption wallet open authenticated by "password"; so that the encrypted columns would be displayed in the report.

" around password are double quotes.

However if the statement is issued like this then by selecting the Sql view for the report in MicroStrategy, one can easily see the password and so we needed to hide it inside a procedure.

I couldnt find any examples of using the alter statement in a procedure and this failed when executed:

create or replace procedure open_my_wallet is
begin
execute immediate 'alter system set encryption wallet open authenticated by "password"'; end;

with privilege errors.

The reason is that the alter system privilege was given through a role and needs to be granted directly. Alternatively by doing it this way, the privileges are granted directly

--
create or replace procedure open_wallet authid current_user
is
begin
execute immediate 'alter system set encryption wallet open authenticated by "password"';
end open_wallet;
/
--

When a procedure is defined as authid current_user (invoker rights), than all privileges available with granted roles will be available to the procedure

Returning clause with insert does not work!

On the web there is a lot of material about the new 10g feature returning clause with examples.
However it looks like those who have published these examples have not tested the code and just typed it out.
See : http://orafaq.com/node/34

Returning clause works fine with Update and Delete.

Ex:

declare
tot_count number;
begin
delete from pn_1
RETURNING count(a1) INTO tot_count;

dbms_output.put_line(tot_count);
end;

However with insert

table pn_1 is created with two columns s1 varchar2(30), a2 number

declare
tot_count number;
begin

insert into pn_1
select table_name,1
from user_tables
RETURNING count(a1) INTO tot_count;

dbms_output.put_line(tot_count);
end;

it gives syntax error
ORA-06550: line 5, column 16:
PL/SQL: ORA-00933: SQL command not properly ended

See Metalink Note:302910.1

So still the best way to note how many records have been inserted :


declare

begin
insert into pn_1
select table_name,1 b
from user_tables;
--RETURNING sum(b) INTO tot_count;
dbms_output.put_line(sql%rowcount);
exception
when others then
dbms_output.put_line(sqlerrm);
end;

How to Check ORACLE_HOME from the Database

First Method:

select NVL(substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) , substr(file_spec, 1, instr(file_spec, '/', -1, 2) -1)) folder
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB'

2nd Method (Requires priv to run sys.dbms_system) :

DECLARE

folder VARCHAR2(100);

BEGIN

sys.dbms_system.get_env('ORACLE_HOME', folder);
dbms_output.put_line(folder);

END;

Archivelog Mode

Just a note of all the things I forget about Archivelog mode

The easiest way to find out if a database is running in archivelog mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

And for more reference:

Archive log related stuff

dbms_stats

There are so many combinations of parameters, how does one decide the most optimum parameters when using dbms_stats?

One method I found useful is checking the accuracy of num_distinct_rows against time taken for gathering statistics. You want good accurate stats but produced in a short time.

First compile this Function to automatically and accurately calculate the number of distinct rows:

create or replace function pn_distinct(p_table in varchar2, p_col in varchar2)
return number
is
q_text varchar2(4000);
v_records number;

begin

q_text := 'select count(distinct ' p_col ') from 'p_table ;execute immediate(q_text) into v_records;
return v_records;

end;

Run the dbms_stat tweaking with the different parameters e.g. to collect stats on all tables in schema that begin with DWH in the example below;

create or replace procedure dwh_gather_stats
as
cursor c1 is
select table_name
from user_tables
where table_name like 'DWH%';

begin

For c1rec in c1 loop
dbms_stats.gather_table_stats(ownname=>'BI_USER',
tabname=>c1rec.table_name,
estimate_percent=>10,
method_opt=>'for all columns size auto',
cascade=>true);

end loop;

end;

record the time taken with each set of parameters.

and then run the following query and decide on a trade off between accuracy of the stats and time taken to gather the stats by comparing the num_distinct and actual_distinct columns. You can use this method to compare dbms_stats against analyze as well.


select p.table_name,p.last_analyzed,t.COLUMN_NAME,t.NUM_DISTINCT,
pn_distinct(p.table_name,t.column_name) actual_distinct
from user_tab_columns T,
user_tables P
where p.table_name like 'DWH%'
and p.table_name = t.TABLE_NAME