Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How do I view Oracle database datafile properties?

GBR
Regular Advisor

How do I view Oracle database datafile properties?

I'm running Oracle 9i on HP-UX 11i v1. I need to determine the properties of the current database datafiles. How can I do that?

GBR
6 REPLIES
Hein van den Heuvel
Honored Contributor

Re: How do I view Oracle database datafile properties?

Please define 'properties'?
Size? Free space? Filespecs? --> Ask Oracle with SQLplus
Protection? Physical device? Dates? -->
Ask HPUX after askign SQL for a list of files.

Here is a trivial SQL script to get you going:

column type format a4
column Tablespace format a15
column file format a45
column id format 99
column mb format 999999
set pages 9999
set heading off
set FEEDBACK off
select 'Redo', 'group ' || l.group# "Tablespace", l.group# "Id", l.bytes/(1024*1024) "MB",
MEMBER "File" from v$logfile f, v$log l where l.group# = f.group#
union
select 'Data' "Type", tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_data_files
union
select 'Temp' "Type", tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_temp_files
union
select 'Ctrl' "Type", 'Control_file' "Tablespace", rownum "Id", 0 "MB",
name "File" from v$controlfile
order by 1,2
/


Good luck,
Regards,

Hein van den Heuvel.
GBR
Regular Advisor

Re: How do I view Oracle database datafile properties?

I guess I could have been a bit more specific with my question. I'm looking for the autoextend properties of the datafiles within my database, before I go and start modifying the properties on them.

Thanks,
GBR
Hein van den Heuvel
Honored Contributor

Re: How do I view Oracle database datafile properties?

Hmm, if you do not know to take that example presented and exten it to display the required propery, then yo might not be in the right position to make modifications to it.

Just do a
SQL> DESCRIBE DBA_DATA_FILES;
You'll see a column called 'AUTOEXTENSIBLE'.

Just add to the example and voila.

Good luck!
Hein.

Eric Antunes
Honored Contributor

Re: How do I view Oracle database datafile properties?

Hi GBR,

You can also see a windoze view of autoextending datafiles in Oracle Enterprise Manager -> Databases [select the instance] -> Storage -> Tablespaces -> Datafiles

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: How do I view Oracle database datafile properties?

hi GBR,

You can query the dba_data_files data dictionary for more information.

An example query is:
SELECT file_name, ROUND (BYTES / 1024 / 1024, 2), blocks, autoextensible,
NVL (increment_by, 0), maxbytes, maxblocks, status, maxblocks, file_id
FROM SYS.dba_data_files

The different columns that can be queried are:
FILE_NAME
FILE_ID
TABLESPACE_NAME
BYTES
BLOCKS
STATUS
RELATIVE_FNO
AUTOEXTENSIBLE
MAXBYTES
MAXBLOCKS
INCREMENT_BY
USER_BYTES
USER_BLOCKS
ONLINE_STATUS

What other information do you require?

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
GBR
Regular Advisor

Re: How do I view Oracle database datafile properties?

Thanks, I have what I need now to poke around.