Re-organize SYSAUX Tablespace
Oct 10, 2010
Sysaux tablespace is 70Gb and keep on growing.How to re-organize the sysaux tablespace in oracle 10.2.0.4 database.My database sysaux tablespace occupants as follows
OCCUPANT_NAME SPACE_USAGE_KBYTES
---------------------------------------- ------------------
LOGMNR 6080
LOGSTDBY 896
STREAMS 512
XDB 49408
AO 26688
[code]....
View 1 Replies
ADVERTISEMENT
Jul 8, 2011
I have a lot of shemas inside an instance... How to organize them?
What I see is a long list of schemas which could be difficult to manage if number of schemas grows up... For example, I'd like to group Schema_1, Schema_2 and Schema_3 in something like Group_1, so it could be seen more organized.
Group_1
Schema_1
Schema_2
Schema_3
Sometimes a java or .net application uses 3 or 4 schemas... these schemas are in the same instance which has other schemas for other applications.
View 2 Replies
View Related
Oct 30, 2007
10.2 Oracle database on Linux
Someone(a non DBA) removed the only sysaux datafile from the server using the rm command Obviously, the DB is still up and none of the schemas in the database have their objects right now.
This is not a critical database as you can see when I say that I have NO backup whatsoever. Even though I have the option of deleting and recreating the database, i want to know if I can avoid doing that.
Creating a file on the server with the same name OR creating a new datafile for the tablespace OR recreate the sysaux tablespace ?But how to do any of these? What else should be done ?
View 38 Replies
View Related
Oct 3, 2012
Just now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
View 9 Replies
View Related
Jan 18, 2012
How to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
View 2 Replies
View Related
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
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
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
Jan 22, 2013
I have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.
Attached File(s)
1.jpeg ( 1.8MB )
Number of downloads: 1
2.jpeg ( 1.6MB )
Number of downloads: 1
3.jpeg ( 1.52MB )
Number of downloads: 0
View 1 Replies
View Related
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
May 2, 2010
what is the data dictionary table to find out the default table space and temporary table space for a user.Let say i have a user called 'Pointers'.How to find out the default table space and temporary table space for this.I have checked in 'dict' table but no luck.
SELECT *
FROM dict
WHERE table_name LIKE '%TABLESPACE%'
View 4 Replies
View Related
Mar 7, 2012
I have a basic question : After the transfer of a database on an other server, the tablespace contains 5 files, but 3 of the 5 files are full. The data in the files may change, they are not static. What is the risk of poor performance, do you think that the files should have free space.
View 2 Replies
View Related
Mar 21, 2013
CREATE TABLE /*+ PARALLEL(CLAIM_LINE_OASIS_PK_TMP, 16) */ EDWSTG.CLAIM_LINE_OASIS_PK_TMP NOLOGGING AS
SELECT /*+ PARALLEL(B, 16) PARALLEL(C, 16) */
B.CLM_LN_EDW_SK,
C.ICN_NBR,
C.CLM_LN_NBR,
C.ADJ_CD,
C.CLM_ORIG_CD,
C.CLM_HDR_EDW_SK,
[code]...
ERROR at line 136:ORA-01652: unable to extend temp segment by 2048 in tablespace EDWSTGDATA00
Is there something that can be done?Is there a way to estimate the space required for this SQL?
View 24 Replies
View Related
Feb 14, 2011
I have come across a small incident in oracle. I have user that exists on the database and is open but user's tablespace is not listed under v$tablespace.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.4.0 - 64bit Production
PL/SQL Release 9.0.1.4.0 - Production
CORE 9.0.1.2.0 Production
TNS for Solaris: Version 9.0.1.4.0 - Production
NLSRTL Version 9.0.1.4.0 - Production
SQL> select USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username ='METERING';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
XYZ OPEN TS_DATA1 TS_TEMP
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
TS_TEMP
STATSPACK_DATA
RBS
INDX
DATA
6 rows selected.
View 6 Replies
View Related
Aug 11, 2012
How can i set alert message for users tablespace.
for ex
My users(tablespace) data file size is 100m . If 90m reached , i need some alert message through email or mobile.
Os : linux 4
version :10.2.0
View 2 Replies
View Related
Apr 18, 2007
I have a small problem, i will try to explain it, My tablespaces are Automatically extend datafile when full (AUTOEXTEND). It had checked "autoextend ON" checkbox.
What I need to know is, when happened that autoextend accion(day/hour), is it possible?.
View 10 Replies
View Related
Jun 16, 2013
i have 80 gb table d9040 which residing in separate tablespace in existing database(10g) OLTP .
i m going to import(impdp) an export(expdp) dump on 11r2 on which ASM is residing.
I have following two options available while importing
1.Put all the datafiles of that particular tabesace on a single disk .
or
2.Put half datafiles on disk 1 and remaining half on disk 2.
View 4 Replies
View Related
May 10, 2011
While I was exporting ( schema level ) ,I am getting following errors :
vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
'Parfile' contents :
owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log
What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace
View 1 Replies
View Related
Nov 15, 2010
I have 25G free in tablespaces. but while inserting the table i am getting error.
ORA-01688: unable to extend table CIRCMANAGER.TBK_NEW_ORDER_SUMMARY partition HA20044Q by 512 in tablespace ts_neworder
We need to change any parameter?
View 4 Replies
View Related
Jun 2, 2013
My require is to get all tablespace used,free space report for all the databases. So I have scheduled Job in OEM to get it with below script. But always got error and my job is getting failed again and again. How to get the all tablespace (used,free space) report for all the databases.
sql' select distinct
a.tablespace_name,
SUM(a.bytes)/1024/1024 "Used Size MB",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Max Size Mb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Total Used MB",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Total
[code]......
View 2 Replies
View Related
Mar 10, 2011
Well, I have a oracle database 10g and the tablespace INDX was getting up to 32 GB size. Now I added second datafile to the space, but can I shrink this space? In my view this space is responsible for indexes, right? There is a command to rebuild the indexes or there's another trick?
View 1 Replies
View Related
Jun 15, 2007
i have used toad to create a tablespace say XYZ and user ABC from the system schema.
Now i want to delete this tablespace xyz.
View 1 Replies
View Related
Feb 4, 2013
I need to prepare script to move all objects from one tbs to another tbs. Should I move all the objects individually using "alter table" Command. I got all the objects information using "DBA_SEGMENTS" view.
I have more number of tables,indexes in that tablespaces.
I can not use exp for tablespace backup.
View 4 Replies
View Related
Sep 10, 2010
I'm having issues with things disappearing from my database. I know what you are thinking, OK Crazy things don't just disappear. The only thing I can think of that could be a possible problem is the way the oracle server is set up. (For the record, I don't agree with this set up. I was like this when I started with the company and they don't want to change it "because it works").
We currently have 90 + schemas on the server all in one tablespace. So the server has a data tablespace and an index tablespace. Every schema shares those tablespaces. It gets much worse that this but I'm not going to get into it.
Do you think that this set up would cause things to become missing?
View 6 Replies
View Related
Apr 24, 2011
I am trying to import the dump of DB into a new DB on ORACLE 10G XE. But while importing i am getting error saying "Table Space has reached it's Max Limit of 4Gb".
I tried to add more file in Tablespace, Turn on AutoExtend Option but I am not able to get Tablespace of more than 4Gb. Is it just not possible to extend TableSpace beyond 4Gb in oracle 10G XE
I require Table space of around 15-20Gb. If it cannot be achieved in ORACLE 10G XE then which ORACLE version should i use to get the required TABLE SPACE.( i want to install it on Window's Vista Home Basic)
View 1 Replies
View Related
Mar 25, 2013
We have started developing a new application to compress tablespaces based on the business specification.
This is a Data Warehouse.
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
And there is a requirement to find the uncompressed segments,to find whether the tablespace is already compressed or not.
View 1 Replies
View Related
Aug 19, 2010
On one of our systems, the system tablespace ist growing up fast. The largest objects are:
SYS.C_OBJ# 3 GB (CLuster)
SYS.I_COL1 2,8 GB (INDEX)
SYS.I_COL2 1 GB (INDEX)
SYS.I_COL3 980 MB (INDEX)
why theses objects are going so big? Is it possible to shrink these objects? Table of the three index ist sys.col$ and part of CLuster sys.c_obj#
View 3 Replies
View Related
Dec 5, 2012
How to Managing Tablespace usage Alerts with out EM(Enterprise manager), in oracle 11g & 10g
I want to set threshold on tablespace usage, it is possible to set threshold on tablespace usage reached 80% full then send email notification with out EM use.
If it not possible,then any other option available to get automatic alert.
View 1 Replies
View Related
Aug 20, 2010
I tried to create an index on a large volume table(approx 9 GB) and I got the below error message:-
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
The size of our TEMP tablespace is 3GB and currently it is showing as full.
This tablespace is locally managed and we are not running any other operation on this test database at the moment.
We need to delete data from this table based on a column value, so I was planning to create an index on that column.
I saw on the net that one of the proposed solution is :-
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
but it was mentioned that this does not work with Locally Managed Tablespaces.
We are currently using Oracle 9.2.0.7.0 production release installed on Linux platform(Linux 2.6.9-89.0.25.ELsmp #1 SMP i686 i686 i386 GNU/Linux)
I increased the size of TEMP tablespace to 12 GB but the index creation still failed with the same error.
View 1 Replies
View Related
Jun 15, 2012
How can we clean a tablespace(delete all the contents) without dropping the tablespace.
View 3 Replies
View Related
