SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database
Jul 19, 2010
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
View 19 Replies
ADVERTISEMENT
May 2, 2012
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
View 3 Replies
View Related
Dec 2, 2010
I try to do this:
CREATE MATERIALIZED VIEW MV_NESTED_DATA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH COMPLETE
ON DEMAND
START WITH ROUND(SYSDATE)
NEXT ROUND(SYSDATE) + 1
WITH ROWID
AS
select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
View 4 Replies
View Related
Aug 6, 2010
DB1: Sql Server
DB2: Oracle 11g
Is it possible to create a MV log against table@DB1? If not, then am I limited to refresh complete if I need to create MVs against @DB1?
View 1 Replies
View Related
Aug 18, 2010
We are in the process of migrating our databases to a hosting provider (10g Sun -> 11g LINUX.) A (former) data architect at our business had this (nightmare) situation implemented:
- have materialized views created
- after creation of mat views(and associated tables), add additional audit columns to the table that are populated by triggers, not by the MV. All data is important.
In order to get all of the data to the hosting provider, we data pump export the full schemas (which include the associated MV tables), have the provider DBA's import (all the materialized views failed on import, but the associated MV tables were created/populated), and I'm now attempting to fix the MV code to get them recreated. Over 100 MV's, most of which have these extra audit columns, and a number of the remote master/source tables do not have primary keys so using the by rowid option. An edited example (object names changed to protect the innocent) - using CAST to include the audit columns:
CREATE MATERIALIZED VIEW SCHEMA1.ACCOUNT
ON PREBUILT TABLE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 512K NEXT 512K PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE S_TS_01_INDX
REFRESH FAST
ON DEMAND
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE
AS SELECT
ACCOUNT.GROUP_ID GROUP_ID
...
...
cast(null as date) CREATE_PROCESS_DATE,
cast(null as varchar2(20 CHAR)) CREATE_PROCESS_ID
FROM SCHEMA2.ACCOUNT@REMINSTANCE.WORLD ACCOUNT
/
ORA-12058: materialized view cannot use prebuilt table
IF remove "with rowid", get error that cannot create because no primary key on source table.
Online options seem to be (1) do not use prebuilt table (in which case we'd lose the additional audit data) or (2) add a primary key on the master table (we're not in a timeline to make & test changes to various production source tables.)
Other thoughts on how to get this data migrated/populated? This needs to be a lift and drop as much as possible - any type of rewrite/restructuring is out of the question.
View 2 Replies
View Related
May 16, 2012
how to create materialized view on partition table?
View 1 Replies
View Related
Aug 28, 2013
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
View 1 Replies
View Related
Mar 6, 2013
I have a view that includes sub queries in its select.
I am creating a MV over that view. and i want to fast refresh it when ever a commit is placed on the table (that is used in the from clause for the simple view)
View 9 Replies
View Related
Sep 12, 2013
I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies
View Related
May 29, 2013
Can we make Data Guard to take clone Primary Database at Specific Time?
e.g. Clone Primary Database at 10 AM and at 6 PM?
View 10 Replies
View Related
Apr 22, 2009
How to use a table type variable with a regular query? For example, in my PL/SQL proc I have a table of employee names.
v_emp_tbl; --contains 'John','Sally','Ted'
Then I want to get names in a table called tbl_employees which are not in the pl/sql table. So if the table contains 'John','Sally','Ted',and 'Don' then I want to see 'Don'.
Not sure how to do this. Essentially I want something like:
Select emp_name into v_single_emp
from tbl_employees where emp_name NOT IN v_emp_tbl;
I know I can loop through the pl/sql table but I wanted to see if there is some other way.
View 2 Replies
View Related
May 20, 2009
In Oracle 10gR2.If a materialized view uses a database link for the query in order to create a snapshot of data on a remote instance, does the name of the database link have to be an entry in the tnsnames.ora file?
The following link suggests not, but is not version specific: Materialized View - Oracle Wiki FAQ
However, the following 11g documentation suggests that the database link name must be the same as the global name of the target database.URL..
I can't any info specific to 10gR2.
We have three instances. Our application metadata is stored in a schema in B.METADATA. There is a shell schema (B.METADATASHLL) that provides access to the tables to remote applications.
On instance A, we have a shell schema (A.METADATASHLL) that creates materialized views as follows:
CREATE DATABASE LINK METADATA_PRIME
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';
Our materialized view is created using the query SELECT * FROM METADATA.APPLICATIONS@METADATA_PRIME WHERE Application = 'A';
The query works, but the materialized view does not.I'm being told that the database link has to be named B as follows
CREATE DATABASE LINK B
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';
Which means that I can only have one public link to instance B, and I'm in a pickle if I create a second application (which I have) using the same model).
View 6 Replies
View Related
Jul 12, 2012
DB_VERSION=10.2.0.4
OS_VERSION=Windows 2008 R2
RAM_SIZE=32 GB
We have 2 production (OLTP) database( DB1 (18GB) and DB2 (200GB) ) on a server out of which DB2 database has almost 20 Materalized Views all (REFRESH FORCE ..ON DEMAND).
I have already informed the client that this is not the OLAP database where you are going to make use of the M Views for reporting purpose. However currently the client does not have any alternative.
Breif details :-
Out of 20 M Views 3 refresh once a week and where as the rest refresh every night daily.
We are currently testing a new website for which we had to re-schedule all M Views from their Old timings to New Timings i.e. Daily Basis.
But post re-scheduling the database performance was badly degarded as the database was too slow also the number of archives increasted to 300GB which was earlier 100 GB.To resolve the issue , we had to go back to the old timings and the database is currently working fine.
Requirement :-Client is looking out for an alternative solution as he needs to once again change the M View refresh time i.e. Daily Refresh.We already have 2 Oracle Instance on the server
Questions :-
1. Can i create another database ( sayTESTDB2 ) in no archivelog mode and create a db link to reduce the load on the server ?
2. If Yes .. Then is there any relavent link /example or Metalink Note ID for the same??
3. What all parameters do i need to consider while creating a DB Link ?
3. Out of 32 GB RAM , I have given 4GB and 8GB sga_max to DB1 and DB2 Respectively . Also the cpu utilization is normally 50-%60%.
Since currently we do not have any Licence for other server .. Can i make use of the same server ( i.e. creating 3 rd Oracle Instance )?
Since the main intention of this activity is reduce the load from the DB2 onto TESTDB when the daily refresh has set once again
View 1 Replies
View Related
Apr 14, 2011
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.
So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
View 4 Replies
View Related
Nov 15, 2012
I have got a simple materialized view question.
I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.
My question is how can I use the materialized view and it's data from a query.
View 2 Replies
View Related
Nov 5, 2008
How I can create a Materialized View without having any data in it.
For e.g.
I create a Materialialized View based on a View.
CREATE MATERIALIZED VIEW test_mv
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_view
In the above case the data fetched by the view test_view gets stored in the Materialized View test_mv. Suppose I want materialized view test_mv to get created with all the columns of test_view but not the data. I will refresh the materialized view test_mv later for data as and when required.
What shall I do for immidiate formation of materialized view test_mv without data.
View 4 Replies
View Related
Feb 29, 2012
1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.
2.Will materialized views can be created in Fast mode for the above scenario?
View 4 Replies
View Related
Jan 31, 2013
I have big source tables to load in a data warehouse. We are in a full Oracle environment. So I need to extract only delta since the last extract.
I need to capture even deleted rows from the source table.
I have tested the following solution:
- declare a materialized view log on the source table
- load the content on this view log in my ODS
- empty this view log
- load my DWH with the captured delta
It is very simple and seems to work perfectly. I am just confused by the fact that nobody seems to have implemented such a solution.
View 11 Replies
View Related
Jun 12, 2013
I have created a materialized view and also a normal View, which has 3 tables used in both the views, when inserted new records it reflects in a normal view but when i select the materialized view i cant see the updated data.
here is the materialized view i created;
CREATE MATERIALIZED VIEW pct_sales_materialized
BUILD IMMEDIATE REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT A.DEP_NAME,B.EMP_ID,C.EMP_NAME
FROM department_head A,department_child B,emp_detail C
WHERE A.DEP_ID = B.DEP_ID
AND B.EMP_ID = C.EMP_ID
View 2 Replies
View Related
Apr 14, 2011
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
View 1 Replies
View Related
Feb 14, 2012
I have created a MV on Oracle 10gR2 using dblink. Source database is AS400 DB2.MV script:
CREATE MATERIALIZED VIEW MV_BU
TABLESPACE TB_XXX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
[code]...
Above query should be refreshing the data every minute but I realized that its not doing it.
View 1 Replies
View Related
Jul 9, 2012
I have two servers:
10g server 10.1.4.30
11g server 10.1.4.32
I have a table testing_mview on 10g which do not have any primary key. I have created MV log for it:
CREATE MATERIALIZED VIEW LOG ON testing_mview WITH ROWID;
Materialized view log created.
My requirement is
1) if there is an UPDATE/DELETE/INSERT on testing_mview, it should be writen to MATERIALIZED VIEW LOG (this has been achieved)
2) Materialized view testing_mview1 of 11g on server 10.1.4.32 should pull these changes on a scheduled basis ( I have created the database link ORCL10R2 here from 11g to 10 g)
On 11g:
SQL> create database link ORCL10R2 connect to omig identified by pswd using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ICS3)
)
)';
Database link created
SQL> create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2;
create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object SYS.DBMS_SNAPSHOT@ORCL10R2
ORA-00604: error occurred at recursive SQL level 2
ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [55916]
ORA-02063: preceding 2 lines from ORCL10R2
View 3 Replies
View Related
Sep 23, 2013
is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies
View Related
Jun 8, 2012
I use windows 2003 server, oracle 10.2.0.4.0.My question is related to maintaing a standby database
1. Do we need to take RMAN full backup of standby database on a regular basis? At the moment I dont take backups of standby database.
2. I have FRA on standby database. The archivelogs from primary go to FRA on standby. I delete my archivelogs as follows:
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
Though the archivelogs are delete there are still empty folders of archivelogs that are not deleted. How to get rid of them automatically.
View 2 Replies
View Related
Feb 27, 2012
How can i load the data into a new table from view,when ever scheduler runs in the night, the data gets loaded in to a view and data coming from different tables and i should load that data every day and i dont want previous data again.The data should be loaded along with view .
View 6 Replies
View Related
Feb 18, 2011
I'm trying to create a function that simply returns the current database name (e.g: select db_unique_name FROM v$database ) from a function but when I compile it comes up with :
Error(9,5): PL/SQL: SQL Statement ignored
Error(9,44): PL/SQL: ORA-00942: table or view does not exist
I am compiling and running this as the SYSTEM user and I do think that I need to set privledges/roles, etc to allow this (since I have read that using synonyms in functions/procedures requires permissions...but I cannot seem to find anything that tells me exactly what role/priveledge I need to grant/allow to let this happen.
View 5 Replies
View Related
Jul 10, 2010
How can we find the size of a view or synonym or table in a database. What is the code.
View 4 Replies
View Related
May 10, 2013
Is there a function that allows the following?
select SOME_FUNCTION('N','E','S','W') from dual;
That returns
N
E
S
W
Currently I'm just doing the following
WITH direction AS
(SELECT 'N' dir FROM DUAL
UNION
SELECT 'E' FROM DUAL
UNION
SELECT 'S' FROM DUAL
UNION
SELECT 'W' FROM DUAL)
SELECT *
FROM direction;
View 4 Replies
View Related
Apr 26, 2010
I have a query on , how to view the sample data from a very table which is large in size ( more than 10 million ).
I just need to see some sample data from a large table ( to see what kind of data which is application related ).
My question is :
Select *
from Sample_table
where rownum < 10
is this a Good way to view the sample data ?
I have understanidng that the rownum will be assigined to the rows once all the rows are reteived.
So what is the best way to view ?..I am not sure of any condition to put in the intial time of querying.
View 5 Replies
View Related
Aug 11, 2011
base on performance it is better to retrieve data from view or mention the table names directly?
I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view
View 9 Replies
View Related
