Solved

Oracle datafile inactive?

Posted on 2009-05-14
6
318 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
  • 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 168 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 36

Assisted Solution

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

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

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 332 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now