Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle datafile inactive?

Posted on 2009-05-14
6
Medium Priority
?
327 Views
Last Modified: 2013-12-18
Hello,

Before two days i expanded a tablespace creating a new datafile. I used the enterprise manager console.

The problem is that this datafile is indicated as online but no data is written to it. Instead the temporary tablespace is begin using. It's increasing with a huge performance impact.

All of the datafiles permisions are -rw-r--r- and the new datafile has -rw-rw---

Can i change the attributes from the datafile? Do i have to shutdown the database first?

We are using 10g on RedHat Enterprise 4.4

Thank you.
Teo
0
Comment
Question by:temm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 

Author Comment

by:temm
ID: 24382965
Also note that i had connected with the Enterprise Manager console as sys/normal since i couldn't connect as sys/sysdba from the remote machine.

Teo
0
 
LVL 10

Accepted Solution

by:
ravindran_eee earned 672 total points
ID: 24392428
Temporary tablespace and permanent tablespaces are for different purposes. Permanent tablespaces are used for storing objects and the later, as name suggests is for temporary operations (for example sorting operations). It does not make sense to compare these 2 tablespaces.

What operations are being performed to expect your newly added datafile space to become full? Are you inserting huge data or creating some tables? If you are going to run some queries against your DB, only the temporary tablespace will be used.
0
 

Author Comment

by:temm
ID: 24394733
Well this is a tablespace where some indexes are stored. There are a few tables also. I did some test inserting records into a test table but i don't see the size of the actual data change from the enterprise manager.

Is there a way to see excatly the amount of bytes in a datafile e.g. from dba_data_files?

0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1328 total points
ID: 24402413
bytes used per tablespace:

select tablespace_name, sum(bytes) /1024 from dba_segments
group by tablespace_name
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1328 total points
ID: 24402443
you can see if the objects (tables, indexes, etc.) are in the correct tablespace too

select * from dba_segments
where segment_name LIKE  '%TABLE_NAME%';
0

Featured Post

Find the IP of a Linux Server in Command Line

Knowing your server’s IP address(s) can be a useful bit of information to have for various reasons. This tutorial will teach you how to check the IPs of any modern Linux server. To follow along will simply need access to the server via SSH or TTY.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question