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


ADVERTISEMENT

Server Administration :: How To Resize Temp Tablespace

Jun 30, 2010

i got a error temp tablespace cannot be resized. How to Resize Temp Tablespace?

View 10 Replies View Related

System Tablespace - File Contains Used Data Beyond Requested RESIZE Value

Oct 23, 2012

I'm trying to increase the size of my system tablespace. The tablespace contains one datafile size 900MB and it can increase to 2GB.

I'd like to change this to autoextend as I'm going to run csscan on the database.

When I do change the autoextend and unlimited part of the file I get: ORA-03297: file contains used data beyond requested RESIZE value

I've also tried to increase the datafile to 4GB but still the same. I can see that there is more then enough space on the filesystem.

Oracle Enterprise edt 10.2.0.5

View 8 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 :: 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 :: Reduce The System Tablespace Size?

Aug 6, 2011

how to reduce the system tablespace size

my system01.dbf size is 6gb

i want reduce from 6gb to 2gb

View 3 Replies View Related

Server Administration :: Tablespace Auto-Extend OFF On Production System

Jun 6, 2012

I want to OFF tablspace AUTOEXTEND on a prodution system, we have many RAC databses and that will be done on all stations. i have got a document from net which was written on 29-Jun-2007 and it says that if need to OFF the AUTOEXTEND of a TABLESPACE so you need to ist make it off on the underlying datafiles of that tablespace so this doc is for Oracle 8.1.7.2.0

View 2 Replies View Related

Server Administration :: Remove Datafile From Operating System After Dropping Its Tablespace

Jul 26, 2012

I want to remove a tablespace with it's datafile.

I've ran: DROP TABLESPACE x_tbs INCLUDING CONTENTS;

I've read(should have read it prior to action) [URL]tm

and I understand now that I should have stated "AND DATAFILES", However, too late now. the tablespace is removed, but the datafile x_tbs is still present in a folder of my OS.

Is it safe to manually remove it by deleting it from the operating system?

View 2 Replies View Related

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.

View 13 Replies View Related

Server Administration :: ASM Diskgroup Resize?

Sep 19, 2012

I've been ASM quite a while. I've two diskgroups extdg and nordg which are of external and normal redundancy respectively. Total diskgroup size is 9G, usuable space came down to 3mb when i tried to resize it to itz maximum. the value under usable column shows the same.

FYI... i've totally three disks in nordg, two in one failgroup and one in another.The disk sized equally 3g each.

Quote:
NAME TOTAL_MB FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- --------------
EXTDG 2047 1995 1995
NORDG 9201 3073 3

The command i used to resize all disk to their max is Quote:SQL> alter diskgroup nordg resize all size 3067M;

View 3 Replies View Related

Server Administration :: Resize Datafile

Mar 21, 2011

Today i am facing an error when going to rezise the datafile its fixed size is 19000M abut after truncating all tables it is 112M, but when i am going to resize its datafile to 500M its get me an error ora-03297 file contain used data beyond requested size values.

I have done the same before a week without any error.But this time i got the error

View 5 Replies View Related

Server Administration :: Space Management - Unable To Resize Datafile?

Feb 13, 2013

I have one generic question about space management. I have one table with size of 1TB. This table stored in ORC1 tablespace. This tablespace contains 70 datafiles.

Since it's 10.2.0.4 database. I have dropped this table by using purge

drop table <<table_name>> purge;

Once table drop was completed. When I check the tablespace space it was 100% free but due to HWM was unable to resize the datafile from current size to small size. What was the reason behind this. Is there any process needs to follow when dropping big tables ? like instead of dropping the tables do I need to truncate first & then drop .

View 5 Replies View Related

How To Resize A Tablespace

Aug 12, 2010

One of my friend is facing a problem that while creating the database he has chosen UNDOTBS size as 4 GB and now he wants to resize it.

View 1 Replies View Related

Backup & Recovery :: Tablespace Resize

Mar 29, 2012

I have a "prj_tbl" named tablespace in a user's schema in my database. i have given 100mb size to the "prj_tbl" at the time of its creation and auto extend is open for 10mb,and now this tablespace is using nearly 3.6mb space for its data and remaining space is free,now i want to reduce the size of "prj_tbl" tablespace to 20mb and when i tried to resize it then ,

It throws an error- "'ora-03297 file contains used data beyond requested resize value oracle"

I think this message showing that my new size is small than the size of data on my tablespace but it shows free space nearly 96mb then it means my new size is larger than the size of data on tablespace. How should I reduce the size..

View 5 Replies View Related

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?

creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces

View 9 Replies View Related

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

My os version is

Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux

My database is OLP system.

My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?

Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.

View 7 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.

The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.

This database is a mission critical database and very less downtime can be allowed.

View 6 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 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 :: 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

Server Administration :: System And Users Table Space

Oct 15, 2011

a newbie dba here..

select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
/

result output is attached .txt file.

the SYSTEM & USERS table space shows only <10 mb free space. Is it a bad sign? What I should do ?

View 7 Replies View Related

Server Administration :: System / Manager Not Accessing In Oracle9i?

May 31, 2010

Recently i am installed oracle9i in my laptop.In oracle scott/tiger accessing. But system/manager not accessing.

View 4 Replies View Related

Server Administration :: Create Tablespace For Administration

Nov 29, 2010

i'm a student currently learning database administration security.

I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.

I have attached the schema that was given to me for this assignment.

View 12 Replies View Related

Server Administration :: Discrepancies In SYS / SYSTEM Object Counts After Migration

May 3, 2010

We had a new outsourcer who completed migration of 1 of our system running under Oracle10g-Solaris 10. We created an inventory script that will list all objects counts of SYS, SYSTEM and ORACLE and all other schemas. This script will be ran before (old server) and after migration (to new server). After migration to the new & bigger server, we noticed from the output of the inventory script for SYS/SYSTEM object counts had been reduced compared from the original/actual value (from the old server). Our outsourcer said that this is not an issue since SYS/SYSTEM objects are not transactional and cannot be controlled.

As we will conform that the migration was completed successfully by our new outsourcer.

View 7 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 :: ORA-01078 / Failure In Processing System Parameters

Feb 25, 2010

I had newly installed oracle 11g database, for testing & learning purpose.

Don't know wht happen to it after successfull installation , I am not able to start database.

Following error is appearing, how to resolve this.

Ora-01078:failure in processing system parameters

I can do only login to idle instance.

View 9 Replies View Related

Server Administration :: Unable To Claim Space For Operating System?

Sep 15, 2012

We are in the process of gaining some space in our database. We identified a tablespace that occupied around 400GB of space. The datas are required for us. Hence, we truncated (with drop storage) the tables, gathered statistics and shrinked the tables. After doing that we found the datafile occupied around 32GB of space. We tried to resize it to 5 GB and we got "ORA-03297: file contains used data beyond requested RESIZE value". We could see that size in dba_free_space against that tablespace. We would like to give back everything to OS.

We also tried exporting the tablespace without rows, dropped the tablespace and re-created and imported again. But the datafile grows to around 32G and we are unable to resize it.

We are sure that the tablespace has only the table definitions without data. But we do not know why is it occupying around 32G of space and we could see that size in dba_free_space. We would like to give back everything to OS. How can we do that?

View 5 Replies View Related







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