How do I view Oracle database datafile properties?

[GBR]

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

[Hein van den Heuvel]

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]

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]

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]

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

[Yogeeraj_1]

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

[GBR]

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