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
annual_report_demo.sql - Using a simple SQL Demo of DB Design to build my Annual Report
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
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
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
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
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_event_count_topN.sql - View the Top-N event counts from ASH ash_event_count_topN_new.sql - The improved version of "ash_event_count_topN.sql" ash_event_count_topN_2.sql - The improved version of "ash_event_count_topN_new.sql"
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_clc.sql - Acquiring Current Logons Count 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_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_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_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_lps.sql - Acquiring Logons Per Second from the historical AWR reports acquire_network_mbps.sql - Acquiring Network MBPS from the historical AWR reports acquire_redo_gen_mbps.sql - Acquiring Redo Generated MBPS from the historical AWR reports acquire_rwps.sql - Acquiring Redo Writes Per Second 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
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_function.sql - Digging all of IP Addresses connecting to Oracle DB Server via pre-created function "resolveHost"
dig_ip_via_trigger.sql - Digging all of IP Addresses connecting to Oracle DB Server via pre-created trigger "on_logon_trigger"
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_exclude_stats.sql - Simulate the circumstance of adding this parameter "statistics=none" or "exclude=statistics" at the end of a usual EXPDP command
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_before_and_after_compare.sql - Comparing all of tables' total numbers (before and after migration) on all of production users
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
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 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
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_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_dbcpu_time_ratio.sql - Acquiring the recent Database CPU Time Ratio 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_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_network_mbps.sql - Acquiring the recent Network MBPS 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_rwps.sql - Acquiring the recent Redo Writes Per Second (RWPS) 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
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
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_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
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
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