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
ADVERTISEMENT
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
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
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
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
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
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
Oct 31, 2012
Steps to Refresh test Oracle 9i schema from production schema on a Windows platform..
View 3 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
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
Jun 2, 2011
I want to move 350 database objects from one database to another database schema.
View 16 Replies
View Related
Sep 20, 2010
Two different says in different documentation as below.
1) Import required same tablespace to fit the objects, otherwise it throws error.
2) Import by default uses the default tablespace to fit the objects, but some times it uses SYSTEM tablespace also.
confirm with the correct one.
View 8 Replies
View Related
Feb 1, 2011
Why is that we recompile all invalid objects after import and how the object gets invalid during import?
View 1 Replies
View Related
Sep 27, 2011
I am trying to refresh tablespace containing indexes only. Can you let me know whether i am on the right track. Below is my query :
expdp directory=expdp DUMPFILE=user_index.dmp LOGFILE=user_index.log TABLESPACES=user_index
impdp directory=expdp dumpfile=user_index.dmp logfile=user_index_impdp.log tablespaces=user_index
View 4 Replies
View Related
Feb 1, 2012
I am having one prod and one devl with prod having stream setup.
I have to refresh devl with prod , but if i will go by full expdp then db_links also get imported into the devl and may cause problem in devl.
Is there any other way using expdp to exclude the stream objects while doing import.
View 1 Replies
View Related
May 18, 2011
I did the datapump export and import from one schema to a new schema in the same database. I had to use different tablespace. I used the following parameters in the parfiles :
export parfile
directory
dumpfile
logfile
parallel
import parfile
directory
dumpfile
logfile
parallel
remap_schema
remap_tablespace
Tell me whether I need to use different parameters than the one I used? Can I use both remap_schema and remap_tablespace at a time?
View 1 Replies
View Related
Aug 3, 2012
I want to populate totale number of record in the file. Usually i get 10000 records per file and i load them using sql loader.I want to also insert the number of records in file while loading the data in table.
How can i achive it.
structure of control file is
load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table ERS_RIO_SRC
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
[code]...
data
V5_RIO_5K7C|78967|172.16.43.153|RioLoginSrc.asp|0.375|01/08/2012 07:44:44.623|01/08/2012 07:44:45.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78968|172.16.43.150|RioLoginSrc.asp|0.187|01/08/2012 08:22:32.813|01/08/2012 08:22:33.000|V5_RIO_5K7C||||||||||
[code]...
table structure
CREATE TABLE ERS_RIO_SRC
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,
CLINICAL_TRANSACTION_ID VARCHAR2(255 BYTE) NULL,
[code]...
View 4 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
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
Oct 11, 2011
I have created a sql loader control file to load a delimeter file in a table.
The file gets loaded into the table properly, however the commit is happening for every 64 records.
My aim is to rise the commit count by 1000 records.
View 1 Replies
View Related
Apr 16, 2012
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:
$impdp SCHEMAS=<schema> DUMPFILE=<dumpfile> LOGFILE=<logfile>
I got the following error:
SCHEMAS=<schema>: not found
Basically anything after $impdp is always not found. Is there any problem during setup?
View 8 Replies
View Related
Apr 29, 2010
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.
View 6 Replies
View Related
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
Mar 4, 2010
i need to dump the table from A schema to B schema with different table name.
Suppose i have TABLE A IN "A" SCHEMA i need to dump the table with DATA+sTRUCTURE in " B"SCHEMA WITH TABLE NAME AS B.
View 3 Replies
View Related
Jul 26, 2013
The way to do IMPORT of a schema's ALL the TABLES only. Through Data Pump. I don't want to import any other objects like 'package',procedure etc...
Is it the only way is specifying them within EXCLUDE parameter?
View 3 Replies
View Related
May 31, 2012
How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.
View 9 Replies
View Related
Feb 7, 2011
I have exported a schema dump with schema name as 'A'.I want to import that dump in to schema 'B'.how ?
View 5 Replies
View Related