SQL & PL/SQL :: Refreshing Schema From One Database To Another

Jul 10, 2012

I am often tasked with refreshing schema's from one DB to another.The first thing I need to check is the space the objects take up in the source DB. SQL statement that prints the size of the following objects

('SEQUENCE','SYNONYM','TABLE','INDEX','PACKAGE','PACKAGE BODY','DATABASE LINK','VIEW','PROCEDURE')

which is totaled by owner, TS.. Or the SQL can just take in an owner. I am looking for an output similiar to this:

TS name, owner, object_name, object_type MB SIZE
users xxx t1 table 100
users xxx t2 table 200

sum of tables 300
[code]....

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Creating And Refreshing Materialized View Group From Another Schema?

Nov 27, 2012

I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.

I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.

View 3 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Server Administration :: Refreshing Stale Statistics In Oracle Database?

Apr 21, 2010

the importance of refreshing the stale statistics in oracle database and also how often we should do this?

View 3 Replies View Related

Server Utilities :: How To Move Schema To Another Schema In Same Database

Jan 5, 2009

move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.

View 14 Replies View Related

Sequence Name In Oracle Database Schema As Well As Timesten Schema?

Jan 13, 2011

How to use same oracle sequence name in Oracle Database schema as well as timesten schema?

View 1 Replies View Related

Convert Oracle Database (schema Data) To Postgresql Database

Oct 11, 2012

i want to convert oracle database(schema data) to postgresql database

which tool would be best and url to download and steps to convert from one database to another ?

View 2 Replies View Related

SQL & PL/SQL :: Mview Is Not Refreshing

Jun 4, 2012

I have a mview based on the database link. Mview created without the error and first time refrehment has been done properly in time, but after the first time it is not refreshing.

CREATE MATERIALIZED VIEW "SMSCHAT"."TBL_VOICECHAT_NEW1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SMSCHAT"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
[code].....

I have set the refreshment time for mview for 15 min with the following command

alter MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 REFRESH FAST START WITH sysdate+0 NEXT sysdate+(15/86400*60);

But now this mview is not refreshing and no jobs is showing in dba_jobs_running.

18:10:32 SQL> select LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,TOTAL_TIME,BROKEN from dba_jobs where job=234;

LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B
-------------------- -------- -------------------- -------- -------------------- -------- ---------- -
04-jun-2012 17:47:13 17:47:13 04-jun-2012 18:02:13 18:02:13 34 N

View 11 Replies View Related

Replication :: Same Database With Different Schema

Dec 25, 2007

Is it possible to create replication on same database with different schema ?

View 1 Replies View Related

Replication :: Materialized View Not Refreshing Automatically

Jan 25, 2011

I am confused to see one materialized view is not refreshing automatically, whereas the same thing I can do manually.

View 2 Replies View Related

SQL & PL/SQL :: Error While Refreshing A Writable Materialized View?

May 3, 2010

Am getting an error while trying to refresh a Writeable Materialized view using the following statement -

exec dbms_mview.refresh('schema_name.MV_name','C')

Error:-
ORA-02448: constraint does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 1

The Primary Key Constraint on the base table/MV is deferrable, the base table and the MV are in different schemas and the user trying to refresh the MV has an explicit grant on "ALTER ANY MATERILIZED VIEW" and "SELECT ANY TABLE" through a role.

Its refreshing successfully when I make the PK constraint (Base Table, MV) NOT DEFERRABLE (or) when I explicitly grant "SELECT ANY TABLE" to the user trying to refresh the MV.

View 5 Replies View Related

Refreshing Logically Correlated Snapshots Data?

Nov 29, 2010

Simplifying the data structure that my problem concern, let's say there are two materialized views between whose data there's a one-to-many relationship [the relationship can be logical without a need of creating any foreing keys].

The data should be as actual as possible respecting the content of the master tables, let's say it shoul be refreshed every 5 minutes.

As far as I know, the jobs related to each snapshot, even if they have a START WITH and NEXT parameter set to the same value, work independently...So: what would be the best manner to synchronize the jobs so as to make sure the data of both snapshots are coherent?

View 3 Replies View Related

Refreshing Materialized View In Oracle SQL Developer?

Apr 6, 2010

I have a database schema with a Materialized View (MV) that updates a products table shown on a website. This schema was recently imported to an 11g database from 8i, but since this import I have been unable to refresh my schema's Materialized View.

The MV_PRODUCT_MASTER Materialized View attaches price data to product data that is stored in a normal WEB_PRODUCT table, and create an MV table as a result. The output MV table is what the website pulls data from to display to the site users. Since it is an MV table, this table cannot be edited directly. In order to change, say, a product description, I need to alter the description field in WEB_PRODUCT and manually refresh the MV_PRODUCT_MASTER table. Only through the refresh will the upstream edits appear in the MV_PRODUCT_MASTER table and be visible on the website.

In my old 7.6.0.11 copy of TOAD, I could manually refresh these MVs easily, by opening the "Snapshots" tab, right clicking on the MV I wanted to refresh and selecting the "Refresh" option. Since the schema was imported to 11g, I have been using the Oracle SQLDeveloper tool to manage the schema. SQLDeveloper doesn't have a clear method for manually refreshing an MV, or else the method I am using isn't working.

If I right click on the MV_PRODUCT_MASTER Materialized View object, and choose "Other Actions", I see the following choices:

Shrink Materialized View
Compile Materialized View
Force Materialized View Refresh
Rebuild Materialized View

I assumed that -Force Materialized View Refresh- was the right choice, and chose that. This option displays the SQL:

alter materialized view "WEBADMIN"."MV_PRODUCT_MASTER" consider fresh

When I apply this, I get the message: Materialized view - MV_PRODUCT_MASTER - has been set torefreshed. However, no changes appear in the MV output table. i.e. if I make a specific change to a row in the WEB_PRODUCT table, the change is not being carried into the MV_PRODUCT_MASTER table, so that indicated that the refresh is not actually happening. The MV table appears to believe it is being refreshed:

REWRITE_CAPABILITY GENERAL
REFRESH_MODE DEMAND
REFRESH_METHOD COMPLETE
BUILD_MODE IMMEDIATE
FAST_REFRESHABLE NO
LAST_REFRESH_TYPE COMPLETE
LAST_REFRESH_DATE 06-APR-10
STALENESS UNKNOWN

But it isn't showing any changes.What am I doing wrong? Is there a plain SQL statement I can run in order to run these refreshes, instead of using the SQLDeveloper GUI?

View 4 Replies View Related

Application Express :: Refreshing Dev Workspace From Prod

Nov 7, 2013

I support an APEX app that has one workspace for Development and one for the live Production application.  When I originally created the Dev instance I was able to copy tables and data by granting select privileges between the two workspaces (I did this one table at a time). 

The development workspace data needs to be refreshed from production since it's been a long time since the initial refresh and is very out of date. I would like to do a couple things:

1) execute SQL to compare the tables in the two workspaces to ensure they're structurally identical
2) execute SQL to truncate Dev tables and refresh from Prod Is there a way to do this? 

View 3 Replies View Related

Application Express :: Fetch Row With / Without Refreshing Whole Page?

May 29, 2013

I would like to create a process that allows me to move from record to record without refreshing a whole page. Specifically, I'd like to use a tree object that allows the user to select items and then have the details for that item show up on the right side of the page. Is there a way to do this without doing a complete page refresh?

So far I've tried two different on-demand processes:

- first I created an "Automatic Row Fetch", but I read on the board that this actually won't work as an on-demand process;

- I also tried a PL/SQL process that sets the various variables (e.g. ":P7_NAME") based on a query. This updated the session state, but it didn't refresh the fields on the page. Is there a way to trigger this refresh?

View 0 Replies View Related

Writing Same Data In Two Database Schema

Aug 3, 2011

I have two same DB schema (same structure, same data) and I need to provide update in one of them when data in the other one is updated. It is singe direction only (we change data in DB Schema A and synchronize data in the DB Schema B; there is not opposite direction). Only small portion of data (compared to the size of DB Schema) might be changed or added this way.

View 1 Replies View Related

How To Move Database Objects From One Schema To Another

Sep 6, 2011

I wanted to move all db objects from one schema to another schema.

View 4 Replies View Related

Import / Export Schema In The Database

Mar 11, 2012

i want to export a particular schema from a database, and than import it in the same database but saving it as a different schema name.

and i need to use imp export utility for it.

one schema with the data... other one with just the meta data.

View 1 Replies View Related

Backup Database Schema And Domain?

Aug 6, 2012

Im in the midst of installing a patch on my UIM. However, in the planning phase, the Oracle guide recommends that I backup my database schema and database domain for UIM in case the patching fails and might affect the whole UIM app. May I know how to do this?

DB : 11.2.0.1.0
UIM : 7.2.0.0.0

View 8 Replies View Related

Replication :: Pausing And Resuming Of Refreshing Materialized Views

Oct 15, 2011

We have some materialized views in our environment which refreshes after every 1 hour. In the same environment, we have some big processes and we do not want the performance of these big processes to get affected because of refreshing these materialized views.

if there is a way to pause the refreshing of materialized views before running the big processes and later after the big processes are completed, then resume the refreshing of the materialized views?

One way I can think of is to drop the materialized views and create the materialized views back.

View 1 Replies View Related

Replication :: Materialized View Is Not Refreshing Data Automatically

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

SQL & PL/SQL :: Insertion Of Data From One To Other Schema Table In Same Database

Aug 9, 2011

I need to insert data from one schema table to other schema's table in same database.The thing is columns are not equal.so when I am trying to use insert statement it is throwing error as not enough values. The situation is explained clearly below.The insert stmt is implemented in second schema whose table name is b.

SQL> create table a(ID_A NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(10));
Table created.
SQL> insert into a(ID_A,Name,rollno,address)values(1,'ravi', 101,'hyd');

1 row created.

SQL> insert into a(ID_A,Name,rollno,address)values(2,'rav', 102,'delhi');

1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(3,'ra', 103,'chn');

1 row created.

SQL> create table b(ID NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(1
0),route varchar2(10),direction varchar2(10));

Table created.
SQL> ALTER TABLE B ADD
2 CONSTRAINT B_PK1
3 PRIMARY KEY
4 (ID);

Table altered.
SQL> create sequence b_seg start with 1;

Sequence created.
SQL> insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual;
insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual
*
ERROR at line 1:
ORA-00947: not enough values

So,for table b in ID column sequence needed to be inserted and other columns need to be taken from table a.I can understand the error is because two tables are not having equal columns.So,the insert stmt is throwing error.

I can manually write by taking columns from a and b and write insert stmt as follows,but this is tedious process.

SQL> insert into b(ID,Name,rollno,address)select b_seg.nextval,lexcom.a.Name,lex
com.a.rollno,lexcom.a.address from lexcom.a,dual;

3 rows created.

But this is time taking and I had tables which has many columns to be inserted.So is there any other way to solve it and implement insert stmt.

View 6 Replies View Related

SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?

Aug 25, 2011

Can we use this format for accesing data from other DB or Schema?

In From clause

database_name.schema_name.table_name

View 7 Replies View Related

SQL & PL/SQL :: ORA-01410 - Invalid Rowid While Selecting / Refreshing Materialized Views

Dec 17, 2012

I have a Multi layer Mviews and Views which i use to load a table. There are three base level Mview which is accessed by 4 views ... which are in turn accessed by a view which is used to load a table.

Before the insert called for the table .. the base mviews are refreshed.

I get the "ORA-01410 Invalid Rowid" error while inserting in to the table and while refreshing the mviews. They don't occur every time and not together as well.

The Mviews contains table joins and the top level views which accessing these mviews does aggregation. Below is the mview query for one of the mview.

SELECT DISTINCT c.fiscal_time_id,
a.product_group_id,
a.allocation_driver_id,
a.hyp_entity_id,
d.business_division_alt_id
[code].....

The Refresh method for the mview is Force.

View 3 Replies View Related

Server Administration :: MView Not Refreshing Automatically At Time Interval Specified?

Sep 14, 2011

I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.

drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

[code]....

I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.

View 33 Replies View Related

Application Express :: Error When Refreshing Report - Widget Failure?

Jan 24, 2013

I am trying to refresh a report with dynamic action. And getting the following errors:

{"dialog":{"uv":true,"row":[{"V":"Widget Failure
ORA-20876: Stop APEX Engine,
classic_report"}]}}

I have identified the problematic part. I have a multi-select select list and my query goes like this:

select o.order_id, s.salesrep_name
from ml_orders o, ml_order_salesrep_mapping m
where m.order_id(+) = o.order_Id
and case when :P22_SALESREP is null then 1

[code]...

View 4 Replies View Related

Server Utilities :: Remap Database Schema During Export?

Mar 3, 2011

is it possible to remap the database schema during export?

Our developers have their databases stored within individual schemas and i want to provide a dumpfile that each developer is possible to easily import to his schema. But when i want to impdp the dumpfile i have to know the schema name within the dumpfile to do a remap to the individual developers schema -> so providing a specific schema name for within the dumpfile would be great.

At the moment i'm getting the ORA-39146, schema does not exist on importing the database..

View 3 Replies View Related

Server Utilities :: How To Import Whole Schema Over Database Link

Apr 22, 2013

I tried to find how to import whole schema over db link , can below command use for whole schema import

CREATE DATABASE LINK EXP_SAT CONNECT TO SYS IDENTIFIED BY SYS USING 'PRTRD01'

IMPDP SYS/SYS@PRTRD01 directory =EXP_SCHEMA NETWORK_LINK = EXP_SAT remap_schema = STG_CUPID_RP2:STG_CUPID_RP2;

How can i import whole schema from other database. i have a database WHO2 WHERE I HAVE A schema called stg_cupid_rp2 i want to import into data WH0T in stg_cupid_rp2 using DBlink EXP_SAT.

View 3 Replies View Related

SQL & PL/SQL :: Refer A Table In Different Schema - Avoid Database Links

Jul 23, 2013

is there any way to refer a table which is in different schema other than using DB links.

View 5 Replies View Related

SQL & PL/SQL :: Created Trigger On Database Level In System Schema

Jun 10, 2010

I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.

CREATE OR REPLACE TRIGGER ddltrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO aud_log
(user_name, ddl_date, ddl_type, object_type,
owner, object_name
)
VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name
);
END;

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved