Oracle SQL Scripts

Bug:

invoke_procedure_in_trigger.sql - PL/SQL compiler will report the very weird error of PLS-00103 if adding a comment after the call procedure clause in a trigger

DB Design Demo:

annual_report_demo.sql - Using a simple SQL Demo of DB Design to build my Annual Report

SCN:

database_scn.sql - Checking SCN number of oracle database (via joining two number of oracle dynamic performance view v$datafile and v$datafile_header)
datafile_header_scn.sql - Checking SCN number (for both the column "checkpoint_change#" and "resetlogs_change#" via the oracle dynamic performance view v$datafile_header) of the header of data file
datafile_scn.sql - Checking SCN number (in the column "checkpoint_change#" via the oracle dynamic performance view v$datafile) of current control file

SQL Quiz:

quiz_intersect.sql - Taking a SQL Quiz for Intersect I once noticed on a place where I seem like to not remember it a few days ago

SQL Set Demos:

minus_inline_external_table.sql - Comparing the entries between two log files by SQL Set Operator "minus" after creating two separate inline external tables for those two log files in oracle database 20c

Acquiring Pool SQL:

buffer_gets_rank_top_5_sql_on_sqlstats.sql - Ranking Top 5 SQL for buffer_gets (High CPU) on "v$sqlstats" of Oracle
disk_reads_rank_top_5_sql_on_sqlstats.sql - Ranking Top 5 SQL for disk_reads (High I/O) on "v$sqlstats" of Oracle
poor_parsing_applications_rank_top_5_sql_on_sqlstats.sql - Ranking Top 5 SQL for poor parsing applications (parse_calls/executions) on "v$sqlstats" of Oracle
shared_memory_rank_top_5_sql_on_sqlstats.sql - Ranking Top 5 SQL for shared memory (Memory hogs) on "v$sqlstats" of Oracle

Active Data Guard:

check_dg_phystdby_log_apply.sql - Checking primany and physical standby's redo log on Oracle Data Guard (active) whether is applied
check_dg_redo_apply.sql - The improved version checking redo data apply on Oracle Data Guard physical standby database

ASH:

active_sessions.sql - Visualizing the oracle active sessions in the view "v$active_session_history" in the last 1 hour by the custom report of SQL Developer
active_sessions_per_session_state.sql - Visualizing the oracle active sessions per session state in the view "v$active_session_history" in the last 1 hour by the custom report of SQL Developer
active_sessions_per_wait_class.sql - Visualizing the oracle active sessions per wait class in the view "v$active_session_history" in the last 1 hour by the custom report of SQL Developer
ash_event_count_topN.sql - View the Top-N event counts from ASH
ash_event_count_topN_2.sql - The improved version of "ash_event_count_topN_new.sql"
ash_event_count_topN_new.sql - The improved version of "ash_event_count_topN.sql"

AWR Trend:

acquire_aas.sql - Acquiring Average Active Sessions (AAS) from the historical AWR reports
acquire_aas_2.sql - The 2nd version of acquiring Average Active Sessions (AAS) from the historical AWR reports
acquire_arp.sql - Visualizing the oracle some performance metrics about "CPU Time" and "Load Average" in the past and real time
acquire_arp_2.sql - The 2nd version about acquire_arp.sql, I've just used the statically converting rows to columns (including two number of methods - classic "MAX(DECODE()) ... GROUP BY" and "SELECT * FROM table_name PIVOT (MAX(column_name_1) FOR column_name_2 IN ())") to state my business logic
acquire_aspac.sql - Visualizing the oracle performance graph "Active Sessions Per Activity Class" (ASPAC) from EMCC 13.5 in last 1 minute and 1 hour
acquire_aspwc.sql - Visualizing the oracle performance graph "Active Sessions Per Wait Class" (ASPWC) from EMCC 13.5 in last 1 minute and 1 hour
acquire_assbrl.sql - Visualizing the oracle performance metrics "ASSBRL" (Average Synchronous Single-Block Read Latency) in the past and real time by the custom report of SQL Developer
acquire_clc.sql - Acquiring Current Logons Count from the historical AWR reports
acquire_cocc.sql - Acquiring Current Open Cursors Count from the historical AWR reports
acquire_col.sql - Acquiring Current OS Load from the historical AWR reports
acquire_cpu_load.sql - Acquiring CPU Load from the historical AWR reports
acquire_cpu_load_2.sql - The 2nd version of acquiring CPU Load from the historical AWR reports
acquire_cpu_usage.sql - Acquiring CPU Usage from the historical AWR reports
acquire_dbcps.sql - Acquiring DB Block Changes Per Sec from the historical AWR reports
acquire_dbcpt.sql - Acquiring DB Block Changes Per Txn from the historical AWR reports
acquire_dbgps.sql - Acquiring DB Block Gets Per Sec from the historical AWR reports
acquire_dbgpt.sql - Acquiring DB Block Gets Per Txn from the historical AWR reports
acquire_dbtime.sql - Acquiring DB time from the historical AWR reports
acquire_dbtime_2.sql - The 2nd version of acquiring DB time from the historical AWR reports
acquire_eps.sql - Acquiring Executions Per Sec from the historical AWR reports
acquire_ept.sql - Acquiring Executions Per Txn from the historical AWR reports
acquire_io_mbps.sql - Acquiring IO MBPS from the historical AWR reports
acquire_io_mbps_2.sql - The 2nd version of acquiring IO MBPS from the historical AWR reports
acquire_io_mbps_by_function.sql - Acquiring IO MBPS by Function from the historical AWR reports
acquire_iops.sql - Acquiring IOPS from the historical AWR reports
acquire_iops_2.sql - The 2nd version of acquiring IOPS from the historical AWR reports
acquire_iops_by_function.sql - Acquiring IOPS by Function from the historical AWR reports
acquire_logic_cpus_union_aas.sql - Visualizing the oracle performance metrics "NUM_CPUS" and "AAS" in the past and real time by the custom report of SQL Developer
acquire_lps.sql - Acquiring Logons Per Sec from the historical AWR reports
acquire_lps_union_tps.sql - Visualizing the oracle performance metrics "LPS" and "TPS" in the past and real time by the custom report of SQL Developer
acquire_lrps.sql - Acquiring Logical Reads Per Sec from the historical AWR reports
acquire_network_mbps.sql - Acquiring Network MBPS from the historical AWR reports
acquire_prps.sql - Acquiring Physical Reads Per Sec from the historical AWR reports
acquire_prps_union_pwps_union_rsps.sql - Visualizing the oracle performance metrics "PRPS", "PWPS" and "RSPS" in the past and real time by the custom report of SQL Developer
acquire_redo_gen_mbps.sql - Acquiring Redo Generated MBPS from the historical AWR reports
acquire_rtpt.sql - Acquiring Response Time Per Txn from the historical AWR reports
acquire_rwps.sql - Acquiring Redo Writes Per Second from the historical AWR reports
acquire_session_count.sql - Acquiring Session Count from the historical AWR reports
acquire_ssrt.sql - Acquiring SQL Service Response Time from the historical AWR reports
acquire_tps.sql - Acquiring TPS from the historical AWR reports
acquire_tps_2.sql - The 2nd version of acquiring TPS from the historical AWR reports
acquire_ucps.sql - Acquiring User Calls Per Sec from the historical AWR reports
acquire_ucpt.sql - Acquiring User Calls Per Txn from the historical AWR reports

Capacity Planning:

checking_table_growth.sql - Checking the growth of table
checking_table_growth_2.sql - The 2nd version of checking the growth of table
checking_table_used_size.sql - Focusing on checking the used size and other situations (such as, num_rows, blocks, avg_row_len and so on) of table
checking_tablespace_growth.sql - Checking the growth of tablespace
checking_tablespace_growth_2.sql - The 2nd version of checking the growth of tablespace
monitor_big_table_size.sql - Monitoring the used size of big tables by using VIEW, PROCEDURE, SCHEDULER in the schema 'monitor'

Dig IP via oracle function:

dig_ip_via_function.sql - Digging all of IP Addresses connecting to Oracle DB Server via pre-created function "resolveHost"

Dig IP via oracle trigger:

dig_ip_via_trigger.sql - Digging all of IP Addresses connecting to Oracle DB Server via pre-created trigger "on_logon_trigger"

Dig listener log:

dig_ip_via_listener_log_xml.sql - Digging real IP Address from the "XML" format of listener log file "log.xml"
dig_ip_via_listener_log_xml_2.sql - The 2nd version of the prior SQL script "dig_ip_via_listener_log_xml.sql", the sole distinguish is this time I use "*" (using "NEWLINE" on 1st version) as a record delimited character when I create that external table

Expdp:

expdp_exclude_stats.sql - Simulate the circumstance of adding this parameter "statistics=none" or "exclude=statistics" at the end of a usual EXPDP command

Grant:

bgs_role_syn.sql - Batch grant (only) select privilege on specific user (prod)'s all of tables to a new role (prod) and then grant this role to new user (qwz)
bgs_role_syn_tab.sql - Batch grant (only) select privilege on specific user (prod)'s all of tables to a new role (prod) and then grant this role to new user (qwz), at the same time it could also query out schema (prod)'s all of table names on schema (qwz)
bgs_role_syn_tab_2.sql - The 2nd version of 'bgs_role_syn_tab.sql', which use a materialized view 'u_tables' to accomplish the same function
bgs_role_syn_tab_3.sql - Grant (only) select privilege on specific user (prod)'s tables T1 to a new role (bbs) and then grant this role to new user (qwz). At the same time it could also query out table T1's latest data on schema (qwz)
bgs_scheduler.sql - Regularly refresh view "u_tables" being created via running SQL script "bgs_role_syn_tab_2.sql"
bth_grt_sel.sql - Batch grant (only) select privilege on specific user's all of tables to a new user 'qwz'

Migration Compare:

migration_before_and_after_compare.sql - Comparing all of tables' total numbers (before and after migration) on all of production users

Materialized View:

materialized_view_demo.sql - Creating a demo of oracle materialized view on 'TEST' schema, by the way guiding you how to periodically (via using an oracle job) and manually refresh it

PLSQL:

dyn_crt_table.sql - Using to dynamically create a test table via substitution variable of SQL*Plus on dyn_crt_table subdir
dyn_crt_table_2.sql - Using to dynamically create a test table via *ACCEPT* command of SQL*Plus on dyn_crt_table subdir
dyn_crt_table_3.sql - Using to dynamically create a test table via using a concatenation string "||" on dyn_crt_table subdir
dyn_crt_table_4.sql - Using to dynamically create a test table via using a q/Q delimiter, e.g q'[...]' or Q'[...]' on dyn_crt_table subdir
bgs_role_syn_2.sql - The 2nd version of 'bgs_role_syn.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/grant/bgs_role_syn.sql on grant subdir
bgs_role_syn_3.sql - The 3rd version of 'bgs_role_syn.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/grant/bgs_role_syn.sql on grant subdir
bth_grt_sel_2.sql - The 2nd version of 'bth_grt_sel.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/grant/bth_grt_sel.sql on grant subdir
bth_grt_sel_3.sql - The 3rd version of 'bth_grt_sel.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/grant/bth_grt_sel.sql on grant subdir
identity_column_compare.sql - Comparing comsuing time (and cpu time) by using 3 different identity column in 3 different tables to insert some dummy data into those tables, you can see here on performance_compare subdir
insert_approach_compare.sql - Comparing spending time (and cpu time) when using 3 number of different approaches to insert some data into a table, you can see here on performance_compare subdir
compare_plsql_output.sql - Comparing the output result of two types of PLSQL code - https://stevenfeuersteinonplsql.blogspot.com/2019/11/plsql-puzzle-what-code-can-be-removed.html on puzzle_plsql subdir
compare_plsql_output_2.sql - The 2nd version of SQL script "compare_plsql_output.sql" which has been simplified by still using anonymous PLSQL block, this means that my processing flow will become simple on puzzle_plsql subdir
string-indexed_collection.sql - A quick little #PLSQL puzzle written by Steven Feuerstein (Oracle) on Twitter on Dec 10, 2019 on puzzle_plsql subdir
brs_role_syn_2.sql - The 2nd version of 'brs_role_syn.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/revoke/brs_role_syn.sql on revoke subdir
brs_role_syn_3.sql - The 3rd version of 'brs_role_syn.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/revoke/brs_role_syn.sql on revoke subdir
bth_rvk_sel_2.sql - The 2nd version of 'bth_rvk_sel.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/revoke/bth_rvk_sel.sql on revoke subdir
bth_rvk_sel_3.sql - The 3rd version of 'bth_rvk_sel.sql' you can see here - https://github.com/guestart/Oracle-SQL-Scripts/blob/master/revoke/bth_rvk_sel.sql on revoke subdir
brgs_role_syn_tab.sql - Creating or replacing a user-defined procedure 'brgs_role_syn_tab' on schema SZD_BBS_V2
brgs_role_syn_tab_2.sql - The 2nd version of 'brgs_role_syn_tab.sql', on this version I simplify my user-defined procedure 'brgs_role_syn_tab_2' based on 'brgs_role_syn_tab' on schema SZD_BBS_V2
brgs_role_syn_tab_3.sql - The 3rd version of 'brgs_role_syn_tab.sql', on this version I create a materiralzed view "u_tables" on my user-defined procedure "brgs_role_syn_tab_3" on grantor schema SZD_BBS_V2
brst2_scheduler.sql - Creating a user-defined job 'BRST2_JOB' on schema SZD_BBS_V2, the primary intention is it could regularly/periodically execute my procedure 'brgs_role_syn_tab_2' on schema SZD_BBS_V2
brst3_scheduler.sql - Creating a user-defined job 'BRST3_JOB' on schema SZD_BBS_V2, the primary intention is it could regularly/periodically execute my procedure 'rgy_refresh_mview_uts' on schema SZD_BBS_V2
brst_scheduler.sql - Creating a user-defined job 'BRST_JOB' on schema SZD_BBS_V2, the primary intention is it could regularly/periodically execute my procedure 'brgs_role_syn_tab' on schema SZD_BBS_V2
collect_empty_dba_hist_tables.sql - Collecting empty DBA_HIST_ tables in different oracle versions
rgy_refresh_mview_uts.sql Regularly refreshing MView "u_tables" created by procedure "brgs_role_syn_tab_3" from the SQL script "brgs_role_syn_tab_3.sql"
switch_redo_log_for_recycle.sql - Switching all of online redo log for a recycle on oracle database

Recent Metrics:

acquire_recent_aas.sql - Acquiring the recent Average Active Sessions (AAS) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_clc.sql - Acquiring the recent Current Logons Count (CLC) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_cocc.sql - Acquiring the recent Current Open Cursors Count (COCC) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_col.sql - Acquiring the recent Current OS Load (COL) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_cpu_load.sql - Acquiring the recent CPU Load from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_cpu_usage.sql - Acquiring the recent CPU Usage from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbcps.sql - Acquiring the recent DB Block Changes Per Sec from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbcpt.sql - Acquiring the recent DB Block Changes Per Txn from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbcpu_time_ratio.sql - Acquiring the recent Database CPU Time Ratio from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbgps.sql - Acquiring the recent DB Block Gets Per Sec from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbgpt.sql - Acquiring the recent DB Block Gets Per Txn from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbtime.sql - Acquiring the recent Database Time from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_dbwait_time_ratio.sql - Acquiring the recent Database Wait Time Ratio from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_eps.sql - Acquiring the recent Executions Per Sec from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_ept.sql - Acquiring the recent Executions Per Txn from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_io_mbps.sql - Acquiring the recent IO MBPS from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_iops.sql - Acquiring the recent IO(Requests)PS from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_lps.sql - Acquiring the recent Logons Per Second (LPS) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_lrps.sql - Acquiring the recent Logical Reads Per Sec (LRPS) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_network_mbps.sql - Acquiring the recent Network MBPS from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_prps.sql - Acquiring the recent Physical Reads Per Sec from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_redo_gen_mbps.sql - Acquiring the recent Redo Generated MBPS from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_rtpt.sql - Acquiring the recent Response Time Per Txn from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_rwps.sql - Acquiring the recent Redo Writes Per Second (RWPS) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_session_count.sql - Acquiring the recent Session Count from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_ssrt.sql - Acquiring the recent SQL Service Response Time from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_tps.sql - Acquiring the recent Transactions Per Second (TPS) from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_ucps.sql - Acquiring the recent User Calls Per Sec from the Oracle DPV v$sysmetric_history and v$sysmetric_summary
acquire_recent_ucpt.sql - Acquiring the recent User Calls Per Txn from the Oracle DPV v$sysmetric_history and v$sysmetric_summary

Revoke:

brs_role_syn.sql - Revoke new role (prod) from new user (qwz) to whom if (once) being granted on schema 'SYS'
brs_role_syn_tab.sql - Revoke new role (prod) from new user (qwz) to whom if (once) being granted on schema 'SYS', furthermore revoke select privilege on new role (prod) and drop this role
brs_role_syn_tab_2.sql - The 2nd version of 'brs_role_syn_tab.sql'
brs_role_syn_tab_3.sql - The 3rd version of 'brs_role_syn_tab.sql'
bth_rvk_sel.sql - Batch revoke (only) select privilege on specific user's all of tables from a new user 'qwz' whom if being granted to

Routine Inspection:

all_prod_user.sql - Listing all of production users by dba_users (excluding sys related users)
break_compute_demo.sql - Breaking (SQL*Plus command) tablespace_name and computing (SQL*Plus command) dropped size based on recyclebin object "BIN$..." existing in Oracle Static Data Dictionary View "dba_segments"
check_non_default_parameter.sql - Checking whether there are some non-default parameters on Oracle database
check_total_size_oracle_db.sql - Checking total sizes of Oracle database
connect_machine_via_sql_id.sql - Checking the machine name connecting to Oracle Database Server via inputting a specific value of SQL_ID
ctl_file_path_in_rman_backupsets.sql - Listing all of control file's locaiton in rman backupsets
db_buffer_cache_hit_ratio.sql - Displaying cache hit ratio for Oracle database
dropped_object_of_recyclebin.sql - Getting some dropped objects (such as TABLE, INDEX, SEQUENCE) from recyclebin via checking static data dictionary (SDD) "DBA_RECYCLEBIN" on Oracle Database
get_ddl_of_object_via_passing_in_arguments.sql - Getting DDL statement of an object (such as TABLE, INDEX, SEQUENCE, VIEW, FUNCTION and PROCEDURE) via calling SQL Script meanwhile passing in some arguments on Oracle Database
get_ddl_of_object_via_using_accept.sql -Getting DDL statement of an object (such as TABLE, INDEX, SEQUENCE, VIEW, FUNCTION and PROCEDURE) via using "accept" of SQL*Plus command on Oracle Database
get_ddl_of_object_via_using_substitution_variable.sql - Getting DDL statement of an object (such as TABLE, INDEX, SEQUENCE, VIEW, FUNCTION and PROCEDURE) via using substitution variable of SQL*Plus on Oracle Database
get_dyn_perf_view_def.sql - Getting the definition of dynamic performance view on Oracle Database
get_dyn_perf_view_def_2.sql - The 2nd version of SQL script "get_dyn_perf_view_def.sql" - using "accept" of SQL*Plus command on Oracle Database
get_dyn_perf_view_def_3.sql - The 3rd version of SQL script "get_dyn_perf_view_def.sql" - calling SQL Script "get_dyn_perf_view_def_3.sql" meanwhile passing in argument on Oracle Database
hit_ratio_db_buffer_cache.sql - Displaying db buffer cache hit ratio for Oracle database
hit_ratio_db_buffer_cache_2.sql - The 2nd version displaying db buffer cache hit ratio for Oracle database
hit_ratio_db_buffer_cache_3.sql - The 3rd version displaying db buffer cache hit ratio for Oracle database
per_machine_act_conn_num_aggr_by_user.sql - Showing per machine's active connect numbers after aggregating by username on v$session, meanwhile showing column client_info, that's to say, client's ip address
rman_backup_check.sql - Displaying rman backup situation for Oracle database
rman_backup_check_2.sql - The 2nd version displaying rman backup situation for Oracle database
rman_backup_check_3.sql - The 3rd version displaying rman backup situation for Oracle database
rman_backup_check_4.sql - The 4th version displaying rman backup situation for Oracle database
rman_backup_check_plsql_1.sql - The 1st version displaying rman backup situation for Oracle database by calling common explicit cursor (open ... fetch ... close) on PL/SQL code
rman_backup_check_plsql_2.sql - The 2nd version displaying rman backup situation for Oracle database by calling implicit cursor (for ... in ...) on PL/SQL code
spfile_path_in_rman_backupsets.sql - Listing all of spfile's locaiton in rman backupsets
tablespace_free_space.sql - Checking the free space of tablespaces (including Data and Temp) on Oracle Database
tablespace_non-temp_compare_total_size.sql - Comparing the difference about total size (using more than one INLINE VIEW) of all of the non-temp tablespaces on Oracle Database
tablespace_non-temp_compare_total_size_simple_version.sql - Comparing the difference about total size (using simple version) of all of the non-temp tablespaces on Oracle Database
tablespace_non-temp_compare_total_size_with_as.sql - Comparing the difference about total size (using WITH ... AS ...) of all of the non-temp tablespaces on Oracle Database
tablespace_non-temp_recyclebin_rollup_segment_name.sql - Checking the per blocks number (or dropped size) and its SUM by ROLLUP (segment_name) on non-temp tablespaces of Oracle Database
tablespace_per_used_size_and_rollup.sql - Checking the used size of per tablespace (and all) using "rollup" clause on Oracle Database
tablespace_per_used_size_and_total_size.sql - Checking the used size of per tablespace (and all) on Oracle Database
tablespace_used_size_1.sql - The 1st version Checking the used size of tablespace on Oracle Database
tablespace_used_size_2.sql - The 2nd version Checking the used size of tablespace on Oracle Database
tablespace_utilization_rate.sql - Checking the utilization rate of all of the tablespace on Oracle Database
tablespace_utilization_rate_2.sql - The 2nd (relatively simple) version of SQL script "tablespace_utilization_rate.sql" - using view both "sys.sm$ts_avail" and "sys.sm$ts_free" to check the utilization rate of non-Temporary tablespace
temporary_tablespace_used_size.sql - Checking the used size of all of TEMPORARY tablespaces on Oracle Database
temporary_tablespace_used_size_2.sql - The 2nd version of SQL script "temporary_tablespace_used_size.sql" on Oracle Database
top_10_segment_on_sysaux_tbs.sql - Showing top 10 segment objects on sysaux tablespace
top_10_segment_on_system_tbs.sql - Showing top 10 segment objects on system tablespace

Scheduler:

scheduler_demo.sql - Check running situation of oracle scheduler/job
user_scheduler_job_log.sql - Check the executing/running situation of the oracle scheduer/job log on 'TEST' schema
user_scheduler_jobs.sql - Checking the some information of the oracle scheduer/job on 'TEST' schema

SQL Tuning:

check_data_dictionary_tables_and_views.sql - Check data dictionary tables and views of Oracle
check_sql_execution_plan_table.sql - Check the SQL statement's execution plan
check_sql_multiple_execution_plans.sql - Check SQL multiple execution plans
check_sql_multiple_execution_plans_2.sql - Check SQL multiple execution plans-2
like_expression.sql - Optimize the SQL statement with LIKE expression on Oracle Database
like_expression_2.sql - The 2nd version of like_expression.sql, which will focus on talking about these two cases: "%qw" and "q%w"
optimize_query_null_value.sql - Optimize the SQL query of "NULL" value
pagination_query_ascending_index.sql - Observing the execution plan of top-N and pagination query on Oracle Database via calling DBMS_XPLAN.display_cursor()
pagination_query_bug.sql - Observing the execution plan of top-N and pagination query on Oracle Database via setting autotrace traceonly
topN_query_descending_index(bug).sql - Observing the execution plan of top-N (20) query on Oracle Database via calling DBMS_XPLAN.display_cursor()
user_index_columns.sql - Checking the related index columns info by inputting a table name when using SQL*Plus to connect to a user on Oracle Database
user_index_expressions.sql - Checking the related index expressions on several columns by inputting a table name when using SQL*Plus to connect to a user on Oracle Database
user_indexes.sql - Checking the related indexes info by inputting a table name when using SQL*Plus to connect to a user on Oracle Database

Statistics Info:

all_tables_mods_on_all_proc_users.sql - Check DML of all of tables from all of production users
all_tables_stats_on_all_proc_users.sql - Check statistics of all of tables from all of production users
table_column_statistics.sql - Check some related statistics of column of table
table_mods_on_proc_user.sql - Only check modifications of table or user which has been appointed
table_stats_on_proc_user.sql - Only check statistics of table or user which has been appointed