Size of Oracle Database

select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Size in GB” from dual;

Read More

Display the columns related to redaction in Oracle 12c

SELECT object_owner, object_name, column_name, function_type, function_parameters, regexp_pattern, regexp_replace_string, regexp_position, regexp_occurrence, regexp_match_parameter, column_description FROM redaction_columns WHERE object_owner = DECODE(UPPER(‘&1’), ‘ALL’, object_owner, UPPER(‘&1’)) AND object_name = DECODE(UPPER(‘&2’), ‘ALL’, object_name, UPPER(‘&2’)) ORDER BY 1, 2, 3;

Read More

Number of archived redo by day

SELECT TRUNC(first_time) AS day, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb FROM v$archived_log WHERE TRUNC(first_time) >= TRUNC(SYSDATE) – &1 GROUP BY TRUNC(first_time) ORDER BY TRUNC(first_time);

Read More

Shows differences in table structures

select a.column_name, a.data_type, a.data_length, a.data_scale,a.data_precision, b.column_name, b.data_type, b.data_length, b.data_scale,b.data_precision from user_tab_columns a, user_tab_columns b where a.table_name = upper(‘&table1’) and b.table_name = upper(‘&table2’) and a.column_name = b.column_name and ( a.data_type b.data_type or a.data_length b.data_length or a.data_scale b.data_scale or a.data_precision b.data_precision );

Read More

Top Sql in Exadata

SELECT * FROM ( SELECT sql_id, executions, physical_read_bytes FROM v$sqlstats WHERE io_cell_offload_eligible_bytes = 0 ORDER BY physical_read_bytes DESC ) WHERE ROWNUM <= 10 /

Read More

Show Exadata cell versions

SELECT cellname cv_cellname , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/objective/text()’) AS VARCHAR2(20)) objective , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/status/text()’)    AS VARCHAR2(15)) status , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/name/text()’)      AS VARCHAR2(30)) interdb_plan , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/catPlan/text()’)   AS VARCHAR2(30)) cat_plan , CAST(extract(xmltype(confval), ‘/cli-output/interdatabaseplan/dbPlan/text()’)    AS VARCHAR2(30)) db_plan FROM v$cell_config  WHERE conftype = ‘IORM’ ORDER BY cv_cellname /

Read More