Common Oracle maintenance SQL

1. ---- query the SQL statement being executed by a session
SQL> select S. Sid, SQL _text from V $ session S, V $ SQL Q
Where S. SQL _address = Q. Address and S. SQL _hash_value = Q. hash_value
And S. Sid in (107, 44803) and S. Serial # In (53098,54004 );

SQL>
Select P. spid, SQL _text from V $ sqlarea V, V $ session S, V $ PROCESS p
Where v. hash_value = S. SQL _hash_value and V. Address = S. SQL _address
And P. ADDR = S. paddr and P. spid in (29638,29716, 28453,28693 );

2. -- Query events
SQL> select event, p1text from V $ session_wait
Where sid in (107, 44803) and SEQ # In (53098,54004 );

SQL> select * from V $ session_event where Sid = 14

--- Query index Definitions
SQL> select * From user_ind_columns where index_name = upper ('& index_name ');

-- Re-Indexing

SQL> alter index <indexname> rebuild <tablespacename>

-- Table Analysis
Analyze table slview. flowraw compute statistics for all indexes;

-- Index prompt
Select * from a where col1 = xxx;

--- View the constraints of a table
SQL> select constraint_name, constraint_type, search_condition, r_constraint_name
From user_constraints where table_name = upper ('& table_name ');

SQL> select C. constraint_name, C. constraint_type, CC. column_name
From user_constraints C, user_cons_columns CC
Where C. Owner = upper ('& table_owner') and C. table_name = upper ('& table_name ')
And C. Owner = cc. Owner and C. constraint_name = cc. constraint_name
Order by CC. position;

-- Size of the table space
Select upper (F. tablespace_name) "tablespace name", D. tot_grootte_mb "tablespace size (m)", D. tot_grootte_mb-F. total_bytes "used space (m )",
To_char (round (D. tot_grootte_mb-f. total_bytes)/d. tot_grootte_mb * 990, 2), '2017. 99 ') "usage ratio", F. total_bytes "Free Space (m)", F. max_bytes "maximum block (m )"
From
(Select tablespace_name, round (sum (bytes)/(1024*1024), 2) total_bytes, round (max (bytes)/(1024*1024), 2) max_bytes from sys. dba_free_space
Group by tablespace_name) F,
(Select dd. tablespace_name, round (sum (DD. bytes)/(1024*1024), 2) tot_grootte_mb from SYS. dba_data_files DD group by dd. tablespace_name) d
Where D. tablespace_name = f. tablespace_name order by 4 DESC;

--- Query the database lock table session
Select O. object_name, L. session_id, S. Serial #, S. program,
S. username, S. Command, S. Machine, S. lockwait from V $ locked_object L, all_objects o, V $ session s
Where o. object_id = L. object_id and S. Sid = L. session_id;

 

Alter system kill session '123 ';

---- Query Oracle jobs
Select job, what, next_date, interval, failures, broken from user_jobs;
Exec dbms_job.run (5 );

----- Table size
Select segment_name, bytes
From user_segments
Where segment_type = 'table ';
Or
Select segment_name, sum (bytes)/1024/1024 from user_extents group by segment_name
Where tablespace_name = 'user ';

Storage functions and processes

View the status of functions and processes
SQL> select object_name, status from user_objects where object_type = 'function ';
SQL> select object_name, status from user_objects where object_type = 'Procedure ';

View functions and processesSource code
SQL> select text from all_source where owner = user and name = upper ('& plsql_name ');

Patch check
$ CD $ ORACLE_HOME/opatch
$./Opatch lsinventory