Server Utilities :: Compare Column Name Of Schema With Other Schema's Column Name
Dec 15, 2012
I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop.
SET SERVEROUTPUT ON
DECLARE
V_COLS VARCHAR2(20);
BEGIN
FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES
WHERE OWNER LIKE 'CCD_MAIN'
[code]....
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
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.
I would like to find out same column_name from different table across the schema, which means if two tables or more are having the same column name but is not bound by foreign keys, I want to find out and list. for detailed report if we required small anonymous block also fine.provide the info for the above.
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
I have a problem that goes like this. My senior sent me the recent dumps of our database from production. He told me that dont drop the tablespaces and users in order to save time of re-creation. He told me told me to drop the objects of individual users. Now, i have dropped the objects of XDB oracle's default schema which is required for XML support functions.
How to export the XDB schema from another database with the same version and re-store in the present database.Whenever I try to take the export of XDB user from other database I get error user does not exist. I have checked in the console it is locked.
I'm new to oracle and solaris.got a dmp file that I need to restore from. I've managed to successfully restore it in windows but not in solaris. the following command:
I want to compare two schema and find out object differences and generate DDL script to make schema1 like schema2. I was wondering what is the easiest way to use dbms_metadata.compare_alter to compare all tables. I know its possible through procedure but I am new to sql.
I have a requirement to add all the objects of schema A of database X to schema B of database Y. Schema A and B does not have any common objects.
Apart from tables,indexes schema A also have BLob object, materialize views, type. The size of database X is 50 GB. Through initial research I found data pump , traspoting tablespaces , and RMAN duplication .
Is the any other method I should be looking at? Is RMAN duplication even a option because I just want to copy a schema not the whole database? The data base has other schemas too.
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?
This message for my clarification,we have two schemas MAD_LIVE and MAD_TEST we want to take data from MAD_LIVE to MAD_TEST for particular period of time. way to import data from MAD_LIVE to MAD_TEST for particular period
My SCHEMA MAD_LIVE using from year 2010 till now,we want to take only 2012 data from MAD_LIVE and restore it in MAD_TEST.
when i try to export schema using expdp i got error Like
Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 6.25 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.
OPTIONS (ERRORS=100000000) LOAD DATA CHARACTERSET UTF8 INTO TABLE "TARGET_STG_AMC" TRUNCATE [code]....
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.
I have two oracle 9i databases A and B with complete equal schema. Only the data is different. I want to import all table data from A to B. The problem is that there are duplicate primary keys. Therefore I want to insert data with new primary keys (all referencing tables are concerned too).
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..
I'm trying to deploy the schema using DATAPUMP API. The user from which the schema get deployed has the direct privilege of CREATE USER (not through role). But got the insufficient privileges error.
Processing object type SCHEMA_EXPORT/USER ORA-31685: Object type USER:"SCOTT1" failed due to insufficient privileges. Failing sql is: CREATE USER "SCOTT1" IDENTIFIED BY VALUES '4EBB0DDE3C79FE47' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP2" PROFILE "APP_PROFILE".
But the user get created successfully when run the CREATE statement manually.I have created the user manually and again run the deployment procedure. Got the below error for ROLE_GRANTS.
Processing object type SCHEMA_EXPORT/ROLE_GRANT ORA-39083: Object type ROLE_GRANT failed to create with error: ORA-01932: ADMIN option not granted for role 'EXP_FULL_DATABASE' Failing sql is: GRANT "EXP_FULL_DATABASE" TO "SCOTT1"
The user has EXP_FULL_DATABASE with ADMIN Option and IMP_FULL_DATABASE with ADMIN option direct privileges.which privileges the user needs to deploy the schema successfully?
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.
I have exported data of one user an importing into another schema at another server. when i am trying to imoport it is working fine for quite no of imports into tables, but after some time it starts giving me below mention error...
IMP-00008: unrecognized statement in the export file: < IMP-00008: unrecognized statement in the export file: < IMP-00008: unrecognized statement in the export file: <ے IMP-00008: unrecognized statement in the export file: +A IMP-00008: unrecognized statement in the export file: [code]...
