Hinzinfotech

The basic health checks Performing the below-mentioned queries and commands which applicable for RAC,standalone database and windows. Check free space on disk and DB uptime$ df -k DB uptime control whether database Listener is running$ lsnrctl status control whether database is running$ srvctl status database -d racdb $ sqlplus / as sysdba SQL> select name,open_mode […]

step 1: create the tuning task for the sql idSQL> declaresql_tune_task_id varchar2(100);beginl_sql_tune_task_id := dbms_sqltune.create_tuning_task (begin_snap => 41062,end_snap => 44516,sql_id => ‘2mjr0fw17sjuh’,scope => dbms_sqltune.scope_comprehensive,time_limit => 10800,task_name => ‘2mjr0fw17sjuh_tuning_task’,description => ‘tuning_in_OLBUI’);dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);end; DECLARE  sql_tune_task_id VARCHAR2(100);BEGIN  sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (    sql_id       => ‘2mjr0fw17sjuh’,    begin_snap    => 41062,    end_snap     […]

Here are the Generating statistics in the Oracle database examples below: exec dbms_stats.gather_system_stats(‘Start’);select * from sys.aux_stats$;exec dbms_stats.gather_table_stats(ownname => ‘SH’, tabname => ‘SALES’, cascade=>true);select * from dba_tab_statistics where table_name = ‘SALES’; select * from sales;select * from dba_tab_columns where table_name = ‘SALES’;exec dbms_stats.gather_system_stats(‘NOWORKLOAD’);select * from sys.aux_stats$;select * from v$sql_plan; exec dbms_stats.gather_database_stats;exec dbms_stats.gather_dictionary_stats;exec dbms_stats.gather_schema_stats(ownname => ‘SH’);exec dbms_stats.gather_table_stats(ownname […]

Q1.when you decide to tune the queries? Answer: We call these queries as “top consuming” queries. These queries do not exceed 5 percent of the total queries Or mostly it is much less.But most of the times, the top consuming queries use 80% of the total resources. This is a really huge ratio. Less than […]

1.To list long-running forms user sessions select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) fromv$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ andp.addr=s.paddr ; 2.To list inactive Sessions respective username SELECT username,count(*) num_inv_sessFROM v$sessionwhere last_call_et > 3600and username is not nullAND STATUS=’INACTIVE’group by usernameorder by num_inv_sess DESC; SELECT count(*) FROM v$session where last_call_et > 43200 and […]

In today’s digital era, cloud computing has revolutionized the way businesses operate, providing scalable, cost-effective, and reliable solutions to meet diverse IT requirements. Among the leading cloud service providers, Amazon Web Services (AWS) stands out as a powerhouse, offering a comprehensive suite of services to cater to the needs of organizations worldwide. What is AWS? […]

Export and Import using all parameters: 1.SOURCE_EDITION Parameter SQL> show userUSER is “SYS”SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to remo;Grant succeeded.SQL> alter user remo enable editions;User altered.SQL> grant create any view to remo;Grant succeeded.SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME=’REMO’; USERNAME E-REMOYSQL> col USERNAME for a10SQL> / USERNAME E REMO YSQL> conn remo/remoConnected.SQL> […]

High Water Mark management in Oracle Database: Description: ⦁ Each table is made up of extents and each extent is made up of oracle blocks – a common block size is 8k. So you have a table with 10 extents (80K). ⦁ When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward […]

Table Partition: SQL>select * from user_part_tables; SQL>select * from user_tab_partitions; SQL> select table_name,table_owner,partition_name,subpartition_count from dba_tab_partitions where table_name='<table_name>’; Oracle you can partition a table by CREATING PARTITION TABLES 1)Range Partitioning:  This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;for example, value of year. Performance is […]

PostgreSQL is an free open-source database system that supports both relational (SQL) and non-relational (JSON) queries. PostgreSQL is a back-end database for dynamic websites and web applications. PostgreSQL supports the most important programming languages: Python.