Server Administration :: Clean Up Duplicate Objects Owned By SYS And SYSTEM?

Dec 1, 2011

we are doing database upgradation 10g2 to 11gr2, while doing pre check before upgrdation ..we have found few duplicate objectes on sys and system schema..

SQL> column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')

[code]...

As per metalink note "How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema [ID 1030426.6]"..

im going to do drop only below objects ..

DROP TABLE SYS.HELP;
DROP INDEX SYS.HELP_TOPIC_SEQ;
DROP TABLE SYSTEM.PLAN_TABLE;

and ignore below objects ...

DROP TABLE SYSTEM.AQ$_SCHEDULES;
DROP INDEX SYSTEM.AQ$_SCHEDULES_PRIMARY;
DROP PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
DROP PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-04089 / Cannot Create Triggers On Objects Owned By SYS

Oct 10, 2011

now i can log in but here i faced with another erorr, while i am creating a trigger it gives the following erorr:

SQL> ed
Wrote file afiedt.buf
1 create or replace trigger trgr
2 before insert on myTable
3 for each row
4 begin
5 select mySequence.nextval into :new.ID from dual;
6* end;
SQL> /
create or replace trigger trgr
*
ERROR at line 1:ORA-04089: cannot create triggers on objects owned by SYS

note: i tried and gave different names for the trigger, still it is the same.

View 22 Replies View Related

Security :: Error Ora-28663 Cannot Encrypt Sys Owned Objects

Mar 21, 2012

I am getting this error ora-28663 cannot encrypt sys owned objects,...any solution ,how i can perform TDE.

View 8 Replies View Related

Server Administration :: How To Clean Corrupted Dba_tables

Feb 5, 2013

I have a table enrty in dba_tables:

SQL> select OWNER,TABLE_NAME from dba_tables where TABLE_NAME='Viep_Porteur_Etudiant';
OWNER TABLE_NAME
------------------------------ ------------------------------
IEP_PESSAC Viep_Porteur_Etudiant

But this tables doesn't exist then I can't drop this table:

SQL> desc IEP_PESSAC.Viep_Porteur_Etudiant;
ERROR:
ORA-04043: objet IEP_PESSAC.Viep_Porteur_Etudiant inexistant
SQL> drop table IEP_PESSAC.Viep_Porteur_Etudiant;
drop table IEP_PESSAC.Viep_Porteur_Etudiant
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante

How can I clean dba_table? I suppose that that's happened because the user tablespace was full when he tried to create this table...

View 6 Replies View Related

Server Administration :: Cause Of Invalid Objects

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

Server Administration :: How To Delete All Objects From Database

Jul 30, 2012

How can I delete all the objects from my database?

View 18 Replies View Related

Server Administration :: SYS Objects And Catproc Invalid?

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

Server Administration :: How To Know Which Objects Used Keep Buffer Cache

Mar 28, 2011

How can i know which objects used keep buffer cache?

View 5 Replies View Related

Server Administration :: Monitoring Unusable Objects

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

Server Administration :: Shrink Tablespace Of 100gb Which Has Objects

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

Server Administration :: ORA-38301 / Cannot Perform DDL / DML Over Objects In Recycle Bin

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

Server Administration :: Invalid Dba-objects - Care / Repair?

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

Server Administration :: Deleting Unwanted SYS Schema Objects?

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

Server Administration :: USERS Tablespace Should Not Contain Schema Objects?

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

Server Administration :: Objects Invalid Due To Auto-compilation

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

Server Administration :: System TableSpace Is So Big?

Sep 26, 2011

Today I see my system tablespace is 98%, 480 MB used. Why the system tablespace is so big?

View 5 Replies View Related

Server Administration :: System Tablespace Had Been Used More?

May 4, 2012

In my database ,the size of the system tablespace is 1024M.now i found its free space is 477.875M.

SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 M FROM DBA_FREE_SPACE where tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME ;
TABLESPACE_NAME M
--------------- -------
SYSTEM 477.875

In system tablespace ,the max size segment is IDL_UB1$,it is 167M

SQL> SELECT SEGMENT_NAME, SX || 'M' "Size" FROM (SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 SX FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME) WHERE SX > 100 ORDER BY SX DESC;
SEGMENT_NAME Size
------------- -----
IDL_UB1$ 167M

QUESTION: what's the table IDL_UB1$ used for ?

View 4 Replies View Related

Server Administration :: IPPS Has Use Up This Amount Of Space In Database From All Objects?

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

Server Administration :: Modified Objects - Compare Two Users On Different Databases

Oct 2, 2012

I want to compare two users on different databases, actaully there are two users one in user a on database a and another user b on database b they have same tables, and everytime when a table or object is created on user a (database a) i will take the table name ,procedure or any other object from user_objects based on ddl_created date and then i need to recreate the same on user b on database b, is there a way to find out tables which are not only created but also i need to check whether if there is any column added or any change in procedure or any other objects.Is there a way to generate the scripts based on list of objects selected from user_objects.
all i want is.

a)Find out the list of objects added along with creation scripts
b)find out the list of objects modified along with creation scripts.

View 2 Replies View Related

Server Administration :: Duplicate Datafile Name

Feb 29, 2012

I have oracle 10g running on Solaris with file system and some one created database files with same name but in different directories for example data01.dbf in two different directories, say /u01/oradata/data01.dbf and /u02/oradata/data01.dbf. Now, I want to find out the duplicate datafiles (data01.dbf in this case) sitting in different directories, is there anyway to find this out?

View 11 Replies View Related

Server Administration :: Non-expiring SYSTEM And SYS Password

Aug 27, 2012

that's what I did:

SQL> select profile from DBA_USERS where username = 'SYSTEM';

PROFILE
------------------------------
DEFAULT

SQL> alter profile DEFAULT limit password_life_time UNLIMITED;

Profile altered. But I get still the warning message every login. ORA-28002: the password will expire within 7 days.

$ sqlplus system/oracle111@XE
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 27 10:29:03 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

View 2 Replies View Related

Server Administration :: OFA And Raid System For Oracle

May 5, 2011

Can I use OFA and Raid system together for Oracle 11g in Windows 2008 Server? I am installing the OS in C drive. Where to install the oracle, location for Oracle_Base, number of logical and physical drive required, size of each etc.

I am planning to use
Raid 1 - Controlfile, redolog file and archive log file
Raid 1+0 - Datafiles

Also I want to multiplex virtual memory page also.

View 6 Replies View Related

Server Administration :: System Tablespace Resize?

Dec 23, 2012

My system tablespace size is 800mb and it sends an alert saying that it has exceeded 80% of its usage.However its total max size is 30gb.

FYI
Its autoextensible is set to 'YES'.
2 Node RAC database.
File system is ASM

My question here is... is it advicable to resize a system tablespace to some size when autoextensible is set to 'YES'
or genrally can we resize a system tablespace when autoextensible is set to 'No' ?

SQL> col FILE_NAME format a40
SQL> /
FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------------------------------------- --------------- --- ------------------
+DATA/ge01114/datafile/system.556.2254454 800 YES 30000.9844

View 4 Replies View Related

Server Administration :: Why Can't SYSTEM Tablespace Extend

Mar 19, 2010

The SYSTEM tablespace refuses to autoextend (3 segments in SYSTEM tablespace unable to extend). It sits on its current size of 559MB (auto set to 2GB, increment 10M).

I guess there are no free extends that are that big as 10M.

View 5 Replies View Related

Server Administration :: System View Cannot Found

Feb 22, 2012

There are some sys views can not found. why?

SQL> connect / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> desc dba_users;
ERROR:
ORA-04043: object dba_users does not exist

View 2 Replies View Related

Server Administration :: Sys Password Changed By System?

Feb 18, 2010

Here is an article to show a case when SYS password is changed by SYSTEM:

[URL]....

This behavior is not working on all the environments.

View 10 Replies View Related

Server Administration :: Trace IP Address And OS Username / Service IP Who Modified Objects?

Sep 4, 2011

I have a problem some user modified the objects and now objects became invalid .How to trace the ip address and operating system username and service ip who modified the objects?

View 3 Replies View Related

Server Administration :: Impact Of Up-to-date Statistics On Main Schema Objects

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

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 View Related

Server Administration :: Move File System Datafile To ASM

Oct 23, 2012

I mistakenly added a datafile to a tablespace which is asm, however the datafile was created in a default location and not the asm location:

alter tablespace pdaiidata1 add datafile '<filename>' size 2048M;

What I should have done:

alter tablepsace <tablespace_name> add datafile '+DATA1' size 2048M;

Is there any way to move this filesystem datafile into the asm tablespace? In previous Oracle versions, I've taken a tablespace offline, moved a datafile, renamed it, then brought the tablespace back online. Can I do something similar here in this situation?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved