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" << EOF
> 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_USED
4189795.79

This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.

How to rename 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 ORA-32318.

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.2
DB2 - where mview exists - 10.2.0.2
T1 - name of the table on DB1
M1 - name of the mview - Fast/Incremental refresh on demand

Now 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 link

Materialized view created.

SQL> select count(*) from m1;

COUNT(*)
----------
62551

Now steps to change the name:

Step1: On DB1
- Stop DML/DDL activities on the base table T1

Step2: 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 DB2

Create 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 DB2

Drop 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(*)
----------
62549

Finally 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.

External Tables - 10g

Until 9i external tables could only be used for read purposes.
But 10g allows you to populate external tables.

here is how it does:

Step 1: - Create a directory

SQL> create directory my_dir as '/usr/test'; --- Make sure Oracle OS user has
privilege to write on this directory.

Step 2: - Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: - Create the External Table: -

SQL> CREATE TABLE scott.external_emp_dept
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
LOCATION ('emp_dept.exp')
)
reject limit unlimited
AS
select e.ename,d.dname from emp e, dept d where
e.deptno=d.deptno;

SQL> select * from scott.external_emp_dept;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH

ENAME DNAME
---------- --------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.

Now you can take this file to the target database and create an external table
and associate the file with that table.

Step 4: - to be executed on the source database
SQL> set long 2000
SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;

The above command will give you the DDL command to create the table at target
database.

Similar note is also available on metalink 249321.1 which was also written by me when I was with Oracle Support.