Oracle

Oracle Indexes

Oracle Indexes

Index creation Create an index online in parallel 1 CREATE INDEX <SCHEMA>.<NAME> ON <SCHEMA>.<TABLE>(<COLUMN>,<COLUMN>,...) ONLINE PARALLEL X; Example: 1 CREATE INDEX EXAMPLE.MYINDEX ON EXAMPLE.MYTABLE(COLUMN1,COLUMN2) ONLINE PARALLEL 8; Find indexes on table 1 2 3 4 5 6 column INDEX_NAME format a30 column TABLE_OWNER format a30 column TABLE_NAME format a30 column UNIQUENESS format a20 set linesize 120 SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS FROM ALL_INDEXES WHERE TABLE_NAME = 'XX'

Oracle SGA

Oracle SGA

Change the SGA size 1 2 alter system set sga_max_size=20480M scope=spfile; alter system set sga_target=20480M scope=spfile; Since this change only applies to the spfile, you have to bounce the database when done.

Oracle Undo

Oracle Undo

Query Undo Segments 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 set pagesize 1000 set linesize 200 select tablespace_name, segment_name, round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES", round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES", round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES", NO_OF_EXTENTS from ( select tablespace_name, segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS from DBA_UNDO_EXTENTS where status='ACTIVE' and tablespace_name like 'UNDOTBS%' group by tablespace_name, segment_name union select tablespace_name, segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS from DBA_UNDO_EXTENTS where status='UNEXPIRED' and tablespace_name like 'UNDOTBS%' group by tablespace_name, segment_name union select tablespace_name, segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS from DBA_UNDO_EXTENTS where status='EXPIRED' and tablespace_name like 'UNDOTBS%' group by tablespace_name, segment_name ) group by tablespace_name, segment_name, NO_OF_EXTENTS having NO_OF_EXTENTS >= 30 order by 5 desc; Example Query Output

Oracle Tablespace

Oracle Tablespace

The following are statements and queries I use working with tablespaces in Oracle. Query Tablespace Usage ⚠️ Please be aware of the following: If this query runs really slow, it may indicate you need to empty your recycle bin You should not run this query frequently on large databases as it’s not a lightweight query 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 set tab off set linesize 200 set pagesize 100 select files.