FROM ORACLE TO UNIX SHELL ENVIRONMENT

HOW TO PASS A VALUE FROM ORACLE TO UNIX SHELL ENVIRONMENT VARIABLE


Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.

Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0
{{SELECT STMT}};
exit
EOF`

For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" <<>
set echo off verify off feed off termout off pages 0

> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8>
from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes>
from dba_data_files> group by tablespace_name) tot,> dba_tablespaces tbs>
where tot.tablespace_name = tbs.tablespace_name>
and fre.tablespace_name(+) = tbs.tablespace_name>
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes> /> exit> EOF`

-bash-3.00$ echo $DB_USED4189795.79This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.



HOW TO RENAMED MATERIALIZED VIEW? ORA-32318


Now - this is what you call a long break .. almost a year.

Well - The personal life has been rocking all during last year and I have good news to share, I'm a proud father of a cute little girl.

We are still searching a name for her.

That is some relief in Hindu religion that we can take up to some days before finalizing the name of baby.

I hope to be more punctual in writing my experiences here on the blog.

Today, I have something on materialized views.

How to rename them?Last week, I had this requirement to rename a materialized view and initial search on how to the same landed me on to ORA32318.

ORA-32318: cannot rename a materialized view

Cause: Renaming a materialized view or its base table is not supported.

Action: Do not rename the base table of a materialized view.

Now, this is what I dont expect Oracle to do.

when rename of table is possible, then why not rename of mat view possible?

If any one knows a logical reason, please share.Anyways, I gave it a further thought on how to accomplish this and an idea struck me.I'm giving the information of our system but with changed names.DB1 - where base table exists - 10.2.0.2DB2 - where mview exists - 10.2.0.2T1 - name of the table on DB1M1 - name of the mview - Fast/Incremental refresh on demandNow as discussed the requirement is to convert M1 to M2, with least down-time (at least for selects).Here is some test setup scripts:On DB1:

SQL> create table t1 as select * from all_objects;Table created.

SQL> alter table t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.On DB2:

SQL> create materialized view m1 refresh fast on demand as select * from t1@db1;

- db1 is database linkMaterialized view created.

SQL> select count(*) from m1;

COUNT(*)----------62551Now steps to change the name:

Step1: On DB1- Stop DML/DDL activities on the base table T1Step2: On DB2- Refresh the M1 mview to ensure that there are no changes in there, so we can plan to change the name.

- Create another table with new name. This new name should be the name of mview intended.

SQL> create table m2 as select * from m1;

Table created.>> Well this can help you in moving non-partition base-table/mview to partitioned base-table/mview

Step3: On DB2Create new materialized view using "prebuilt" option.

SQL> create materialized view m2 ON PREBUILT TABLE REFRESH fast on demand as select * from T1@DB1;

Materialized view created.

Step4: On DB2Drop the old materialized view.

SQL> drop materialized view m1;

Materialized view dropped.

Thats it!Now to check if thats working.

Delete few rows on t1@db1

SQL> delete from t1 where object_id in (2,3);

2 rows deleted.

SQL> commit;

Commit complete.

>> And now refresh the new mview.

SQL> exec dbms_mview.refresh('M2')

PL/SQL procedure successfully completed.

>> Check the no of rows:SQL> select count(*) from m2;COUNT(*)----------62549Finally check the way oracle refreshed this mview.

SQL> select LAST_REFRESH_TYPE from user_mviews where MVIEW_NAME='M2';

LAST_REF
------------
FAST

Well that's just a confirmation :)

Note: I havent tested other functionalities of this test.

So please do a thorough testing of this code before pushing into production database system.BTW - we are doing the same.