Oracle
Articles in the Oracle category (1).
Articles in this category.
- Active session who wait events
- 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 RMAN backup job status in Oracle — SQL queries to follow finished and running RMAN backup jobs, their progress and their output.
- 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.
- 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.
- How far back can Oracle Flashback Database go — Query V$FLASHBACK_DATABASE_LOG and understand db_flashback_retention_target and the fast recovery area.
- Install19cOracleRedhat8
- 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.
- Monitoring RMAN backup status and output with V$ views — SQL queries to check the status, progress, size and output of Oracle RMAN backup jobs.
- OPTIMIZER USE FEEDBACK
- 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-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: legacy clients, SID connections and locked accounts — Fix ORA-12505, ORA-28040, locked or expired accounts and case-sensitive passwords on Oracle 19c.
- 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 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: 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: 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 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: 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 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 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: 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 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 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: DBMS_OUTPUT.PUT_LINE prints nothing in SQL*Plus — Enable SET SERVEROUTPUT ON to display DBMS_OUTPUT in SQL*Plus.
- 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: 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: 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: 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: kill inactive and zombie sessions — Find and kill inactive or zombie Oracle sessions at database level and at OS level.
- Oracle: list, run, disable and audit DBMS_SCHEDULER jobs — DBMS_SCHEDULER queries and calls to manage and monitor Oracle jobs.
- 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 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: monitoring modes of the check_oracle_health plugin — Reference list of Oracle monitoring counters (tablespaces, SGA, redo, rollback, waits).
- Oracle multitenant: PDB command cheat sheet — Connect to, create, auto-open and plug in a PDB, including recovery without the XML manifest.
- 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 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 ORA-00020: maximum number of processes exceeded — Use sqlplus -prelim to shut down an instance saturated with processes, then adjust the processes parameter.
- 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: 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 primary key syntax — Create, verify, drop, enable and disable an Oracle primary key, single or composite.
- Oracle: purge the listener log (listener.log) — Truncate, rotate or purge an oversized listener.log and old Oracle trace files.
- Oracle: recompile invalid objects — Find, list and recompile invalid Oracle objects with utlrp.sql, ALTER COMPILE and DBMS_DDL.
- Oracle: reduce SYSAUX size (purge optimizer statistics history) — Identify SYSAUX occupants, purge the optimizer statistics history and rebuild fragmented tables and indexes.
- 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 command cheat sheet — Quick reference of RMAN commands: show, backup, catalog, report, list, crosscheck, delete, change and validate.
- Oracle RMAN: failure diagnosis and backup check commands — RMAN commands to list failures, check backups and delete expired backups.
- Oracle: roll back in-doubt distributed transactions (ORA-01591) — List prepared 2PC transactions in DBA_2PC_PENDING and generate rollback force commands.
- 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 the processes and sessions parameters — Check Oracle process and session usage and compute the processes, sessions and transactions parameters.
- Oracle SQL trace and event tracing — Enable, locate and analyse the Oracle SQL trace (10046) and the ERRORSTACK and SYSTEMSTATE event traces.
- 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: 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.
- 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.
- Spfile
- 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.
- Tablespace
- Tablespace/details
- The six types of Oracle constraints — Quick reference of Oracle constraint types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and REF.
- 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.