There’s nothing like installing Oracle database when you haven’t done it for years, especially when it’s a new version. Moving from 12c to 18c is very similar (no they haven’t missed out 5 versions, they jumped straight to 18). It’s
How to find out which data is in the the SYSTEM tablespace
Sometimes objects can find their way into the system tablespace when they should be in a difference tablespace. Here’s how to see if there are any of these objects that need moving. select owner, segment_name from dba_segments where tablespace_name =
How to find out how much space is left in each tablespace
Find out how much space is left in each tablespace. Select tablespace_name, sum(bytes) bytes From dba_free_space Group by tablespace_name;
How to find the location of the online redo log files
Get online Redo Log files. SELECT GROUP#, MEMBER FROM sys.v$logfile;
How to list init.ora settings
V$ script to get INIT.ORA settings. select name, value, isdefault, isses_modifiable, issys_modifiable from v$parameter order by name
How to find the hit ratio percentage
Hit ratios below 90-95% are usually a sign of poor indexing, BUT Distortion of the hit ratio numbers is possible select (1-(sum(decode(name, ‘physical reads’, value,0))/ (sum(decode(name, ‘db block gets’, value,0)) + (sum(decode(name, ‘consistent gets’, value,0)))))) * 100 “Read Hit Ratio”
How big is your database?
/* How big is the DB ? */ compute sum of bytes on report break on report Select tablespace_name, sum(bytes) bytes From dba_data_files Group by tablespace_name;
How to check your tablespace setup
select tablespace_name, initial_extent, next_extent, pct_increase, min_extlen from dba_tablespaces where (initial_extent not in (160*1024, 5120*1024, 160*1024*1024) or next_extent != initial_extent or pct_increase != 0 or min_extlen != initial_extent) and contents = ‘PERMANENT’ and tablespace_name!=’SYSTEM’ and tablespace_name not in (select tablespace_name from
How to get the data dictionary cache miss ratio
/* Data dictionary cache miss ratio. Looking for a good percentage hit rate. */ select sum(gets) “Gets”, sum(getmisses) “Misses”, (1 – (sum(getmisses) / (sum(gets) + sum(getmisses))))*100 “HitRate” from v$rowcache;