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?
I have table which has 240 columns.
Here is the list of column data type.
VARCHAR2(50)
TIMESTAMP(6)
VARCHAR2(25)
VARCHAR2(25)
NUMBER(15,2)
VARCHAR2(50)
NUMBER(1)
[code]....
The table has 64 partition.. When i count the table(select count(*) from table), i see close to 22 million records.
SQL> select (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
2 from dba_tables
3 where table_name='TRX_TAB';
GB NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
74.9393936 21871585 3679
SQL>
It is supposed to take 75 GB. But this table is consuming 135 GB now. It is occupying 8 GB per day.
How can I delete all the objects from my database?
View 18 Replies View RelatedHow do we find the free space in tablespaces in a standby database
View 4 Replies View RelatedWe 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.
I'm new into Oracle, consider the below scenario and respond it accordingly.
I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.
I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;
System altered.I bounced the instance just to make sure
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....
And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:
SQL> drop table check_char;
Table dropped.
SQL> create table check_char (some_name varchar2(10));
Table created.
SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';
C
-
B
SQL>
What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?
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 RelatedI 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.
How can i know which objects used keep buffer cache?
View 5 Replies View RelatedHow 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 Relatedi 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
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
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
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 RelatedI 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 ?
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?
In My database rollback segment space is not releasing space even though, there is no transaction is using RBS. RBS tablespace size is around 70GB. Unfortunately still our environment is running in 9i due to application code
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 1800
undo_suppress_errors boolean FALSE
undo_tablespace
Is there any way I can calculate percentage of space used in a block.Eg if a table size is 100 blocks,How Can I check the percentage of used space in block.
View 6 Replies View Relatedfor some reason,i want to know which data block contains free space,or which table/index contains free space.
View 6 Replies View Related am using Oracle 10.2.0.4 on win 2008 server SP2. I would like to know if we can set the Segment Space Management feature to AUTO for RBS and Temporary tablespaces. As the data is not permanent in these tablespaces, will it manage automatically?
Presently its Segment Space Mgmt is manual for System, RBS, Temporary tablespaces.
In my drive E: I'll have a space issue soon because one file is taking all space. SYSTEM01. DBF size is around 25GB. I want to know if I delete some data in my database I'll gain space.
View 2 Replies View RelatedIn one of our Data warehousing DB, even though, all the tablespaces' space should keep on at least 1 month, but our leader want us to estimate how much space can free up with db method.
I have referenced
<Administrator Guide> - Reclaiming Wasted Space
[URL].....
I have several questions on reclaim space:
1. It seems that segment adviser give a really cool view to know which segment should be shrink and how much size will free up after shrink. But actually, this need a very a job or manually do this. I have once heard about some query from can estimate this :
A script from MOS, but actually I found this it's not very accurate with segment adviser. This script should report the real space the table occupy, but after shrink space, the space doesn't free up.
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
Also there is a script evaluate the tablespace fragments from some people:
SELECT tablespace_name,
SQRT (MAX (blocks) / SUM (blocks))
* (100 / SQRT (SQRT (COUNT (blocks))))
FSFI
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1;
if the value is very low, we can coalesce the tablespace. But after I coalesce the tablespace, I can't see any space free up.
From <Concepts>:
Quote:Coalescing extents is not necessary in
locally managed tablespaces, because all contiguous free space is available for
allocation to a new extent regardless of whether it was reclaimed from one or more
extents.
2. is there any good way to estimate how much space we should free up? ( After free up the space , dba_free_space should see this result)
Application team requested hosting team to add some space to tablespace as it was exceeding 80% used.Now the hosting team have added the space as per recommendation and the application team wants us to verify if the space was added. How to check the space was added in GB to list of tablesapces ?
View 5 Replies View Relatedwe 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;
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.
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 RelatedI 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.
I just want to know whether we have to pre-allocate space for temporary tablespaces?
View 1 Replies View RelatedHow 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;