We help IT Professionals succeed at work.

"Alter database datafile" problem

deNZity
deNZity asked
on
Medium Priority
2,703 Views
Last Modified: 2007-11-27
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
Comment
Watch Question


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

Are you SURE???

Copy and Paste the error to Post it here.


Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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%';

Author

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

Author

Commented:
markgeer running your query in shell on the server returned no rows??
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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;
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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).

Author

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

Author

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.

Author

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
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
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;

Commented:
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.


Try:

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

Author

Commented:
Thanks vishal68 and markgeer  and others for answering.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.