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
ADVERTISEMENT
Mar 30, 2011
I wanted to transfer my database from Oracle 11.1.0. on windows 2003 server 32 bit to Oracle 11.2.0. windows server 2008 64 bit
i have done following
1)On new server of 11.2.0 windows 2008 64 bit use rman backup from Oracle 11.1.0. on windows 2003 server 32 bit to restore the database and recover the database when i try to open the database Alter database open it gave me message that i should start my database as upgrade I start the database with upgrade
STARTUP UPGRADE and then run following
@?/rdbms/admin/utlirp.sql
and then run following
@?/rdbms/admin/utlu112i.sql
and then run
@?/rdbms/admin/catupgrd.sql
during run of catupgrd.sql after around 30 minutes of run, i got error
ORA-03113: end-of-file on communication channel
but i reconnected myself with database again and shutdown the database and open it in normal mode and it has opened there were thousands of uncompiled objects and i recompiled them.
Only uncompiled objects left 60 are 'OLAPSYS' (Locked user) USER and 30 of PUBLIC user (all Synonyms)
Have i taken correct steps?
What should i do now to check that everything will be alright in future
View 6 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
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
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
Jul 30, 2012
How can I delete all the objects from my database?
View 18 Replies
View Related
Oct 3, 2011
select sum(bytes/1024/1024) from dba_segments where owner='IPPS';
Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?
View 7 Replies
View Related
Sep 6, 2011
I wanted to move all db objects from one schema to another schema.
View 4 Replies
View Related
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
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
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
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 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
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
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
Jan 5, 2012
I am working on Index issue and need to validate the structure of the index. Does validation lock the Index ?
Do we need downtime or we can do it in normal working hours.
alter index index_name validate structure;
View 9 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
Apr 1, 2011
I have database A (Working in Live environment) and Database B copy of Database (Not live) I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B
There are around 20 schema If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.
IF i do following in source database
expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE
AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes.
View 5 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
May 11, 2012
I have a doubt on invalid objects.What would be the impact to database and application if there are many invalid objects in database?
View 3 Replies
View Related
Mar 22, 2011
I am getting below error while connecting to sqlplus.
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 22 12:47:48 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
[code]....
Executed the below scripts but it didnt resolve the issue, whereas some of the SYS objects and catproc got invalid...
dbmsotpt.sql
dbmsapin.sql
Now even after reexecuting the catproc.sql and utlrp...Sys objects and the catproc status is still INvalid.
I tried to manually compile the sys objects, but it didnt work.
OWNER SUBSTR(OBJECT_NAME,1,40) OBJECT_TYPE
-------------------- ---------------------------------------- --------------------
SYS DBMS_XPLAN PACKAGE BODY
SYS AQ$AQ_SRVNTFN_TABLE VIEW
SYS DBMS_LOGREP_DEF_PROC PACKAGE
SYS DBMS_LOGREP_DEF_PROC PACKAGE BODY
[code]....
how to go about making the SYS objects and catproc VALID and resolve the error which i mentioned above.
View 36 Replies
View Related
Mar 28, 2011
How can i know which objects used keep buffer cache?
View 5 Replies
View Related
May 6, 2012
How monitor the unusable objects in oracle 10g (10.2.0.1.0) os rhel 5? is it same as invalid objects and can be monitor as same?
View 4 Replies
View Related
Dec 15, 2012
In production database 30 tables are there in different schemas.
In some tables data is existed for 5 years
in some tables data is existed 8 years
in some tables data is existed for 6 years.
And all these tables are having billions and millions of records.Let us assume the tables in the production database are
Table1 -- 8 years data -- No. Of Records 3538969000
Table2 -- 6 years data -- No. Of Records 592844435
Table3 -- 3 years data -- No. Of Records 33224993
Table4 -- 4 years data -- No. Of Records 52361756
Table5 -- 5 years data -- No. Of Records 8948567
Table15 -- 6 years data -- No. Of Records 308476987
Now I want to trnasfer these 15 tables data to test database.Based on the following conditions.
For Table1 I want to transfer 6 years data to test database, keeep 2 years data in production and delete all 6 years data from production databse.
For Table2 I want to transfer 4 years data to test database, keeep 2 years data in production and delete all 4 years data from production databse.
For Table3 I want to transfer 2 years data to test database keeep 1 years data in production and delete all 2 years data from production databse.
This will be done periodically.
I.e. suppose if we run the script now and transfer the requested data to test database,again after one year the data got increased in production then again we have to run the script and transfer data to test database based on some conditions,so it should work for long time. what is the good and fast process to transfer the data.
View 31 Replies
View Related
Oct 1, 2012
Im doing a project for my college and i need to transfer database. The thing is that i have created tables with databases and linked it to VB using ADODC.
I have done all this in the Computer at my home. So i have done all the project in my computer. I want to show the same project in my college, but i cant do it as the database is stored in my personal computer at home. Is there any ways i could transfer the database from my house to college like storing it in a pendrive. I used sqplus
oracle 9i to create the tables.
View 15 Replies
View Related
Jul 6, 2010
i am trying to shrink tablespace of 100gb which has objects.
i tried coalesce,then i try to shrink and also tried to resize the datafile with no luck
error message can't resize
View 2 Replies
View Related
Aug 3, 2012
I am getting the following errors when I try drop a tablespace.
I already did the following.
a) The tablespace & its datafiles offline.
b) I have purged dba_recyclebin.
SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
View 14 Replies
View Related
Jan 31, 2012
here are so much invalid dba_objects. Do I need to care/repair?
SELECT owner, COUNT (*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY ROLLUP (OWNER)
output
ZIM4_RO71
ZIM473
PUBLIC16
SYS35
195
View 11 Replies
View Related
Jun 27, 2012
One of my friends is facing a peculiar problem where objects are getting "Invalid" during execution I suspect it is happening as they are changing system date during their testing (time travel) which can create conflicted last_ddl_time on objects having dependencies
Consider a scenario
[1] system date is 10-06-2012 there are total 10 objects which has status as 'valid'
[2] the system date is changed to 10-07-2012 Now out of 10 Only 5 objects are compiled During execution ORA-04065,ORA-06508, ORA-06512 are observed
[3] the system date is brought back to 10-06-2012 Again during execution ORA-04065,ORA-06508, ORA-06512 are observed
suppose in step 2 objects are compiled whereas there synonyms are compiled in step 1, only thus last_ddl_time for objects will be later to that of its' synonym...
Does database validate last_ddl_time for objects having dependency during execution and then auto-compiles or invalidates the objects?
View 3 Replies
View Related
