Solved

Oracle datafile inactive?

Posted on 2009-05-14
6
321 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 37

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 37

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

1. Introduction As many people are interested in Linux but not as many are interested or knowledgeable (enough) to install Linux on their system, here is a safe way to try out Linux on your existing (Windows) system. The idea is that you insta…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

808 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