-
Temp files !!
SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
SQL> select file_name
2 from dba_data_files
3 where tablespace_name='TEMP';
FILE_NAME
--------------------------------------------------------------------------------
/db07/oradata/DMG/DMGDWHD/temp01.dbf
/db09/oradata/DMG/DMGDWHD/temp02.dbf
/dba/DMG/oradata/DMG/temp03.dbf
SQL> desc dba_temp_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS CHAR(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
SQL> select file_name from dba_temp_files;
no rows selected
-------------------------------------------------------------------
Why do the temp files not show up in the 2nd view ?
-
Are the temp files of type temporary? You can name a tablespace TEMP but it doesn't mean it is a temp tablespace unless you specify that.
default temporary tablespace orcl_temp tempfile 'c:\ora9i\oradata\orcl\orcl_temp.dbf'
size 5m autoextend on
Last edited by stecal; 05-25-2005 at 02:58 PM.
-
You probably didn't specify TEMPFILE when you created the temp tablespace. Pull the temp tablespace DDL and post it.
I remember when this place was cool.
-
Tx. for confirming what I was thinking - just inherited this rather messy dev. db. - no way to get the DDL the dba maintaining this left
and no one bothered to get even the app. schema passwords !!
-
See Note 160426.1 for detailed explanation.
-
My next q:-
SQL> select tablespace_name from v$sort_segment;
TABLESPACE_NAME
-------------------------------
TEMP
--------------------------------------------------------------------
So it show up here but not in dba_temp_files !!
-
http://download-west.oracle.com/docs...pace.htm#10561
Temporary Datafiles
Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles, with the following exceptions:
Tempfiles are always set to NOLOGGING mode.
You cannot make a tempfile read-only.
You cannot rename a tempfile.
You cannot create a tempfile with the ALTER DATABASE statement.
When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
--------------------------------------------------------------------------------
Caution:
This enables fast tempfile creation and resizing; however, the disk could run of space later when the tempfiles are accessed.
--------------------------------------------------------------------------------
Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.
I remember when this place was cool.
-
Originally posted by cruser3
My next q:-
SQL> select tablespace_name from v$sort_segment;
TABLESPACE_NAME
-------------------------------
TEMP
--------------------------------------------------------------------
So it show up here but not in dba_temp_files !!
If you look at dba_users what is the temporary tablespace? I would guess that it is temp. You should create a new temp tablespace and point all of the users to that tablespace. See what is in temp, and as long as it doesn't contain actual data, then drop it. Make sure that no one is using system for their temporary tablespace.
-
I'm guessing it is not LMT.
This is under CREATE TABLESPACE parameters...
TEMPORARY
Specify TEMPORARY if the tablespace will be used only to hold temporary objects, for example, segments used by implicit sorts to handle ORDER BY clauses.
Temporary tablespaces created with this clause are always dictionary managed, so you cannot specify the EXTENT MANAGEMENT LOCAL clause. To create a locally managed temporary tablespace, use the CREATE TEMPORARY TABLESPACE statement.
--------------------------------------------------------------------------------
Note:
Oracle Corporation strongly recommends that you create locally managed temporary tablespaces containing tempfiles by using the CREATE TEMPORARY TABLESPACE statement. The creation of new dictionary-managed tablespaces is scheduled for desupport.
I remember when this place was cool.
-
Originally posted by cruser3
My next q:-
SQL> select tablespace_name from v$sort_segment;
TABLESPACE_NAME
-------------------------------
TEMP
--------------------------------------------------------------------
So it show up here but not in dba_temp_files !!
because it doesnt use tempfiles?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|