Skip to content

oracle.md

Latest commit

 

History

History
1121 lines (819 loc) · 27.3 KB

oracle.md

File metadata and controls

1121 lines (819 loc) · 27.3 KB

Oracle

https://www.oracle.com/

Widely used classic enterprise RDBMS databases with good performance, durability and PL/SQL advanced SQL.

Most of this was not retained to be ported and I don't work on Oracle any more to go back and populate this.

Key Points

  • Expensive
  • Widely used battle tested enterprise RDBMS
  • Well suited to large-scale databases
  • Good performance and optimizations
  • Good security and encryption
  • Cloud - available on major clouds as a managed database
  • PL/SQL - Procedural Language/Structured Query Language adds more programming logic as a superset of SQL
    • analogous to Microsoft SQL Server's T-SQL (Transact SQL)
  • Oracle Autonomous Database automates tasks like tuning, backups, and patching using machine learning
  • High Availability - RAC (Real Application Clusters) and Data Guard offer high availability and disaster recovery
  • Clients - SQL*Plus, SQLcl, and SQL Developer for database management and development
Port Description
1521 Oracle SQL port

AWS RDS Limitations

If you use Oracle on AWS RDS then you'll face the following restrictions.

Including no use of these commands:

  • ALTER DATABASE
  • ALTER SYSTEM
  • CREATE ANY DIRECTORY
  • DROP ANY DIRECTORY
  • GRANT ANY PRIVILEGE
  • GRANT ANY ROLE

AWS Oracle DBA Tasks PDF.

Install Oracle Client Packages - SQL*Plus, JDBC, ODBC, SDK

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

If you have DevOps-Bash-tools you can use this automated script below.

This will give you everything - SQL*Plus, JDBC, ODBC, SDK and Tools:

install_oracle_client.sh

If you get this RPM install error:

Error: Invalid version flag: or

Install an older version.

See Oracle Client Install Error in Troubleshooting section at end.

SQL*Plus Readline Support

Use the rlwrap readline wrapper command in front of sqlplus to get command history:

rlwrap sqlplus <user>/<pass>@<fqdn>/<sid>

This is usually available in the rlwrap package on RHEL and Debian-based Linux systems and brew on Mac.

Local Login as Admin

This bypasses all authentication and logs you in as the superuser for administer the DB without needing a password.

First su to the oracle user under which Oracle was installed:

sudo su - oracle

Then as the oracle user start the local sqlplus client like this:

sqlplus / as sysdba

Connecting to Oracle - TNS Listener & SID

Check the TNS Listener configuration for what SID the Oracle DB expects you to connect to otherwise it'll reject your connection.

SQLcl command line client

https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/

Newer much more user friendly CLI client from Oracle with 100 command history buffer.

Backwards compatible CLI options with classic SQL*Plus.

Features

  • edit multi-line statements and scripts interactively at the SQLcl prompt
  • native Liquibase integration, with automatic changelog generation for your Oracle Database objects
  • 100 command history buffer
  • auto-complete object names or keywords using the <TAB> key
  • new commands: CTAS, DLL, Repeat, ALIAS, SCRIPT, FORMAT etc.
  • client-side scripting - execute Javascript to manipulate query results, build dynamic commands, interact with the session etc.
  • supports classic SQL*Plus environment settings, commands, and behaviours

Install SQLcl

https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/

Quickly install using DevOps-Bash-tools:

install_oracle_sqlcl.sh

This will create a convenience stub script /usr/local/bin/sqlcl pointing to /usr/local/sqlcl/bin/sql for $PATH convenience.

If you get an error running sqlcl or /usr/local/sqlcl/bin/sql like this:

Error: Could not find or load main class oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli
Caused by: java.lang.ClassNotFoundException: oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli

This rather unintuitive message is caused by the stupid installation zip having 0640 octal permissions on sqlcl/lib/* so if installed as root normal users not in the wheel group can run the sql but it can't find the lib/*.jar files.

If you have installed via the DevOps-Bash-tools scripted install install_oracle_sqlcl.sh you shouldn't encounter this as it fixes the permissions at install time.

SQLcl Config

Show all settings:

show all
set ...

Using SQLcl - Tips & Tricks

/usr/local/sqlcl/bin/sql <user>/<pass>@<fqdn>/<sid>

Inside SQLcl:

help

New commands are underlined.

<TAB> autocompletes column names.

Print the DDL for a table:

ddl <schema>.<table>

Options to customize DDL pretty output:

help set ddl
clear screen

Queries are smart formatted instead of column width to the definition like in SQL*Plus.

show sqlformat

To unset this to use the same old formatting as SQL*Plus:

set sqlformat

To output to CSV format (strings are quoted):

set sqlformat csv

Turn off column headers in the output:

set header off

Go back to using the nice smart formatting:

set sqlformat ansiconsole
set pagesize 50

Print the history list of last remembered 100 commands:

history

Go back to command number <n> as displayed from the history command:

history <n>

Execute it:

/

INFO is a new version of DESCRIBE that also shows Last Analyzed date-time, rows, sample size, if table is inmemory enabled/disabled, comments on the table, * next to the primary key, column comments, list of indexes, foreign key referential integrity constraints:

info <table>

Aliases for command queries or PL/SQL blocks with binds eg. :days:

alias list
alias list tomorrow
SELECT sysdate + :days from dual

Prints timestamp 7 days from now:

tomorrow 7

Repeat the last command 10 times, once every 1 second (useful to watch sessions and SQL queries being sent or watch the status of an index rebuild):

repeat 10 1

SQL Developer IDE

https://www.oracle.com/database/sqldeveloper

SQL Developer - free and widely used Oracle-specific IDE.

This is better than using a generic client like DBeaver because you can use Oracle specific commands like:

SHOW PARAMETER undo_retention;

which will fail on DBeaver like this:

SQL Error [900] [42000]: ORA-00900: invalid SQL statement

Alternatives:

  • Toad for Oracle
  • Navicat for Oracle
  • generic SQL Clients

Install SQL Developer

Download link

Quickly from DevOps-Bash-tools:

install_oracle_sql_developer.sh

This will even auto-open it for you on Mac.

On Mac you can find this in your Applications pop-up menu along with the usual programs or you can open it from the CLI using this command:

open -a "SQLDeveloper"

Using SQL Developer

Execute Shortcut

Hit Ctrl-Enter on Windows or Mac when the cursor is on a query in the Query Builder SQL Worksheet to quickly execute the SQL statement (must be ended with a semi-colon ; to separate it from the next query) without having to click the green triangle run button.

SQL Scripts

Scripts for DBA administration and performance engineering:

HariSekhon/SQL-scripts

Readme Card

SQL

dual - the pseudo-table

If you want to SELECT a value from a function or built-in value that does require querying data from a real table or view, in Oracle you need to provide a fake pseudo-table called DUAL to pass the syntax check of the SELECT query, eg. when selecting the current date (see next section for example).

Get Date

SELECT SYSDATE FROM dual;

Get Oracle Version

SELECT * FROM v$version;
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

List Tablespaces

SELECT tablespace_name, status, contents, logging FROM dba_tablespaces;

List Tables

The owner is the schema, also known as the database in other RBDMS systems.

SELECT owner, table_name FROM dba_tables;
SELECT
    tablespace_name,
    table_name,
    status,
    pct_used,
    pct_free
FROM
    all_tables
WHERE
    tablespace_name NOT IN ('SYSTEM', 'SYSAUX', 'RDSADMIN')
ORDER BY
    tablespace_name, table_name;

Count Tables per Tablespace

SELECT
    tablespace_name,
    count(1) as NUM_TABLES
FROM
    all_tables
GROUP BY
    tablespace_name
ORDER BY
    NUM_TABLES DESC;

List Users

SELECT username, user_id, password, account_status, lock_date, expiry_date, profile, last_login FROM dba_users;

Show your Currently Connected Username

User role assumed:

SELECT USER FROM dual;

User originally connected as:

SELECT CURRENT_USER FROM dual;

Show Tables Owned by Currently Connected User

SELECT table_name FROM all_tables WHERE owner = USER;

Show the Privileges of the Currently Connected User

SELECT * FROM user_sys_privs;

Show Privileges of All Users

SELECT grantee, privilege FROM dba_sys_privs ORDER BY grantee;

Show Expired Passwords

SELECT username, account_status FROM dba_users WHERE account_status LIKE '%EXPIRED%';

Alter User Password

ALTER USER spacewalk IDENTIFIED BY test;
-- or prompts for a new password
-- also allows for chars like ! which aren't liked on the alter user statement
--PASSWORD

Show DB Configuration Parameters

SELECT name, value FROM v$parameter;

Get Table DDL

Without these doesn't give full show create table output:

SET PAGESIZE 0;
SET LONG 1000;
SELECT dbms_metadata.get_ddl('TABLE', 'myTable', 'mySchema') FROM DUAL;

Investigate table

SELECT MIN(row_id), MAX(row_id) FROM myTable;
SELECT MIN(mycolumn), MAX(mycolumn), AVG(mycolumn) FROM myTable;

Backup Table to adjacent backup table

Do this before any risky operations or shrinking tables:

CREATE TABLE mytable_backup AS SELECT * FROM  mytable;

Space Clean Up

  • drop temporary and backup tables if you can
  • purge recyclebin and dba recyclebin
  • shrink tables / tablespaces

Purge Recyclebin

SHOW RECYCLEBIN;
PURGE RECYCLEBIN;
SHOW RECYCLEBIN;

To only purge the recyclebin for a given table:

PURGE TABLE table_name;

Purge DBA Recyclebin

This is for all user's recyclebins.

Show the recyclebin contents for all users:

oracle_show_dba_recyclebin.sql

SELECT
    owner,
    object_name,
    original_name,
    type,
    droptime,
    space
FROM
    dba_recyclebin
ORDER BY
    owner,
    droptime
DESC;

Then purge it:

PURGE DBA_RECYCLEBIN;

On AWS RDS you will need to call this instead due to AWS restricting some system procedures:

EXEC rdsadmin.rdsadmin_util.purge_dba_recyclebin;

See this doc for more details.

Then re-run the show recyclebin query to check:

oracle_show_dba_recyclebin.sql

SELECT
    owner,
    object_name,
    original_name,
    type,
    droptime,
    space
FROM
    dba_recyclebin
ORDER BY
    owner,
    droptime
DESC;

Investigate Tablespaces Space

These scripts calculations assume an 8KB block size, verify that using this query first:

SELECT value FROM v$parameter WHERE name = 'db_block_size';

Should show value = 8192.

Show Tablespace Size, Space Used vs Free in GB and as a Percentage:

HariSekhon/SQL-scripts - oracle_tablespace_space.sql

SELECT
    df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024 * 1024) "Size (GB)",
    (df.bytes - SUM(fs.bytes)) / (1024 * 1024 * 1024) "Used Space (GB)",
    ROUND(SUM(fs.bytes) / (1024 * 1024 * 1024), 2) "Free Space (GB)",
    ROUND(SUM(fs.bytes) / df.bytes * 100, 2) "Free Space %"
FROM
    dba_free_space fs,
    (SELECT
        tablespace_name,
        SUM(bytes) bytes
    FROM
        dba_data_files
    GROUP BY
        tablespace_name) df
WHERE
    fs.tablespace_name (+) = df.tablespace_name
        AND
    UPPER(fs.tablespace_name) LIKE '%UNDO%'
GROUP BY
     df.tablespace_name,
     df.bytes
ORDER BY
     "Free Space (GB)" DESC,
     "Used Space (GB)" DESC;

Show Tablespace Size, Space Used GB and Percentage Used:

HariSekhon/SQL-scripts - oracle_tablespace_space2.sql

SELECT
    tablespace_name "Tablespace",
    -- convert used_space in blocks to GB as each block is 8KB
    ROUND(used_space * 8 / 1024 / 1024, 2) AS "Used Space (GB)",
    -- convert tablespace_size in blocks to GB as each block is 8KB
    ROUND(tablespace_size * 8 / 1024 / 1024, 2) AS "Total Space (GB)",
    ROUND(used_percent, 2) AS "Used Space %"
FROM
    dba_tablespace_usage_metrics
ORDER BY
    "Used Space %" DESC;

Investigate Big Tables with Free Space

HariSekhon/SQL-scripts - oracle_table_space.sql

SELECT
    owner,
    table_name,
    -- each block is 8KB, multiply it to GB, round to two decimal places
    ROUND(blocks * 8 / 1024 / 1024, 2) AS total_gb,
    -- estimate data size from rows vs average row size, round to two decimal places
    ROUND(num_rows * avg_row_len / 1024 / 1024 / 1024, 2) AS actual_data_gb,
    -- estimate free space by subtracting the two above calculations
    ROUND((blocks * 8 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024 / 1024), 2) AS free_space_gb,
    -- calculate free space percentage from the above three calculations
    ROUND(
        ( (blocks * 8 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024 / 1024) ) /
        (blocks * 8 / 1024 / 1024) * 100, 2) AS free_space_pct
FROM
    dba_tables
WHERE
    blocks > 0
        AND
    num_rows > 0
        AND
    ((blocks * 8 / 1024 / 1024) - (num_rows * avg_row_len / 1024 / 1024 / 1024)) /
    (blocks * 8 / 1024 / 1024) > 0.2  -- TUNE: currently only showing tables over 20% utilized
        AND
    owner NOT IN
      ('SYS', 'SYSTEM', 'SYSAUX', 'RDSADMIN')
ORDER BY
    free_space_gb DESC,
    total_gb DESC;

To investigate tables space in only a given schema eg. USERS:

SELECT
    t.owner,
    t.table_name,
    ROUND(t.blocks * 8 / 1024 / 1024, 2) AS total_gb_from_tables,
    ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS total_gb_from_segments,
    ROUND(t.num_rows * t.avg_row_len / 1024 / 1024 / 1024, 2) AS actual_data_gb,
    ROUND(((t.blocks * 8 / 1024) - (t.num_rows * t.avg_row_len / 1024 / 1024)) / 1024, 2) AS estimated_free_space_gb,
    ROUND(((t.blocks * 8) - (t.num_rows * t.avg_row_len / 1024)) / (t.blocks * 8) * 100, 2) AS estimated_free_space_pct,
    t.last_analyzed
FROM
    dba_tables t
JOIN
    dba_segments s
ON
    t.owner = s.owner
        AND
    t.table_name = s.segment_name
WHERE
    t.blocks > 0
        AND
    t.num_rows > 0
        AND
    -- TUNE: currently only showing tables over 20% utilized
    ((t.blocks * 8 / 1024) - (t.num_rows * t.avg_row_len / 1024 / 1024)) / (t.blocks * 8 / 1024) > 0.2
        AND
    t.owner = 'INFA_STG2'
ORDER BY
    estimated_free_space_gb DESC,
    total_gb_from_segments DESC;

To investigate segments in the given schema eg. USERS:

SELECT
    segment_name,
    segment_type,
    tablespace_name,
    bytes/1024/1024/1024 AS size_gb,
    blocks
FROM
    dba_segments
WHERE
    owner = 'USERS'  -- XXX: Edit this
        AND
    segment_type = 'TABLE'
        AND
    blocks > 8
    -- to look at only specific tables
    --    AND
    --segment_name IN
    --('MY_TABLE', 'MY_TABLE2')
ORDER BY
    size_gb DESC;

Find table candidates to move / shrink in a given tablespace:

SELECT
    segment_name,
    segment_type,
    ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM
    dba_segments
WHERE
    tablespace_name = 'USERS' -- XXX: Edit
GROUP BY
    segment_name,
    segment_type
HAVING
    SUM(bytes)/1024/1024/1024 > 1
ORDER BY
    size_gb DESC;

Shrink Table

First backup the table you are going to shrink to an adjacent backup table.

Then SHRINK SPACE of the table to reduce space allocated to it by removing unused space from its data blocks (optimizes storage and improves performance).

ALTER TABLE mytable ENABLE ROW MOVEMENT;

CASCADE also shrinks dependent objects eg. indexes:

ALTER TABLE mytable SHRINK SPACE CASCADE;
--Start time  Thu Oct 10 13:56:13 GST 2024
--Finish time Thu Oct 10 14:01:10 GST 2024

ALTER TABLE mytable DISABLE ROW MOVEMENT;

Regenerate stats on table otherwise you won't see the space change:

CALL DBMS_STATS.GATHER_TABLE_STATS('myschema', 'mytable');

Check the space again by running scripts / queries above in HariSekhon/SQL-scripts.

Investigate table to check it looks ok.

If happy, then drop the backup table:

DROP TABLE mytable_backup;

Rollback if any problem following Restore table from adjacent backup table.

Shrink Tablespaces

On AWS RDS follow this doc and this doc.

Shrink Permanent Tablespace

ALTER TABLESPACE users COALESCE;
ALTER TABLESPACE users RESIZE 500G;
SQL Error [3297] [42000]: ORA-03297: file contains used data beyond requested RESIZE value
Big File Tablespace

Add new temp tablespace and then remove old one:

CREATE TEMPORARY TABLESPACE
  temp2
TEMPFILE '/path/to/new_tempfile.dbf'
SIZE 50G REUSE
AUTOEXTEND ON NEXT 1G MAXSIZE 500G;

Switch default tempspace to the new one:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Switch all users tempspaces to the new one:

BEGIN
  FOR r IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || r.username || ' TEMPORARY TABLESPACE temp2';
  END LOOP;
END;
/

Wait for old user sessions to complete:

SELECT
  username, tablespace, blocks
FROM
  v$tempseg_usage
WHERE
  tablespace = 'TEMP';

Then drop the old temp tablespace:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Small File Tablespace

Create new temporary tablespace tempfile with a new size first to avoid Oracle having any period of time with no sort / index rebuild space:

ALTER TABLESPACE temp ADD TEMPFILE '/path/to/new_tempfile.dbf' SIZE 1000M;

Find the path to the old tempfile:

SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb FROM v$tempfile;

or

SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb FROM dba_temp_files;

Edit the filename path in this ALTER statement to delete that tempfile when no session is using the temp tablespace:

ALTER DATABASE TEMPFILE '/path/to/old_tempfile.dbf' DROP;

Shrink Temporary Tablespace

If this is overallocated you can drop and create it smaller.

Use this query to find out if it's a Bigfile or Smallfile tablespace. AWS RDS uses Bigfile tablespaces.

SELECT TABLESPACE_NAME, CONTENTS, BIGFILE FROM DBA_TABLESPACES;

WARNING: dropping temp tablespace or tablespace file can disrupt active sessions using sorts, large queries, or index rebuilds, all of which use the temporary tablespace and can get hit with errors like:

ORA-01187: cannot read from file because it failed verification tests

or

ORA-01110: data file name of the tempfile

You should add a new temp tablespace for a bigfile tablespace or add a new temp tablespace datafile for smallfile tablespace.

Check for a low activity time and that there are no currently active sessions using temp tablespace before dropping the old temporary file.

Show User Sessions Using Temporary Tablespace:

HariSekhon/SQL-scripts - oracle_show_sessions_using_temp_tablespace.sql

SELECT
    s.sid,
    s.username,
    t.tablespace,
    t.blocks,
    t.segfile#,
    t.segblk#,
    t.contents,
    t.sql_id
FROM
    v$sort_usage t,
    v$session s
WHERE
    t.session_addr = s.saddr;

Shrink Undo Tablespace

You cannot shrink it but must replace it with a smaller one.

Check how long your Undo retention is:

SHOW PARAMETER undo_retention;

This is in seconds, ie. 15 minutes:

NAME           TYPE    VALUE
-------------- ------- -----
undo_retention integer 900

Create a new smaller 'undo' tablespace:

CREATE UNDO TABLESPACE new_undo_ts DATAFILE SIZE 100G AUTOEXTEND ON NEXT 1G MAXSIZE 500G;

Switch to the new undo tablespace. On AWS RDS this is actually quite a bit problem since the ALTER SYSTEM command is not allowed and therefore requires a database restart.

Set system to use new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = new_undo_ts;

Important*: Wait until no active connections is using the old UNDO tablespace:

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';

only then drop the old undo tablespace:

You can reduce the Undo Retention period like this to try to expedite this:

ALTER SYSTEM SET undo_retention = 900;  -- Set to 15 minutes temporarily

Once there is nothing using it, drop the old undo tablespace:

DROP TABLESPACE old_undo_ts INCLUDING CONTENTS AND DATAFILES;

Restore table from adjacent backup table

First check you have the backup table mytable_backup.

Once backup table contents has been verified, then empty the table to be restored:

TRUNCATE TABLE mytable;

Then restore the rows from the backup table:

INSERT INTO mytable SELECT * FROM mytable_backup;

Troubleshooting

Oracle Client Install Error: Invalid version flag: or

This happens on Amazon Linux 2 with the latest Oracle Client version 23.

Workaround: Install Oracle Client 21 instead.

SQLcl Error: Could not find or load main class oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli

If you get an error running sqlcl or /usr/local/sqlcl/bin/sql like this:

Error: Could not find or load main class oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli
Caused by: java.lang.ClassNotFoundException: oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli

This rather unintuitive message is caused by the stupid installation zip having 0640 octal permissions on sqlcl/lib/* so if installed as root normal users not in the wheel group can run the sql but it can't find the lib/*.jar files.

Fix:

chmod -R o+r /usr/local/sqlcl/lib

If you have installed via the DevOps-Bash-tools scripted install install_oracle_sqlcl.sh you shouldn't encounter this as it fixes the permissions at install time.


Partial dump from memory because I didn't have many notes retained from my Oracle DBA time in 2005-2009