DB Notes

 Oracle Tuning Advisor is a tool that can help DBA's to improve certain SQL queries. Here how you can use them: CREATE  DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '238djik018ja1', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 600, task_name => '238djik018ja1_tuning_task',  description => 'Tuning task for sql id 238djik018ja1');  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXECUTE EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '238djik018ja1_tuning_task'); GETTING REPORT select dbms_sqltune.report_tuning_task('238djik018ja1_tuning_task') from dual; QUERY TASKS SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG; DROP execute dbms_sqltune.drop_tuning_task('238djik018ja1_tuning_task');

Tip 1: Using REGEXP_LIKE (Oracle 11g+) Worse:  select * from table where a like '%x%' or a like '%y%' or a like '%z%'; Better:  select * from table where regexp_like('x|y|z'); Tip 2: Generating Multiple Columns Worse:  select 'abc' from dual  union all  select 'bcd' from dual  union all  select 'xyz' from dual; Better:  select column_value from table(sys.odcivarchar2tolist('abc','bcd','xyz'));

Sometimes if base object of a synonym is dropped without dropping synonym, ORA-00980 error may occur. For the fix, you can search related SCHEMA for synonyms that doesn't have its base objects with the PL/SQL codeblock below:

 You can see the python code below. It's suitable for RHEL/Centos/OEL servers: import subprocess import datetime import sys output = subprocess.check_output(["rpm","-qa","kernel-uek*"]) output = output.decode().strip().split("\n") output.sort(key=lambda x: subprocess.check_output(["rpm","-q","--queryformat","%{INSTALLTIME}",x])) output.reverse() active_kernel=subprocess.check_output(["uname","-r"]).decode().strip() active_kernel="kernel-uek-"+active_kernel #print(active_kernel) #print(output[0]) if active_kernel == output[0]:     print("Aktif kernel en güncel sürümdedir.")     sys.exit(0) else:     most_recent_install_date = subprocess.check_output(["rpm","-q", "--queryformat","%{INSTALLTIME}",output[0]])     most_recent_install_date = datetime.datetime.fromtimestamp(int(most_recent_install_date))     active_install_date = s...

Initial Steps Summary Let’s sum up the initial steps, then dive into key points: Ensure that the primary and replica servers have proper firewall configurations. Both servers should be able to communicate over PostgreSQL service ports (default: 5432 ). Edit pg_hba.conf on the Primary Database by adding the following line to allow the replica server to connect: host replication rep_user /32 scram-sha-256 Create a replica user on the Primary Database with the following command: createuser -U postgres rep_user -P --replication -p Edit the postgresql.conf file on the Primary Database: wal_keep_size = 10000 MB max_slot_wal_keep_size = 10000 MB Note: Adjust these values based on your needs. For a high data load, increase the value. For a low data load, decrease it. The above values assume that, at most, 10GB of data might fail to be sent during an outage. Key Points 1. Handling Large Databases For large databases, replication may take hou...

SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD ' || ' TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_INDEXES WHERE STATUS='UNUSABLE' UNION SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_IND_PARTITIONS WHERE STATUS='UNUSABLE' UNION SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME || ' REBUILD SUBPARTITION '||SUBPARTITION_NAME|| ' TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_IND_SUBPARTITIONS WHERE STATUS='UNUSABLE';

 Sometimes in production servers, you might need to check specific record(s) in order get older versions. Oracle has Flashback technology to revert your datas to a specific SCN or timestamp. In order to use it, Flashback option should be enabled. After enabling, Oracle will start to save tables at specific times. select * from <table_name> as of timestamp(sysdate - interval '10' minute) The query above shows you the table's older version. You can also narrow results with WHERE clause: select * from <table_name> as of timestamp(sysdate - interval '10' minute) where proc_id=3843