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


ADVERTISEMENT

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 :: 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 :: How To Delete A Database

Feb 5, 2012

How can i delete a database? i using the dbca,but the Delate a Database can not use,why?

View 3 Replies View Related

Server Administration :: Unable To Delete A Database Using Dbca?

Sep 17, 2012

I am trying to delete a database using dbca, but the database configuration option and delete option are not enabled. It's grayed.

View 1 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 :: 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 :: 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 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 :: 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 Utilities :: Move Objects From One Database To Another?

Jun 2, 2011

I want to move 350 database objects from one database to another database schema.

View 16 Replies View Related

Server Utilities :: Find All Objects Of Schema HR In Imported Database

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

Server Administration :: DELETE And Buffer Cache?

Aug 19, 2010

1)If i issue a DELETE statement to delete a row, will this statement drag any data from the datafile to database buffer? How is the change made by a DELETE statement recorded in buffer cache? How is this change then applied to the data in datafiles after commit?

View 7 Replies View Related

Server Administration :: How To Delete Flashback Log And Release Space

Feb 15, 2012

How to delete flashback log and to release the space?

I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;

View 6 Replies View Related

Server Administration :: How To Delete Files In Incident Folder

Aug 3, 2010

I have got oracle 11g release 1, I need recomendation, how can I delete safely content from the folder C:oraclediag dbmsinstancenameinstancenameincident?

can i delete files manually which are not useful?

View 4 Replies View Related

Server Administration :: Delete Statements UseTemp Tablespace?

Jun 16, 2011

Why large delete statements, with no where clauses, could be using up temp tablespace segments? I thought temp tablespace was just for sort operations,joins, etc.

We have a weekly job that inserts rows into a staging table, deletes, with commit, the a few minutes later and repeats the process. Each delete is using some of the temp tablespace. Eventually we run out of space (ora-01652). That's another thing. I would have thought the space would be released for the next process, but it's now.

SNAP_TIME USERNAME SESSION_ADDR SESSION_NUM MB SQLHASH SQLADDR SQL_TEXT
2011-06-15; 19:10 SCHEMA_USER 0700000209286B00 49680 980 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:15 SCHEMA_USER 070000020F2A7670 10601 275 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:20 SCHEMA_USER 070000020925FE38 19368 4748 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL
2011-06-15; 19:25 SCHEMA_USER 070000020925FE38 19368 15783 3165065004070000020A8F9F20 DELETE FROM SCHEMA.FIN_RECORDS_TO_EVAL

We will probably switch to truncate statements, but this situation really puzzles me. I've tried view the docs but don't see any of delete statements using temp tablespace.

View 2 Replies View Related

Server Administration :: Unable To Delete Archive Files Through ASMCMD

Apr 13, 2011

we are getting below error:

ora-00257 archiver error. connect internal only until freed

when we tried to remove the unwanted arc files thro ASMCMD,we are getting below error:

ASMCMD> rm -ef 2011_04_05/
Unknown option: e
usage: rm [-rf] <name1 name2 . . .>
ASMCMD> rm -rf 2011_04_05/
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_2_seq_27215.1143.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_3_seq_21762.826.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

further we checked FRA size:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------

CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 3.19 0 38
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

and checked any arc processing holding lock on arc files:

> ps -ef | grep -i ora_arc*

oracle 6989 1 0 15:07 ? 00:00:00 ora_arc0_TXCOM1
oracle 6991 1 0 15:07 ? 00:00:00 ora_arc1_TXCOM1
oracle 12246 12164 0 15:17 pts/4 00:00:00 grep -i ora_arc*
oracle 13452 1 0 Mar23 ? 00:01:07 ora_arc0_TWEBAPPS1
oracle 13454 1 0 Mar23 ? 00:00:30 ora_arc1_TWEBAPPS1
oracle 15402 1 0 Mar23 ? 00:00:50 ora_arc0_SXCOM1

[Code] ........

but we were not able to remove those .arc files from that folder. finally we have down all the instances and deleted those files manually.

View 1 Replies View Related

Server Administration :: Impact On Table Of Partition Truncate / Delete?

Oct 21, 2012

Any impact is there if is do the following:

ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;

will there be any lock on the table during this operation? DML operations will work without any issue or not?

View 10 Replies View Related

Server Administration :: Delete Stats Gather Stats

Sep 11, 2012

During STATS gather running for the table, unknowingly i deleted the old stats using EXEC DBMS_STATS.DELETE_TABLE_STATS. I would like to know will it affect the stats gather job currently running for the table and whether my stats will be gathered successfully.

View 5 Replies View Related

Server Administration :: Database Configuration Assistant Not Working / When Create New Database

Mar 26, 2011

i am installing oracle database 8.1.7 on dell server power edge 2650 first time database successfully installed but when i want to crate new database by Database Configuration Assistant it is not working for new database creation.

View 1 Replies View Related

Server Administration :: Installing Oracle Database 10.1.0.2.0 On Windows 2003 Server 64bit?

Jan 11, 2011

i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.

"The image file D: is Valid, but is for a machine type other than the current machine."

View 1 Replies View Related







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