topic Re: How do I view Oracle database datafile properties? in Operating System - HP-UX

How do I view Oracle database datafile properties?

GBR — Wed, 24 Oct 2007 13:15:36 GMT

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

Re: How do I view Oracle database datafile properties?

Hein van den Heuvel — Wed, 24 Oct 2007 19:17:06 GMT

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.

Re: How do I view Oracle database datafile properties?

GBR — Wed, 24 Oct 2007 20:05:30 GMT

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

Re: How do I view Oracle database datafile properties?

Hein van den Heuvel — Wed, 24 Oct 2007 20:46:17 GMT

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.

Re: How do I view Oracle database datafile properties?

Eric Antunes — Thu, 25 Oct 2007 05:09:43 GMT

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

Re: How do I view Oracle database datafile properties?

Yogeeraj_1 — Thu, 25 Oct 2007 23:19:44 GMT

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

Re: How do I view Oracle database datafile properties?

GBR — Thu, 27 Nov 2008 15:19:25 GMT

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