Skip to content

Oracle

  • in Oracle
  • 1 min read

Locks Management

1. Gather locks for current session

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT
  (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
  a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
 ' is blocking ',
  (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
  b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial
FROM 
  v$lock a, v$lock b
WHERE 
  a.block = 1
  AND b.request > 0
  AND a.id1 = b.id1
  AND a.id2 = b.id2;
  • in Oracle
  • 1 min read

Database size

1. Full size of the database

1
SELECT  SUM(bytes/1024/1024/1024) FROM dba_segments

2. Size by tablespace

1
2
3
SELECT  OWNER, SUM(bytes/1024/1024/1024) FROM dba_segments 
GROUP BY owner
order BY owner;

3. Fragmented space for a schema

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT 
  table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
  round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
  round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
  round((round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100,2) "percentage"
FROM 
  all_tables 
WHERE 
  owner='<OWNER>' 
  AND blocks>0;
  • in Oracle
  • 1 min read

Transport SQL profile from one base to another

1. List all profiles to retrieve the profile name

1
SELECT * FROM DBA_SQL_PROFILES;

2. Create table with DBMS to export profile

1
2
3
4
5
6
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
    table_name  => 'TMP_SQL_PROFILES'
,   schema_name => 'SYSTEM'
);
END;

3. Export profile to the temporary table

1
2
3
4
5
6
7
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
    profile_name         => 'SYS_SQLPROF_02701ab7b57c0000'
,   staging_table_name   => 'TMP_SQL_PROFILES'
,   staging_schema_owner => 'SYSTEM'
);
END;
Export/Import this table between both databases

4. Import profile from temporay table in the target database

1
2
3
4
5
6
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
     replace            => true
,    staging_table_name => 'TMP_SQL_PROFILES'
);
END;
  • in Oracle
  • 1 min read

Statistics Management

1. Gather statistics for a table

1
2
3
4
BEGIN
 DBMS_STATS.GATHER_TABLE_STATS (ownname => '<OWNER>' , tabname => '<TABLE_NAME>',
  cascade => true, estimate_percent => 15,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);
END;

2. Gather statistics for a schema

1
2
3
4
5
6
7
exec dbms_stats.gather_schema_stats( -
    ownname          => '<SCHEMA_NAME>', -
    options          => 'GATHER AUTO', -
    estimate_percent => dbms_stats.auto_sample_size, -
    method_opt       => 'for all columns size repeat', -
    degree           => 15 -
)