Solved

Oracle datafile inactive?

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

911 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

21 Experts available now in Live!

Get 1:1 Help Now