Server Administration :: Schema Comparing Tool
Apr 8, 2012
we have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.
View 7 Replies
ADVERTISEMENT
May 16, 2011
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
View 2 Replies
View Related
Jul 4, 2008
Any tool or way to view the schema and relations between tables of an Oracle database?
View 1 Replies
View Related
Feb 1, 2010
single schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?
View 4 Replies
View Related
Feb 10, 2012
I have 10 important schemas in my database,and i want to give only select privileges from SCHEMA PRODUSER to other 10 schemas. And also want that the new objects that are created in PRODUSER after granting the privileges are also have select privileges. Is it possible that i should directly grant select privileges from one schema to other without granting via individual objects(script to grant individual privileges from individual objects)
Details are as below:
database version: 9.2.0.8.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2
View 1 Replies
View Related
Sep 14, 2011
a table can have many schema or just 1 schema which is the table owner?
View 5 Replies
View Related
Oct 1, 2012
I need to see all schema users password in a text.I am database admin,but it difficult to remember the password of all users created in single database So we also dont want to change password of all time,table to view password in text. Also we all know we can see using dba_users, but it was showing in hash value i need it in a text
View 3 Replies
View Related
Apr 15, 2012
I want to get clear with one thing yesterday i installed oracle9i and dev 2000 to my client.
when they run one report they got stuck with pl/sql compilation errorrep-1247
when i checked that report in the report builder, in the query they are using some other table which is not belongs to that schema,then I give that schema.tablename and compiled, but this is coming for other reports also, then only i came to know that they are acceessin other schema also, how can i sort this out.
can i fix this by givin full access privilige or what privilige can i give to get full access of other schema table.
how can i check in the old database what are all the roles and privileges given to this user,
View 5 Replies
View Related
Apr 26, 2010
difference between a user and schema on Oracle.By difference, I do not mean what is a user and what is a schema.
I understand that if we create a user, the schema gets created by default for that user. Does that mean that we cannot create a user/schema alone.
What if I just want to create a user and grant him some read access on an existing schema or just want to create a schema.
View 7 Replies
View Related
May 22, 2012
I have created different scripts to monitor growth planning of the database my idea was generate a simple output file to a directory and create a report and send it as mail thru mail server. but the client requested me to create new schema and population all the stats i have collected into the tables in this schema .
How to pool all the stats from different databases into this new schema efficiently.
View 3 Replies
View Related
Mar 22, 2012
When we want to gather schema statistics which method we should follow and why ?
exec dbms_utility.compile_schema('SHIKHAR');
or
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'JACK'
[Code].....
View 3 Replies
View Related
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
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
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
Oct 28, 2010
I have problem/misunderstanding with gather schema stat utility of oracle. Herewith i'm posting my try and output of it. My main question is why the column 'LAST_ANALYZED' of dba_tables not updated on gathering fresh schema level statistics.
SQL>select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
[code]...
10 rows selected.
SQL>execute dbms_stats.gather_schema_stats(OWNNAME => 'STO', OPTIONS => 'GATHER AUTO');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where owner='STO' and rownum<=10 order by LAST_ANALYZED;
OWNER TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS SAMPLE_SIZE LAST_ANAL
------------------------------ ------------------------------ ---------- ----------- ---------- ----------- ---------
STO BILLSLIPB 7695 26 36 7695 29-MAR-10
STO BILL_CHECKING_SLIP 2634 71 28 2634 29-MAR-10
STO FACTORYBILLA 2 119 1 2 29-MAR-10
[code]...
10 rows selected. SQL>
View 4 Replies
View Related
Oct 31, 2012
Steps to Refresh test Oracle 9i schema from production schema on a Windows platform..
View 3 Replies
View Related
Jul 13, 2011
I am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.
SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1
[code]....
View 17 Replies
View Related
Mar 2, 2011
Presently have one instance, one Schema on my DB Server. Want to split the existing schema into 3 logical divisions. Have created the 3 Schemas. Through Oracle Export from the old Schema am able to Export (using wild cards) the tables and indices for each grouping and am able to Import into the new specific schema. The problem i'm having is that i cannot seem to Export the specific sequences due for each of the new schemas. Is there an easy way to accomplish this?
View 6 Replies
View Related
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
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
Jul 7, 2012
Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.
View 12 Replies
View Related
Oct 25, 2011
Are there any GUI based tools that can auto generate a CTL file based off a CSV input? I'd love something like this since I have quite a few SQL*LDR projects coming up!
View 2 Replies
View Related
Jan 3, 2012
I am trying to use the datapump tool to migrate a 10g db to 11g. Everything works fine except for the "nameless" check constraints.
View 7 Replies
View Related
Jul 5, 2012
how to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')
View 4 Replies
View Related
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
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]....
View 16 Replies
View Related
Sep 15, 2010
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]...
View 6 Replies
View Related
Nov 23, 2005
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.
Now, how do i restore the XDB schema.
View 12 Replies
View Related
May 2, 2012
I have a requirement to get the count of those records whose department is not changed since they have joined the organization.
Script:
CREATE TABLE ddumps(orgid NUMBER, orgdate DATE, orgdept VARCHAR2(10))
/
INSERT INTO ddumps VALUES(1,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2000','ORG2')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2001','ORG2')
/
INSERT INTO ddumps VALUES(2,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2000','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2001','ORG1')
ORGID ORGDATE ORGDEPT
1 1/1/1999 ORG1
1 1/1/2000 ORG2
1 1/1/2001 ORG2
2 1/1/1999 ORG1
2 1/1/2000 ORG1
2 1/1/2001 ORG1
since the orgid 1 has changed the dept from org1 to org2 I do not want this to be appeared in the final count. Results should only include the orgid 2 since it didn't changed any dept.
View 11 Replies
View Related
Nov 7, 2012
I'm a Data Manager recently hired at a small company that uses InForm as our data management tool. We are currently not set up with any type of SQL tool to query the database. Consequently, I feel like I'm trying to clean data with one hand tied behind my back. I have two questions:
1) Is there any sort of built in feature in InForm for using SQL statement to query the data? (I'm not really a fan of the built in Cognos feature.)
2) Any tool that is easy to install/configure?
I'm facing some resistance here, so I'm looking for a really easy/cheap solution. I've used SQL Developer before, and I know that's free, but is it easy to set-up?
View 1 Replies
View Related
