Oracle

1 2 3 SELECT COUNT (*) FROM dba_objects WHERE status = 'INVALID'; 1 2 3 SELECT COUNT (*) FROM user_objects WHERE status = 'INVALID';

See all directories 1 SELECT * FROM all_directories ORDER BY directory_path; Create a new directory and grant access 1 2 CREATE OR REPLACE DIRECTORY EXPORT_DIR AS '/u01/export/'; GRANT READ, WRITE ON DIRECTORY test_dir TO XXX; Export a schema 1 expdp system/password@sid schemas=XXX directory=EXPORT_DIR dumpfile=XXX.dmp logfile=XXX.log parallel=3 Export a database 1 expdp system/password@sid full=Y directory=EXPORT_DIR dumpfile=XXX.dmp logfile=XXX.log parallel=3 Kill a expdp job

Create a user 1 CREATE USER MYUSER IDENTIFIED BY MYPASSWORD DEFAULT TABLESPACE USERS TEMPORARY TABLESPAE TEMP; Lock a user 1 ALTER USER MYUSER ACCOUNT LOCK; Unlock a user 1 ALTER USER MYUSER ACCOUNT UNLOCK; Drop a user 1 DROP USER MYUSER CASCADE; Get default tablespace for user 1 SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME = 'MYUSER'; View objects in tablespace

Force a log switch 1 ALTER SYSTEM SWITCH LOGFILE;

Run the following on the replica to see if there is Standby Lag 1 select * from v$recovery_progress; Run the following on the replica to see lag times 1 2 3 4 5 set linesize 9000 column name format a25 column value format a20 column time_computed format a25 SELECT name, value, time_computed FROM v$dataguard_stats; Run the following on the primary database to see redo logs applied

How to check free space in ASM 1 select name, state, total_mb, free_mb from v$asm_diskgroup;

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'

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.

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

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.