How To Move Database Objects From One Schema To Another
Sep 6, 2011I 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, 2009move 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 RelatedServer Utilities :: Move Objects From One Database To Another?
Jun 2, 2011I want to move 350 database objects from one database to another database schema.
View 16 Replies View RelatedQuery To Identify Invalid Objects In Database Per Schema
Sep 29, 2012I 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 RelatedServer Utilities :: Find All Objects Of Schema HR In Imported Database
Jul 25, 2010I 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]....
Server Administration :: Validate OLAPSYS Schema Objects After Transferring Database
Apr 21, 2011We 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.
SQL & PL/SQL :: Oracle - How To Move Table To Another Schema
Feb 6, 2008How can I move a table to another schema in Oracle?”
View 33 Replies View RelatedServer Utilities :: Move Data From One Schema To Another
Feb 7, 2013I 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 RelatedSQL & PL/SQL :: What May Be Disadvantages Of Schema Objects
Jul 13, 2010I 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.
SQL & PL/SQL :: Recreate Objects Of One Schema Into Another
Apr 8, 2010I 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]....
SQL & PL/SQL :: Invalid Objects In SYSMAN Schema
Jul 19, 2012Recently 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]....
What Privileges Other Schemas Have On Objects Of Schema A
May 19, 2013i 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 RelatedLoose Coupling Among Objects Within Oracle Schema?
Sep 30, 2010I 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?
Replication :: Replicate (A) Schema Objects To (B) At Server?
Mar 30, 2011I 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?
SQL & PL/SQL :: Returns Comments Of All Objects Of Schema Using Metadata Api
Mar 2, 2010A 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'?
Server Utilities :: Getting Count Of Objects After Schema Refresh
Sep 13, 2013Iam 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.
Server Administration :: Deleting Unwanted SYS Schema Objects?
May 25, 2011I 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 RelatedServer Administration :: USERS Tablespace Should Not Contain Schema Objects?
Jan 31, 2012I 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 ?
Backup & Recovery :: Restore Schema And Its Associated Objects Once Dropped?
Sep 11, 2011Is 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.
Server Utilities :: Import A Schema From One Database Schema To Another Schema B?
Aug 10, 2010I 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]....
Server Utilities :: IMP-00058 - Error While Trying To Import Objects Into Schema
Feb 23, 2013I 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 ?
Server Administration :: Impact Of Up-to-date Statistics On Main Schema Objects
Feb 29, 2012I 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.
Sequence Name In Oracle Database Schema As Well As Timesten Schema?
Jan 13, 2011How to use same oracle sequence name in Oracle Database schema as well as timesten schema?
View 1 Replies View RelatedMove Database To New Host With More RAM / CPU
Apr 10, 2013We 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?
SQL & PL/SQL :: How To Move Only Subset Of Data From One Database To Another
Mar 13, 2012The 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
Client Tools :: To Move Oracle Job From One Database To Another
Jun 15, 2011I 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)
Server Administration :: Move Database From 10.2.0.4 (Solaris 9) To 11.2.0.2
Sep 17, 2011I 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 RelatedServer Administration :: How To Move 10g Database And Make New Installation
Oct 31, 2010Are 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 RelatedSQL & PL/SQL :: How To List Database Objects
Jul 21, 2010Is 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.
SQL & PL/SQL :: Dependencies Between Objects In Database?
Dec 3, 2012Dependencies 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.
