DBA NAVIGATOR

🧠 Understanding and Managing SQL Profiles in Oracle SQL Profiles in Oracle are powerful tuning tools generated by the SQL Tuning Advisor. They help the optimizer produce better execution plans by supplying additional statistics or corrected cardinality estimates — all without changing the original SQL text. Unlike SQL Plan Baselines or Hints, SQL Profiles do not lock down a specific plan. Instead, they allow the optimizer to adapt and make smarter decisions based on real-time data. These profiles are especially helpful when the optimizer misestimates row counts or selects suboptimal joins. 🔧 Step-by-Step: SQL Profile Management 1️⃣ Check If a SQL Profile Is Assigned to a SQL ID SELECT DISTINCT p.name AS sql_profile_name, s.sql_id FROM dba_sql_profiles p JOIN dba_hist_sqlstat s ON p.name = s.sql_profile WHERE s.sql_id = '&sqlid'; 💡 Tip: Replace &sqlid with the actual SQL ID to see if it's linked to any profile in AWR history. 2️⃣ List...

🧱 How to Add a Temporary Tablespace in Oracle Standalone Database Temporary tablespaces in Oracle are used for sorting operations, managing global temporary tables, and handling intermediate result sets. It's a good practice to add or manage these tablespaces especially in large databases or when the default temp tablespace gets full or fragmented. In this guide, we'll walk you through a clean and safe method to create a new temporary tablespace, make it default (if needed), and drop the old one (optional). 🔧 Step-by-Step Instructions 1️⃣ Connect to the Database as SYSDBA sqlplus / as sysdba -- or sqlplus sys@ORCL as sysdba 2️⃣ Identify Existing Temporary Tablespaces SELECT tablespace_name, status, contents FROM dba_tablespaces WHERE contents = 'TEMPORARY'; 3️⃣ Create a New Temporary Tablespace CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_new01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 5G EXTENT ...