?
Solved

"Alter database datafile" problem

Posted on 2006-03-23
15
Medium Priority
?
1,985 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
0
Comment
Question by:deNZity
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16275035

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

Are you SURE???

Copy and Paste the error to Post it here.


0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16275067
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16275082
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:deNZity
ID: 16275326
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
 

Author Comment

by:deNZity
ID: 16275372
markgeer running your query in shell on the server returned no rows??
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16275391
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16275415
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
 

Author Comment

by:deNZity
ID: 16275780
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
 

Author Comment

by:deNZity
ID: 16275804
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
 

Author Comment

by:deNZity
ID: 16276002
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
 
LVL 7

Accepted Solution

by:
vishal68 earned 1400 total points
ID: 16277886
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 600 total points
ID: 16279612
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
 
LVL 4

Expert Comment

by:marper
ID: 16281246
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16281654

Try:

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

Author Comment

by:deNZity
ID: 16295716
Thanks vishal68 and markgeer  and others for answering.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

807 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