Oracle datafile inactive?

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
temmAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ravindran_eeeConnect With a Mentor Commented:
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
 
temmAuthor Commented:
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
 
temmAuthor Commented:
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
 
Geert GConnect With a Mentor Oracle dbaCommented:
bytes used per tablespace:

select tablespace_name, sum(bytes) /1024 from dba_segments
group by tablespace_name
0
 
Geert GConnect With a Mentor Oracle dbaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.