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


ADVERTISEMENT

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

Server Utilities :: Move Objects From One Database To Another?

Jun 2, 2011

I want to move 350 database objects from one database to another database schema.

View 16 Replies View Related

Query To Identify Invalid Objects In Database Per Schema

Sep 29, 2012

I need a job to get executed for every 1hour.Like i need a query to identify invalid objects in database per schema ,invalid type and this is a job to run every hour.How to schedule it.I only know that dba_jobs have the info.

View 3 Replies View Related

Server Utilities :: Find All Objects Of Schema HR In Imported Database

Jul 25, 2010

I imported a schema HR from export DUMP ....i can find all the objects of schema HR in the imported database... but i got an error for a plan_table which is assigned to USERS tablespace in the source database.. ...

HERE COMES THE ERROR I GOT DURING IMPORT:

[oracle@localhost mom]$ imp file=exp_schema_ref.dmp log=imp.ref.log fromuser=hr touser=hr commit=y
Import: Release 10.2.0.1.0 - Production on Mon Jul 26 00:03:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys/sys as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing HR's objects into HR
. . importing table "COUNTRIES" 25 rows imported
. . importing table "DEPARTMENTS" 27 rows imported
. . importing table "EMPLOYEES" 107 rows imported
[code]....

View 1 Replies View Related

Server Administration :: Validate OLAPSYS Schema Objects After Transferring Database

Apr 21, 2011

We transferred our Oracle database 11.1.0.7 from windows 2003 enterprise edition 32 bit to windows 2008 enterprise edition server 64 bit.Database is working fine but we have 53 uncompiled objects which are related to OLAPSYS and public as follows

OLAPSYSALL$OLAP2_AW_CUBE_AGG_LVLVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_MEASVIEW
OLAPSYSMRAC_OLAP2_AW_DIMENSIONS_VVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_OPVIEW
OLAPSYSALL$OLAP2_AW_CUBE_AGG_SPECSVIEW
[code]....

Our business objec is working fine and all other schema does not have any uncompiled objects.How can we validate the OLAPSYS and PUBLIC schema.

View 11 Replies View Related

SQL & PL/SQL :: Oracle - How To Move Table To Another Schema

Feb 6, 2008

How can I move a table to another schema in Oracle?”

View 33 Replies View Related

Server Utilities :: Move Data From One Schema To Another

Feb 7, 2013

I am trying to move data from one schema in database A to another schema in database B and only move data not others (procedure, view, function). What is the best way to do it? I am thinking database link then insert into select from database link. Is there a better way?

View 8 Replies View Related

SQL & PL/SQL :: What May Be Disadvantages Of Schema Objects

Jul 13, 2010

I was wondering whether we all think of disadvantages or is it we always love the concept and use them..so recently i started out to think what may be the disadvantages of schema objects.

For example Packages has lot of advantages like Information hiding, Scope of varaibles declared in spec, dependency crisis etc, then i read somewhere that if a package is referenced then the entire package will be downloaded in the memory.

So my question is what if the package is very large would it accomdate in the memory without any leaks , i am assuming that its like LEAST RECENTLY USED mechanism. i have a question for you about disadvantages

1. Packages
2. Cursors
3. Collections, includes Associative arrays, VARRAY, nested table
4. materialized views
5. views
6. indexes
7. having more than 100 columns in a table need to spilt it or to keep in the same table.

any object can have its own disadvantages.

View 3 Replies View Related

SQL & PL/SQL :: Recreate Objects Of One Schema Into Another

Apr 8, 2010

I got the below error while executing the procedure. What the procedure doing is recreate the objects of one schema into another. After recreating some objects it throws the error.

code is...

1 CREATE OR REPLACE PROCEDURE SCHEMA_IMPORT6
2 AUTHID CURRENT_USER
3 AS
4 V_Objid NUMBER;
5 V_Xml XMLTYPE;
6 V_Xml_Ind XMLTYPE;
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Invalid Objects In SYSMAN Schema

Jul 19, 2012

Recently I migrated our Oracle to new machine using exp/imp on schema basis. After import finished I had tons of invalid objects in database. I ran utlrp.sql script and lots of them got validated. Than I recompiled manually in EM those are left but two invalid objects (MGMT_JOB_UI description and body) in SYSMAN schema gave error while recompiling.

Now when I click on any scheduled jobs to edit it or view its schedule, EM throw following error:

X Error
jobType - jobType page property expected

I think its related to that invalid package. The errors while compiling the specification are as follow:

Line # = 50 Column # = 1 Error Text = PL/SQL: Declaration ignored
Line # = 65 Column # = 9 Error Text = PLS-00201: identifier 'JOBRUNTABLETYPE' must be declared
Line # = 88 Column # = 1 Error Text = PL/SQL: Declaration ignored
Line # = 107 Column # = 9 Error Text = PLS-00201: identifier 'JOBEXECTABLETYPE' must be declared

The Package specification is as below,

AS---------------------------------------------------------------------------------- type definitionTYPE CURSOR_TYPE IS REF CURSOR;-- Get the targets for this step, if any-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN SMP_EMD_STRING_ARRAY;-- Get the targets for this step, if any, as a comma separated string-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets_str ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN VARCHAR2;-- Get the parameters for this job, filter as specified for this jobtypePROCEDURE get_visible_params ( p_job_id RAW, p_exec_id RAW, p_params_out OUT CURSOR_TYPE);-- Get the URI for this uri_use-- see emSDK/job/dtd/UriSource.java for uri_use constantsFUNCTION get_display_uri ( p_job_type IN VARCHAR2, p_uri_use IN

[code]....

View 16 Replies View Related

What Privileges Other Schemas Have On Objects Of Schema A

May 19, 2013

i have schema A and i want to check who(which schemas) can access schema A's objects and what privileges other schemas have on objects of schema A.

View 7 Replies View Related

Loose Coupling Among Objects Within Oracle Schema?

Sep 30, 2010

I am building an information service that manages Suppliers. The suppliers are used by our billing system, tender system and sales system. Though 60% of the attributes of supplier are unique to each system, there are still 40% attributes of Supplier that are shared across the systems.

My objective is to build a flexible system, so that change to one individual system's data, should not impact other systems. For example, if i need to make certain tables offline for upgrading them, it should not impact rest of the systems that need supplier information. What is the best way of achieving this? Should all the different context specific attributes live in one schema, but deployed on different table spaces? Also, the read and update may happen more for one set of attributes than the other. How should i logically represent them via one model, but deploy them in such a fashion that they can evolve independently?

View 3 Replies View Related

Replication :: Replicate (A) Schema Objects To (B) At Server?

Mar 30, 2011

I told about Oracle multi master replication below.

Can I replicate some objects of "X" schema at A server to "Y" schema at B server using MM replication?

View 13 Replies View Related

SQL & PL/SQL :: Returns Comments Of All Objects Of Schema Using Metadata Api

Mar 2, 2010

A function returns the comments of all objects of the schema using metadata api. I used DATABASE_EXPORT as object_type for open function. which name (eg: base_object_name, base_object_schema...) should i use to get comments of single object. My code is..

1 CREATE OR REPLACE function f_depen_obj
2 return clob
3 as
4 a number;
5 b number;
[code].....

I got comments of all objects when i commented 11th,12th lines. Now i want the comments of EMP table of SCOTT schema. I got the following error ..

SQL> select f_depen_obj from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "EMP"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "SCOTT.F_DEPEN_OBJ", line 17

no rows selected...i got output (comments of single object) when i use COMMENT instead of DATABASE_EXPORT in open function(line no: 9). But i need to use DATABASE_EXPORT as a object_type. Which name is suitable for set_filter procedure instead of 'NAME'?

View 3 Replies View Related

Server Utilities :: Getting Count Of Objects After Schema Refresh

Sep 13, 2013

Iam having the following query, After executing schema refresh using export & import , getting count of database objects comparison to be done,

-- SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
SQL> SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;

The output expected was to display each table name in a schema following below with corresponding number of records to be displayed, but it wasn't showing correctly.

View 7 Replies View Related

Server Administration :: Deleting Unwanted SYS Schema Objects?

May 25, 2011

I was importing one schema from Oracle 10g to 11g using traditional import. I imported as a SYS user, so all the objects created in SYS schema. how can I remove these objects and retain only default SYS objects

View 11 Replies View Related

Server Administration :: USERS Tablespace Should Not Contain Schema Objects?

Jan 31, 2012

I heard that USERS tablespace should not contain any other application schema objects.

If the above statements is true , why it should not contain other schema objects ?

View 7 Replies View Related

Backup & Recovery :: Restore Schema And Its Associated Objects Once Dropped?

Sep 11, 2011

Is there a way to restore the schema and its associated objects once it is dropped?

1. The flash back database feature is not ON in the database where schema was dropped.

2. The data files/ table space has not been dropped

3. There was no export taken for the schema.

View 4 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 Utilities :: IMP-00058 - Error While Trying To Import Objects Into Schema

Feb 23, 2013

I get import error while trying to import objects into schema.

Export file created by EXPORT:V11.02.00 via direct path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing DEMO's objects into TEST
. . importing table "TAB1"
IMP-00058: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'

i understand we need to grant the user space resource on the tablespace as below.

ALTER USER <user> QUOTA UNLIMITED on <tablespace_name>

My another question is can we grant QUOTA UNLIMITED on <tablespace_name> to user ?

View 2 Replies View Related

Server Administration :: Impact Of Up-to-date Statistics On Main Schema Objects

Feb 29, 2012

I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.

For this I wanted to:

first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.

However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.

View 1 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

Move Database To New Host With More RAM / CPU

Apr 10, 2013

We plan to move the database to a new host with much more RAM, CPU. The new database server will have exactly the same OS version, Kernel version and the same version of database / grid standalone (ASM). I will have to install OS, Database software, Grid Standalone on the new server.

The current version of the database is 11.2.0.3.0 Enterprise Edition and the OS version is Oracle Enterprise Linux 6.3. However, we plan to use the same disks (ASM disks). I believe I don't need to use RMAN to do the migration, Is it correct?

View 7 Replies View Related

SQL & PL/SQL :: How To Move Only Subset Of Data From One Database To Another

Mar 13, 2012

The requirement details are as below,

1) The database contains static as well as transactional data for telecom domain.

2) We have to move region-wise data from one database to another.

3) There are around 10 regions.

4) The region wise data extraction from source db is based on around 40 to 50 tables. Rest of the tables contains static data & it will not change.

5) The volume is around 1 million subscribers per region.

6) The migration is required because

a) The client is upgrading its base product which uses this database

b) There is a change in structure of static tables

c) Hardware upgrade

d) The client want to start with single region on new database & rest of the regions will be operated from old database.

7) Keep execution time to very minimum.

I am thinking to have solution as below,

1) Create destination database with upgraded db structure (as mentioned in 6b)

2) Create database links to access source db in destination db.

3) Write SQL queries to fetch data from all the respective tables for a specific region

4) Write separate PL/SQL blocks for each table to fetch data from source db & insert into respective table in destination db

a) Use FOR ALL & bulk collect to improve the performance

b) Divide table data into multiple chunks & execute parallel batches (around 10) to insert the data

5) Validate pre & post counts to verify the success of migration

View 6 Replies View Related

Client Tools :: To Move Oracle Job From One Database To Another

Jun 15, 2011

I want to move a oracle job from one database to another database. i tried to open the job from toad by pressing f4 on it in editor , im getting message like...

"unsupported type for describe popups (Without DB admin module)

View 2 Replies View Related

Server Administration :: Move Database From 10.2.0.4 (Solaris 9) To 11.2.0.2

Sep 17, 2011

I have to move a database from 10.2.0.4(solaris 9) to a 11.2.0.2(Solaris 10) . 11g shell database is ready .Its a 400GB database.

View 7 Replies View Related

Server Administration :: How To Move 10g Database And Make New Installation

Oct 31, 2010

Are there any good information about on how to move a 10g database to a new server? We will keep the same disks on the SAN, but make a new installation on the new server.

View 3 Replies View Related

SQL & PL/SQL :: How To List Database Objects

Jul 21, 2010

Is there a SQL that gives the list database objects (tables/views/packages/functions etc.) where a particular column in a table is used?

Example:

CREATE TABLE XYZ (ID NUMBER);

I want to know all the objects that used the column ID in table XYZ either in select statement or in a condition.

View 3 Replies View Related

SQL & PL/SQL :: Dependencies Between Objects In Database?

Dec 3, 2012

Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.

Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.

View 9 Replies View Related







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