INSTALLATION DOCUMENTS BY RAVI

Tuesday, April 25, 2017

Important SQL Queries

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
/


No comments:

Post a Comment

SQL query to find whether OdiSendMail is configured or not ODI 12c

In the SQL developer connect to the ODI work repository user (Ex: DEV_ODI_REPO) and run the below query. select SCEN_NAME,TASK_NAME3,DE...