Oracle
Articles in the Oracle category (151).
Articles in the Oracle category (151).
- Case-insensitive comparisons in Oracle — Options for case-insensitive comparisons in Oracle SQL: normalising with UPPER or LOWER, regular expressions, linguistic sort settings and function-based indexes.
- Change the hostname of a Linux server hosting Oracle — Files to update in Linux, Oracle and Enterprise Manager after a hostname or IP address change.
- Check Oracle licences and feature usage — SQL queries to check licensed sessions and CPUs and the Oracle features in use.
- Check RMAN backup job status in Oracle — SQL queries to follow finished and running RMAN backup jobs, their progress and their output.
- Connecting to Oracle: environment variables, SQL*Plus and SQLcl — Cheat sheet for Oracle environment variables (Linux, Windows, AIX) and SQL*Plus and SQLcl connection syntaxes.
- Date arithmetic with SYSDATE in Oracle — Common date calculations with SYSDATE, NLS date formatting and a query on active session history.
- Disable ASLR for Oracle on Linux (ORA-00445) — Why ASLR makes Oracle background processes fail on Linux, how to check it and how to disable it.
- Drop a database with RMAN — Drop an Oracle database from the RMAN prompt, with or without its backups, and the related warnings.
- Enable and disable archivelog mode in Oracle — Check, enable and disable archivelog mode on an Oracle database, with the SQL*Plus commands.
- Extract Oracle object DDL with DBMS_METADATA — Examples of DBMS_METADATA.GET_DDL and GET_GRANTED_DDL for tables, indexes, procedures, grants and profiles.
- Find the instance, SID, database name and service name of an Oracle session — SQL queries to read the instance name, SID, database name, service name and session context, and the background processes shown by ps.
- Finding object locks and blocking sessions in Oracle — Diagnostic queries to find the session holding a lock, the locked objects and the blocked sessions in Oracle.
- Fixing the Backspace key in SQL*Plus on Linux — Fix the ^H output of the Backspace key in SQL*Plus, using stty erase or rlwrap.
- Gathering Oracle optimizer statistics with DBMS_STATS — Common DBMS_STATS calls to gather schema, database, dictionary, fixed object and system statistics, and flush the shared pool afterwards.
- How far back can Oracle Flashback Database go — Query V$FLASHBACK_DATABASE_LOG and understand db_flashback_retention_target and the fast recovery area.
- Install and decommission Oracle 12c on Linux and Windows — Prerequisites, GUI install and DBCA creation of Oracle 12c on Linux, then decommissioning steps.
- Installing ODP.NET for Power BI and checking an Oracle connection — Register the ODP.NET provider with OraProvCfg, then test the Oracle connection and listener.
- Intermittently slow Oracle query: execution plans and cardinality feedback — Diagnose an Oracle query that is sometimes fast and sometimes slow: 10046 trace, TKPROF, and the _optimizer_use_feedback hidden parameters.
- List Oracle constraints with DBA_CONSTRAINTS — Queries on DBA_CONSTRAINTS and DBA_CONS_COLUMNS to list constraints, their columns and references.
- List Oracle privileges granted to a user or role — Queries to list the system privileges, table privileges and role-inherited grants of an Oracle user, using the DBA dictionary views.
- Listing default tablespaces and data files per user — Query listing each user's default and temporary tablespace, with the data files of the default tablespace.
- Migrate an Oracle schema to another server with Data Pump — Lock the account, export with expdp, copy the files, import with impdp and check the result.
- Modify a hidden parameter in Oracle Database — Read, change in the SPFILE and verify an Oracle hidden parameter, with listing queries.
- Monitoring RMAN backup status and output with V$ views — SQL queries to check the status, progress, size and output of Oracle RMAN backup jobs.
- Monitoring the Oracle shared pool — Queries to check free memory in the Oracle shared pool, flush it and inspect its chunks.
- ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired — Find locked objects, kill the blocking session and set DDL_LOCK_TIMEOUT to deal with ORA-00054.
- ORA-01555 snapshot too old: sizing UNDO_RETENTION and the UNDO tablespace — Understand ORA-01555, calculate a suitable UNDO_RETENTION value and identify the sessions consuming undo space.
- ORA-01652: unable to extend temp segment in tablespace TEMP — Diagnose and fix ORA-01652: check tempfiles, grow the TEMP tablespace and identify the sessions consuming sort space.
- ORA-03113 at startup after changing SGA_MAX_SIZE — How to recover from ORA-03113 at startup after lowering sga_max_size in an spfile, by editing a pfile and recreating the spfile.
- ORA-03113 at startup: full recovery area — Find the first error in the alert log with ADRCI, then handle a full Oracle recovery area.
- ORA-04030 with bulk collect: memory workarounds — Two workarounds for ORA-04030 during bulk collect on Linux: max_map_count and the realfree heap.
- ORA-04036: PGA memory exceeds PGA_AGGREGATE_LIMIT — Understand PGA_AGGREGATE_LIMIT and its default limit in 12c, and two ways to set it when ORA-04036 terminates sessions.
- ORA-19809 when creating an archived log — Check the fast recovery area usage, enlarge it, and free space by backing up and deleting archived logs with RMAN.
- ORA-27092 on AIX during an RMAN backup: fsize limit — Raise the fsize user limit in /etc/security/limits on AIX so that an RMAN backup agent can write files larger than the default limit.
- ORA-32004: obsolete or deprecated parameters specified — Find the obsolete or deprecated parameters flagged by ORA-32004 and remove them through a PFILE round trip.
- Oracle 12c: startup, shutdown and open modes (CDB and PDB) — STARTUP and SHUTDOWN options, ALTER DATABASE open modes and pluggable database commands in Oracle 12c, from the CDB or from a PDB.
- Oracle 19c/21c on RHEL 8: install, patch and post-install — Checklist to prepare RHEL 8, install Oracle Database 19c/21c, apply the 19.13 Release Update with OPatch and tune the database after installation.
- Oracle 19c: legacy clients, SID connections and locked accounts — Fix ORA-12505, ORA-28040, locked or expired accounts and case-sensitive passwords on Oracle 19c.
- Oracle administration queries cheat sheet — Common Oracle administration queries and commands: instance, users, control files, orapwd and sys_context.
- Oracle audit trigger on inserts — Log user, host and date of each insert with an AFTER INSERT trigger and SYS_CONTEXT.
- Oracle auditing: enable, query and purge the audit trail — Enable Oracle auditing, audit logons and failed statements, query the audit views, purge SYS.AUD$ with DBMS_AUDIT_MGMT and locate the alert log.
- Oracle column-level audit trigger — Oracle trigger template that logs the old and new value of each column, on INSERT and on UPDATE.
- Oracle control files: locate, back up and multiplex — Find the control file locations, back up their definition to a trace or a script, and add a copy by changing control_files in the spfile.
- Oracle daily RMAN full backup with DBMS_SCHEDULER — Daily full RMAN backup of an Oracle database, run from a shell script and scheduled with DBMS_SCHEDULER.
- Oracle Data Pump export per schema with size estimate and timing — Prepare a Data Pump directory, estimate export size and export several schemas with expdp while measuring elapsed time.
- Oracle database failure types and how they are recovered — Six families of database failure (statement, network, user process, instance, media, unwanted changes) and the recovery approach for each.
- Oracle database links: create, list and query remote objects — Create a database link, list the links defined in the database and query a remote table or view with the @link syntax.
- Oracle DATE internal format and out-of-range dates — Decode DATE bytes with DUMP (types 12 and 13) and insert a date from its raw value with DBMS_STATS.CONVERT_RAW_VALUE.
- Oracle instance startup time, uptime and activity history — Queries to check the startup time and uptime of an Oracle instance, and to analyse past active sessions.
- Oracle Instant Client on Linux: installation and login issues — Install Instant Client and SQL*Plus on Red Hat/Oracle Linux, configure sqlnet/tnsnames, fix ORA-01017 and audit failed logins.
- Oracle Managed Files: setting the default file destination — Set DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n so that Oracle names and places datafiles, redo logs and control files automatically (OMF, non-ASM).
- Oracle materialized view and ORA-01031 error — Fix ORA-01031 when creating a materialized view in another schema, and retrieve its DDL.
- Oracle multitenant: PDB command cheat sheet — Connect to, create, auto-open and plug in a PDB, including recovery without the XML manifest.
- Oracle on Linux: huge pages, max_map_count and memory settings (ORA-04030) — Linux and Oracle settings (huge pages, vm.max_map_count, _realfree_heap_pagesize, PGA) and apply procedure for a memory-hungry Oracle server.
- Oracle on Linux: kernel parameters (sysctl) and user limits — Kernel parameters in sysctl.conf and resource limits in limits.conf to prepare a Linux server for Oracle Database, with the commands to apply and check them.
- Oracle on Windows: regain SYSDBA access after losing the sys password — Temporarily disable NTS authentication, rebuild the password file and create Oracle administration accounts on Windows.
- Oracle ORA-00020: maximum number of processes exceeded — Use sqlplus -prelim to shut down an instance saturated with processes, then adjust the processes parameter.
- Oracle primary key syntax — Create, verify, drop, enable and disable an Oracle primary key, single or composite.
- Oracle RMAN command cheat sheet — Quick reference of RMAN commands: show, backup, catalog, report, list, crosscheck, delete, change and validate.
- Oracle RMAN: backup file format specifiers — Reference of the RMAN and LOG_ARCHIVE_FORMAT substitution variables used to name backup and archive files.
- Oracle RMAN: backup, restore and archived logs cheat sheet — RMAN configuration, full and point-in-time restore, Veeam plug-in and archived log housekeeping.
- Oracle RMAN: failure diagnosis and backup check commands — RMAN commands to list failures, check backups and delete expired backups.
- Oracle SQL Developer: switch the interface to English — Force SQL Developer to English with the -Duser.language=en option in ide.conf.
- Oracle SQL trace and event tracing — Enable, locate and analyse the Oracle SQL trace (10046) and the ERRORSTACK and SYSTEMSTATE event traces.
- Oracle TNS name resolution with OpenLDAP — Use an OpenLDAP directory instead of Oracle Internet Directory to centralise TNS aliases.
- Oracle: abnormally large SYSTEM tablespace — Queries to find the large segments of the SYSTEM tablespace and reclaim unused datafile space.
- Oracle: archive logs, Fast Recovery Area and RMAN cleanup — Cheat sheet to check ARCHIVELOG mode, measure archived log generation, manage the FRA and free space with RMAN.
- Oracle: Auto DOP and PARALLEL_SERVERS_TARGET — Check the parallelism parameters and size PARALLEL_SERVERS_TARGET.
- Oracle: bind variable values and execution plan from a SQL_ID — Queries to retrieve bind variable values and the plan for a SQL_ID, from memory or from AWR.
- Oracle: buffer cache hit ratio — Measure the Database Buffer Cache hit ratio, estimate the effect of another cache size and find the objects that use the cache.
- Oracle: change the service name of an 11g database — Change the service_names parameter in the SPFILE rather than only the tnsnames.ora alias, then restart the instance and check the connection.
- Oracle: check the data dictionary with hcheck.sql — Run hcheck.sql to detect known Oracle data dictionary problems.
- Oracle: clean up logs and traces with logrotate and adrci — Rotate alert and listener logs with logrotate, set ADR retention policies and purge with adrci, plus navigation tips.
- Oracle: client environment variables on Windows and Linux — Environment variables, clean-up steps and ODBC drivers to check before installing an Oracle Instant Client on Windows, with the Linux equivalent.
- Oracle: command-line tools and their commands (sqlplus, lsnrctl, expdp, rman) — Lookup table of common Oracle tools and the command that launches each.
- Oracle: copy a table and populate a collection type — Copy a table with or without data and initialise a TABLE type based on an OBJECT type.
- Oracle: copy a user and its grants between servers — Extract the DDL of a user, its roles, privileges and quotas with DBMS_METADATA.
- Oracle: count the rows of all tables in a schema — Get the row count of each table in a schema, with a PL/SQL block that stores the history in a table or with an XMLTYPE query.
- Oracle: customise the SQL*Plus prompt and session with login.sql — Configure SQL*Plus with login.sql or glogin.sql: date format and a prompt showing the user, the database or the date.
- Oracle: Data Pump and RMAN backup/restore cheat sheet — expdp/impdp commands, flashback exports, cold and hot backups, and common RMAN operations (list, validate, restore, housekeeping).
- Oracle: database size, size per schema and row counts — DBA_DATA_FILES, DBA_SEGMENTS and ALL_TABLES queries to measure an Oracle database, its schemas and the rows per table.
- Oracle: date format, NLS_LANG and SQL*Plus output formatting — Cheat sheet for NLS_DATE_FORMAT, NLS_LANG, NLS parameters and SQL*Plus output formatting.
- Oracle: DBMS_OUTPUT.PUT_LINE prints nothing in SQL*Plus — Enable SET SERVEROUTPUT ON to display DBMS_OUTPUT in SQL*Plus.
- Oracle: default database accounts, purpose and removal — Reference of the accounts created by default in an Oracle database (SYS, SYSTEM, XDB, SCOTT...), their purpose, the creating script and whether they can be dropped.
- Oracle: detect and reclaim table and tablespace fragmentation — Queries to measure fragmentation of Oracle tablespaces, tables and datafiles, and ways to reclaim space (shrink, move, index rebuild, undo).
- Oracle: difference between a view and a synonym — What a view and a synonym are in Oracle, and when to use each.
- Oracle: draw a Christmas tree in SQL*Plus — Fun SQL query that prints an asterisk Christmas tree using DECODE, LPAD and ROWNUM.
- Oracle: drop a tablespace with its contents and datafiles — DROP TABLESPACE command to clean up a test tablespace, including its objects and data files.
- Oracle: drop or move a tablespace (tables, indexes, LOBs) — Check that a tablespace is empty, drop it, and generate scripts to move tables, indexes and LOBs.
- Oracle: enable, disable and list events with oradebug — Enable and disable Oracle events, list active events with oradebug eventdump, and a selection of common event codes.
- Oracle: estimating SGA/PGA memory and storage — Queries to estimate the memory and storage needs of an Oracle database from an existing one.
- Oracle: Fast Recovery Area size, usage and occupants — Queries to measure the Oracle Fast Recovery Area (FRA), its reclaimable space and its occupants.
- Oracle: find columns that may need an index — Use sys.col_usage$ to list columns often used in predicates that are not indexed.
- Oracle: find open transactions and their sessions — Queries on v$transaction and v$session to list active transactions.
- Oracle: finding a query's sql_id, plan and history — Find the sql_id using a comment tag, then show the text, plan and AWR history of a query.
- Oracle: get a SQL result into a bind variable or a shell variable — Capture a query result in a SQL*Plus, PL/SQL or shell variable using sqlplus -s.
- Oracle: grant SELECT on the tables and synonyms of a schema — Generate the GRANT SELECT statements for all tables and synonyms of the connected schema, with a SQL*Plus script or a PL/SQL block.
- Oracle: health check queries (wait events, locks, space) — SQL*Plus queries to troubleshoot an Oracle database: wait events, locks, CPU, tablespaces.
- Oracle: increasing redo log size and monitoring log switches — Procedure to replace redo log groups with larger ones, with queries to monitor log switches and redo generation.
- Oracle: inspect and purge the recycle bin — Check how many objects and how much space the recycle bin holds, purge it, and disable it for new sessions.
- Oracle: install and configure Statspack — What Statspack is and a link to an install and configuration guide.
- Oracle: instance startup time and uptime — Oracle queries to show the instance startup time and uptime.
- Oracle: kill inactive and zombie sessions — Find and kill inactive or zombie Oracle sessions at database level and at OS level.
- Oracle: list an object's dependencies with DBA_DEPENDENCIES — Query DBA_DEPENDENCIES to find the objects a view or PL/SQL program depends on.
- Oracle: list corrupt blocks with V$DATABASE_BLOCK_CORRUPTION — Spot corrupt blocks in an Oracle database and in RMAN backups using two dynamic views.
- Oracle: list the object privileges granted to a user — Data dictionary queries listing the object and column privileges granted to a user.
- Oracle: list triggers — Queries on DBA_TRIGGERS and ALL_TRIGGERS to list triggers by table, schema or disabled state.
- Oracle: list, run, disable and audit DBMS_SCHEDULER jobs — DBMS_SCHEDULER queries and calls to manage and monitor Oracle jobs.
- Oracle: managing tablespaces and datafiles — Oracle queries and commands to monitor space, add, resize, move and drop tablespaces and datafiles.
- Oracle: managing the listener (lsnrctl) and troubleshooting it — Common lsnrctl commands, listener.ora configuration and fixes for Oracle listener failures, including 'The listener supports no services'.
- Oracle: monitoring modes of the check_oracle_health plugin — Reference list of Oracle monitoring counters (tablespaces, SGA, redo, rollback, waits).
- Oracle: number of redo log switches per hour — Queries to count redo log switches per hour and per day using V$LOG_HISTORY and GV$LOG_HISTORY.
- Oracle: ORA-01591, lock held by an in-doubt transaction — Find the in-doubt distributed transactions (DBA_2PC_PENDING) that lock a row and generate the matching ROLLBACK FORCE statements.
- Oracle: ORA-10873, datafile left in backup mode — Fix ORA-10873 at startup after a shutdown during a hot backup.
- Oracle: performance troubleshooting (CPU, memory, sessions, long queries) — OS commands and SQL queries to find Oracle processes and sessions consuming CPU, memory, undo or temp, analyse long-running queries and kill a session.
- Oracle: PFILE and SPFILE, creation and startup with a temporary file — Differences between PFILE and SPFILE, converting both ways, and fixing a parameter that prevents startup (ORA-00093).
- Oracle: purge the listener log (listener.log) — Truncate, rotate or purge an oversized listener.log and old Oracle trace files.
- Oracle: rebuild and list indexes — PL/SQL blocks to rebuild unusable indexes and queries to list indexes and their columns.
- Oracle: recompile invalid objects — Find, list and recompile invalid Oracle objects with utlrp.sql, ALTER COMPILE and DBMS_DDL.
- Oracle: redo log corruption (ORA-00353, ORA-00368) — Diagnose and handle an Oracle redo log corruption: drop and re-add a group, clear logfile, resetlogs.
- Oracle: reduce SYSAUX size (purge optimizer statistics history) — Identify SYSAUX occupants, purge the optimizer statistics history and rebuild fragmented tables and indexes.
- Oracle: roll back in-doubt distributed transactions (ORA-01591) — List prepared 2PC transactions in DBA_2PC_PENDING and generate rollback force commands.
- Oracle: select the last N rows from a table — Oracle queries to get the bottom N rows using RANK, DENSE_RANK or ROWNUM depending on the version.
- Oracle: session count history (AWR) — AWR snapshot query to track the number of sessions over 24 hours.
- Oracle: SHOW ERRORS with the source lines — Join dba_errors and dba_source to display PL/SQL compilation errors with the offending code.
- Oracle: shrink temporary files to their useful size — Compute the high water mark of Oracle tempfiles and generate the ALTER DATABASE TEMPFILE ... RESIZE statements to reclaim unused space.
- Oracle: sizing and monitoring SGA and PGA memory — SGA, PGA, ASMM and AMM basics, monitoring queries, advisors, sizing rules of thumb and HugePages.
- Oracle: sizing the processes and sessions parameters — Check Oracle process and session usage and compute the processes, sessions and transactions parameters.
- Oracle: table shrink and archived log volume — Shrink a table with SHRINK SPACE and measure archived redo volume per day, hour and instance.
- Oracle: tablespace size and biggest segments — Queries for used and free space per tablespace and to list the biggest segments.
- Oracle: UNKNOWN service status in lsnrctl — Understand the UNKNOWN status of a static listener registration and check the dynamic (READY) registration after a restart.
- Oracle: updating OPatch and applying a PSU/OJVM patch on Linux — Steps to back up and update OPatch, apply a PSU and an OJVM patch, then run datapatch.
- Oracle: users, roles, grants and locked or expired accounts — Cheat sheet to create and manage Oracle users, roles and grants, unlock accounts and tune password profiles.
- Oracle: V$ dynamic performance views by topic — Memo grouping the main Oracle V$ views by topic (sessions, SQL, memory, backup, RAC, Data Guard, ASM...).
- Oracle: view hidden (undocumented) parameters — Read the value and properties of a hidden Oracle parameter using the X$KSPPI, X$KSPPCV and X$KSPPSV tables.
- Oracle: what the bdump, udump, dpdump, adump and cdump directories hold — What each Oracle diagnostic directory contains and how to locate bdump and udump.
- PL/SQL: getting SELECT results into variables or cursors — Showing the result of a query inside a PL/SQL block: SELECT INTO for one row, an explicit cursor or a cursor FOR loop for several rows.
- Query session and instance PGA and SGA memory in Oracle — V$ queries to see PGA memory per session and the total SGA and PGA.
- Redo log corruption while the database is open — Redo log corruption errors, clearing or recreating the log group, and incomplete recovery.
- Restore the control file and SPFILE with RMAN — Restore the control file from an RMAN autobackup, depending on the backup location and name.
- Running SQL commands from the RMAN prompt — How to run SQL statements and PL/SQL blocks from RMAN, including quoting rules and the fact that no result is returned.
- Running SQL*Plus scripts from Bash — Run SQL*Plus from a shell script with a here-document, stop at the first SQL error and spool query results to a text file.
- Select the top N rows from an Oracle table — Four methods: RANK, DENSE_RANK, inner query with ROWNUM and correlated COUNT DISTINCT.
- Shrinking an Oracle temporary tablespace — Procedure to recreate the tempfile of an Oracle temporary tablespace that has grown too large.
- SQL*Plus substitution variables and PL/SQL output — Define, prompt for and reuse SQL*Plus variables, with an example of storing a date in a variable.
- Start Oracle 12c automatically at boot with systemd on CentOS — Create systemd units so that the Oracle listener and database start at boot on CentOS, with their environment in a sysconfig file.
- The six types of Oracle constraints — Quick reference of Oracle constraint types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and REF.
- Top 5 SQL statements by CPU with AWR — AWR history query to find the five SQL statements that used the most CPU over an interval.
- Top SQL statements from V$SQLAREA (SQL*Plus) — SQL*Plus script that lists the most expensive statements in V$SQLAREA by elapsed time, CPU, buffer gets, physical reads and rows, and the statements that share the same text prefix.
- Useful Oracle SQL queries: dates, data dictionary, administration and performance — Cheat sheet of everyday Oracle SQL queries for developers and DBAs.
- Veeam: restore a database from a backup — Restore methods for an Oracle or SQL Server database with Veeam Backup & Replication 12 and the Database restore wizard.