"Alter database datafile" problem

Trying to extend the size of tablespace.

ALTER DATABASE  DATAFILE  '/u02/repo1/Repo1/REPO1_REPOS_TEMP.dbf' RESIZE 1000M;

I get the error message nonexistent logfile,datafile, tempfile

"/u02/repo1/Repo1/REPO1_REPOS_TEMP.dbf "

The path is correct and the file name is correct

Oracle10g
deNZityAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:

>>The path is correct and the file name is correct

Are you SURE???

Copy and Paste the error to Post it here.


0
Mark GeerlingsDatabase AdministratorCommented:
Is this a problem with the mixed-case path and file name in the statement you submitted?

Did you try this:
ALTER DATABASE  DATAFILE  '/u02/repo1/repo1/repo1_repos_temp.dbf' RESIZE 1000M;

Try this query to confirm the current path and file name:
select name from v$datafile
where name like ''/u02/repo1%';

Oracle is usually tolerant of mixed-case object names and operators in SQL statements, but Unix and Linux are very case-sensitive, and commands like this need to interact with the operating system and use values that the O/S accepts for those parts of the statement that affect the O/S.
0
Mark GeerlingsDatabase AdministratorCommented:
Oops! That should have been just one single-quote in the query I suggested, like this:

select name from v$datafile
where name like '/u02/repo1%';
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

deNZityAuthor Commented:
Thanks for the replies,

MikeOM_DBA:  ORA-01516: nonexistent log file, datafile, or tempfile "/u02/repo1/Repo1/REPO1_REPOS_TEMP.dbf"

markgeer: Your query returned all the .dbf files except REPO1_REPOS_TEMP :) however browsing the files on the server shows REPO1_REPOS_TEMP.dbf to be in that directory.

I am working with oracle designer and I got this error message while trying to create an organizational unit:

Message
-------
RME-00020: Internal API error - ORA-01652: unable to extend temp segment by 128 in tablespace REPOS_TEMP

--------------------------------------------------------------------------------

Message
-------
RME-00011: Operation 'ins' on VIOLATION has failed

--------------------------------------------------------------------------------

Message
-------
RME-00011: Operation 'close' on ACTIVITY has failed

--------------------------------------------------------------------------------

Message
-------
RME-00222: Failed to dispatch operation to Repository

--------------------------------------------------------------------------------

Message
-------
RME-00224: Failed to close activity

--------------------------------------------------------------------------------
Which is why I was trying to resize REPO1_REPOS_TEMP
0
deNZityAuthor Commented:
markgeer running your query in shell on the server returned no rows??
0
Mark GeerlingsDatabase AdministratorCommented:
I still think it is a case-sensitve name proble on the path and/or file name.  Adjust the query that I gave earlier to return all data file names, like this:

select name from v$datafile where name like '/u02%';

or even:
select name from v$datafile;
0
Mark GeerlingsDatabase AdministratorCommented:
Are you logged in as a DBA?  A query from v$datafile will work in every Oracle database, if you are logged in with DBA privilege (which you need to modify a datafile).
0
deNZityAuthor Commented:
markgeer: yes logged in as.. sys as sysdba

using Toad both queries run and return the same dataset
however from shell on server
select name from v$datafile where name like '/u02%'; returns nada

select name from v$datafile; returns list of all .dbf files

none of the result sets list REPO1_REPOS_TEMP.dbf

0
deNZityAuthor Commented:
How would I drop  REPOS_TEMP?

drop tablespace REPO1_REPOS_TEMP;

Might be easier to drop tablespace and create new one with the same name with autoextend on.
0
deNZityAuthor Commented:
This query "select tablespace_name from dba_tablespaces;" returns the following dataset when executed in Toad on local pc and in shell on server
both show REPOS_TEMP as tablespace.

SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
CONSTANT_GROW_INDEXES
CONSTANT_GROW_TABLES
DEPENDENCY_INDEXES
DEPENDENCY_TABLES
DIAGRAM_INDEXES
DIAGRAM_TABLES
LOB_DATA
RAPID_GROW_INDEXES
RAPID_GROW_TABLES
SYSTEM_META_INDEXES
SYSTEM_META_TABLES
TEMPORARY_INDEXES
TEMPORARY_TABLES
VERSION_INDEXES
VERSION_TABLES
REPOS_TEMP
0
vishal68Commented:
This looks like a Temp file instead of datafile. Try the following query

select * from dba_temp_files;

If this returns the file. then try the following command

alter database tempfile '/u02/repo1/Repo1/REPO1_REPOS_TEMP.dbf' RESIZE 1000M;

HTH
Vishal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
I think vishal68 may be right.  I forgot about the tempfiles.  Remember though that this query will show the tablespace names in uppercase, but not the actual data file names:
select tablespace_name from dba_tablespaces;

Try this query to see both data files and temp files:

select tablespace_name, substr(file_name,1,50) "File_name"
from dba_data_files
union
select tablespace_name, substr(file_name,1,50)
from dba_temp_files;
0
marperCommented:
in Oracle8i you cannot RENAME a TEMPFILE.


Problem Description:
====================

You are trying to rename or move a datafile using:

    SQL> ALTER DATABASE RENAME FILE '<OldPathAndName>' TO '<NewPathAndName>';

but you receive an ORA-01511 and ORA-01516 error.

    ORA-01511: error in renaming log/data files
        Cause: During ALTER DATABASE, an error occurred while renaming
               log or data files.
       Action: Check the error stack for detailed error information.

    ORA-01516: nonexistent log/data file '%s'
        Cause: ALTER DATABASE is attempting to rename/create a log or a data
               file which is not known to the database control file.
       Action: Specify the name of an existing log file.


Solution Description:
=====================

Verify the existing location of the datafile you want to move:

    SQL> SELECT NAME FROM V$DATAFILE;

Reissue the ALTER DATABASE RENAME FILE statement using the filename as it is
listed in the V$DATAFILE view.


Explanation:
============

Using the accurate current location of the file will allow the rename to
proceed successfully.

0
MikeOM_DBACommented:

Try:

ALTER DATABASE TEMPFILE '/u02/repo1/Repo1/REPO1_REPOS_TEMP.dbf' RESIZE 1000M;
0
deNZityAuthor Commented:
Thanks vishal68 and markgeer  and others for answering.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.