Finding the user name of a Table:
select owner, table_name
from all_tables where table_name like 'Table_name';
select B.FILE_NAME from
dba_segments a, dba_data_files b
where
a.header_file=b.file_ID and segment_name='Table_Name';
Finding Tablespace name and their properties:
select TABLESPACE_NAME,
INITIAL_EXTENT, NEXT_EXTENT,
MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS,
CONTENTS from
dba_tablespaces order by TABLESPACE_NAME;
Finding temporary table spaces:
SELECT TABLESPACE_NAME,
BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;
Finding temporary tablespace of a user:
select
username,temporary_tablespace from dba_users where username like 'Table_Name';
Finding temporary tablespace file properties:
select * from
v$tempfile;
SELECT file_name, bytes,
status, autoextensible FROM dba_temp_files;
Finding default temp tablespace of the database:
SELECT * FROM
DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
Finding max size of the table spaces:
select tablespace_name,
max_size from dba_tablespaces;
Finding asm group disk information on database:
Properties of asm disk group:
select * from
v$asm_diskgroup;
Properties of asm file:
select * from
v$asm_file;
Properties of asm disk:
select * from
v$asm_disk;
Active operations on asm instance:
select * from
v$asm_operation;
Monitoring asm disk group:
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100,
2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
