Oracle Tablespace problem

[steveot]

To start off im not a Oracle Expert.

Im having a problem with 3rd party backup application.

Third party backup application uses oracle file OCIw32.DLL to make calls and query into Oracle.

I have a tablespace that is reporting it has two database file assosiated to it when one file physicaally doesnt exist. Oracle wont work on the issue since its 3rd party program but they can provide me with any commands to check if oracle is right.

All indication from 3rd party backup application debug is telling me that oracle is returning that the table space has two file assosiated with it. Backup program debug also reports this is coming from oracle and the command issues in/out of the database are sucessful.

Backup Application see the following two file in the tablespace. When selected for backup it failes on the "MISSING00015" since it physically doesnt exist on disk.

name=E|/ORADATA/PROD/ABC_DATA.DBF
name=D|/ORACLE/ORA92/DATABASE/MISSING00015

Anybody know a query to find if oracle is referencing something that doesnt exist.

 

[LKBrwnDBA]

You are missing a file.

The "MISSING00015" means that when Oracle DB was started, it did not find one of the tablespace files and marked it as "missing".

You may need to recover your database or re-build that tablespace.

Good luck!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Steve,

Oracle uses the file appelation, "MISSING000xx", as a place holder for a file that used to exist for a tablespace, but that does not exist presently.

The way that I have found to get rid of this problem is to

1) choose a time to make the database unavailable; shutdown the database, then bring it back up with "startup restrict" (which prevents log-ins by non DBAs).

2) execute a query that lists all of the space-consuming objects (i.e., segments...aka tables, indexes, et cetera) assigned to the faulty tablespace.

3) export all of objects that reside in the tablespace (typically with a schema-level export)

4) drop all of the tables (which also drops the indexes) that you listed out in step #2, above.

5) drop the faulty tablespace: "DROP TABLESPACE <name>;". With this version of the DROP TABLESPACE command, if it fails due to a "tablespace not empty" error, then determine what object(s) remain, then export/drop them, as well.

6) create a new tablespace to replace the just-dropped tablespace.

7) import (into the new tablespace) from the dump file(s) that you created in step #3, above.

8) make the database available to all user again: "alter system disable restricted session;"

Let us know if this resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[steveot]

Dave : Do you know a query I can run in Step 2 you provided to list the space consuming objects?

Thanks for you detailed information.

 

[SantaMufasa]

Sure, place the following code in a script and run the script from SQL*Plus. (Since there is an ACCEPT statement, you cannot run via a copy-and-paste...it must run from a script.):

set echo off
accept ts prompt "Enter the name of the tablespace to profile: "
col x heading "Space-consuming Objects in '&ts' tablespace" format a80
select owner||'.'||segment_name
       ||' ('||segment_type||': '||to_char(bytes,'fm999,999,999,999')||' bytes)' x
from dba_segments
where tablespace_name = upper('&ts')
order by owner, segment_type, segment_name
/

DHUNT.SYS_C0013386 (INDEX: 65,536 bytes)
DHUNT.ALKSTERS (TABLE: 65,536 bytes)
DHUNT.ALM2_ACTIONS (TABLE: 65,536 bytes)
...et cetera

Notice that the objects are in order of object type within owner for objects in the tablespace...The indexes disappear when you DROP the table that the index modifies.


Let us know if this helps to solve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[steveot]

Excellent. What Im seeing now is that in CMD prompt it looks as if sql plus output is past the buffer size in cmd prompt. Anyway to get this out to a text file?

 

[SantaMufasa]

Yes, you can output SQL*Plus output from a query by placing the following SQL*Plus commands prior to your SELECT statement:

set echo off
set linesize 200
set trimspool on
spool <some file name that you can fully qualify>
SELECT...
spool off

The "guts" of the above code are the "spool" related commands; the others are there just to "spruce things up" for your output.


Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dreampolice]

Is there a Default schema related to my login in our Oracle 9i database?

It seems everytime I use SQL Plus to connect I can query a schema (called FirstSchema) such as:
select * from TableOne;

But all other Schemas I have access to with my login I have to put the schema name in front of the table name:
select * from SecondSchema.TableTwo;

FirstSchema was the first schema created for my login and I assume that is my "default" Schema? Is this normal and for other co workers of mine who have logins to the database how can they find out their "default" schema?

 

[SantaMufasa]

Dream,

Standard users can say:

select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------
<name of user's tablespace>

Anyone with SELECT privileges against the DBA_USERS view can say:

select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------
SYS                            SYSTEM
SYSTEM                         SYSTEM
OUTLN                          SYSTEM
DBSNMP                         SYSTEM
...et cetera...

And yes, every user must have a DEFAULT TABLESPACE.

Whenever you can SELECT from a table without qualifying the table with a schema name, that means either:

1) Your current login owns the table, or
2) There is a PUBLIC SYNONYM for the table you are accessing, which implicitly qualifies the owning schema name.

Let us know if this information resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[steveot]

Well the SQL query worked but I did not see any reference to MISSING00015 in my case. I dont know what else to try to see where oracle is referencing this.

 

[SantaMufasa]

Whoa, Steve, the above query won't give you any information about "MISSING00015" (the file)...the script only displays the DEFAULT TABLESPACE for each user.

If you want to see file names associated with tablespaces, then the query you want is probably my "freespace.sql" script, which shows the filenames associated with each tablespace (along with some other great information):

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".

Let us know if the results from this script give you the information you were looking for.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[steveot]

Dave : U da man!!! that last script worked and we found that oracle was returning the correct information to the 3rd party application. So Oracle is at fault. Three case opened with Oracle support and they kept shutting us down. So Im off to forward this information back to them and say fix it.



ABC_DATA 10 2,097,152,000 1,572,864,000 1,249,705,984 847
,446,016 40 Yes 'E:\ORADATA\PROD\ABC_DATA.DBF'
IAL_DATA 15
No 'D:\ORACLE\ORA92\DATABASE\MISSING00015'
ABC_INDEX 11 524,288,000 104,857,600 65,994,752 458



IF you Ski or Ski Atla in the Spring Id buy you a beer.

 

[SantaMufasa]

IF you Ski or Ski Atla in the Spring Id buy you a beer.

Never skied at "Atla", but my I always ski "Alta" since I'm about 12 minutes down slope from there.

I'll take you up on the "beer" offer:

[tt]
,----------- A&W Root Beer
| ,--------- Barq's Root Beer
| | ,---- IBC Root Beer
| | | ,-- Hires Root Beer
V V V V [/tt]


Fanks, Steve,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Im off to forward this information back to them and say fix it.

Be prepared for their "fix" to be the steps I listed for you, above.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]