Ask Anantha

Cause: Incompatibility   Action: 1. Export the data with the Export utility of the lowest database version involved. 2. Import the data with the Import utility of the target database.

Definitions Materialized View A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.   Materialized View Log When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized view...

This query has become the mostly asked question in any Oracle technical Interview. Following is an excerpt tip which comes from Ramcharan Karthic , software engineer in Bangalore , India .   Note: This tip was published in the January/February 2003 issue of Oracle Magazine .   This Select query will help you select the Nth Max or Min value from any table.   For example, consider a table TAB1 in which you want to find the Nth Max or Min from the column, COL1.   First, the query for Max:   SELECT * FROM TAB1 a WHERE &N = (SELECT count (DISTINCT (b.col1)) FROM TAB1 b WHERE a.col1<=b.col1)   Next, the query for Min:   SELECT * FROM TAB1 a WHERE &N = (SELECT count (DISTINCT (b.col1)) FROM TAB1 b WHERE a.col1>=b.col1)   If N=1 will return first max or first min. N=2 will return second max or min.

The following query if run will fetch Free space, Used space and Total space of each tablespace available.   SELECT Total.name "Tablespace Name", Free_space , ( total_space-Free_space ) Used_space , total_space FROM   ( select tablespace_name , sum(bytes/1024/1024) Free_Space   from sys.dba_free_space   group by tablespace_name   ) Free,   ( select b.name , sum(bytes/1024/1024) TOTAL_SPACE   from sys.v_$datafile a, sys.v_$tablespace B   where a.ts # = b.ts #   group by b.name   ) Total WHERE Free.Tablespace_name = Total.name;   This tip comes from Lazydba

This tip comes from Vikash Varma , Oracle DBA at Intelligent Consulting, in New Jersey . "ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons. Some of them are: You created a synonym on non-existing object by mistake. For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing. You dropped an object but you did not drop the synonyms referencing the object. You dropped a user, but you did not drop synonyms referencing the objects owned by that user. When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms: select * from dba_synonyms s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists ( select 1 from dba_objects ...

This tip comes from Zlatko Sirotic , Software Developer at Istra Informaticki Inzenjering d.o.o., in Pula , Croatia . Suppose you've got a function that is based on a dynamically generated query that returns a ref cursor variable. Now suppose you want to use this ref cursor variable in your procedure, but you don't know the record structure. So how do you make a "FETCH l_ref_cur INTO record_variable " when you don't know the record variable structure. Because ref cursors do not (directly) support description, the solution is quite complicated and requires that the function (or package) returns not only a ref cursor variable but a (dynamically) generated query, too. I am going to use "a good old" DBMS_SQL package and its PARSE and DESCRIBE_COLUMNS procedures in order to make an unknown record variable. 1. Make the "generic" package. First I am going to make a " dyn_fetch " package in which the " describe_columns ...