Thought Persistence

Automatically casts values into types which can be concatenated.

As Oracle interprets NULL as the empty string, it doesn't return NULL if an operand is NULL.

Documentation

The optional NULLs allowed feature is implemented: If the UNIQUE-constraint is imposed on a single column, then the column may contain any number of NULLs (as expected from characteristic 2 in the above description of the standard). However, if the UNIQUE-constraint is specified for multiple columns, then Oracle sees the constraint as violated if any two rows

• contain at least one NULL in a column affected by the constraint
• identical, non-NULL values in the rest of the columns affected by the constraint

Documentation

If you want an auto-incrementing column in Oracle, then create a sequence and use that sequence in a trigger associated to the table. Example: For the table mytable, you want the mytable_id column to be of integer type, with an auto-incrementing values:

CREATE TABLE mytable (
  mytable_id INTEGER PRIMARY KEY,
  ... -- (other columns)
);
 
CREATE SEQUENCE mytable_seq;
 
CREATE TRIGGER mytable_seq_trigger
BEFORE INSERT ON mytable FOR EACH ROW
BEGIN
  IF (:new.mytable_id IS NULL) THEN
    SELECT mytable_seq.nextval INTO :new.mytable_id
    FROM DUAL;
  END IF;
END;
/

This will create an auto-incrementing column resembling the GENERATED BY DEFAULT variant from the standard. If an column resembling the GENERATED ALWAYS variant is needed, then the trigger should be extended to raise an exception if the user tries to insert a non-NULL value, and a trigger preventing UPDATEs of the relevant column should be added.

Note: If 'nice', incrementing values aren't important, you may use Oracle's SYS_GUID function as the default for a column; that way, universally unique identifiers will be assigned if you don't indicate a value for the column in new rows.

Documentation: CREATE TRIGGER, CREATE SEQUENCE, and SYS_GUID.

Note, that the TRUNCATE TABLE implicitly commits the current transaction.

Needed privileges—Quoting from the documentation:
...the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.

Documentation

sqlplus lacks command completion, and has very limited built-in command history handling.

• On unix-like systems: Look in the /etc/oratab file.
• On Windows: Start Windows' Services management console and look for services with names starting with OracleServiceXXXX. Each XXXX is the name (AKA SID) of an instance.

Documentation

The quickest way to get a usable list of 'normal' tables:
SELECT * FROM tab
(Use of the tab dictionary view is officially deprecated, though.)

Documentation

To get (very) basic information:
DESCRIBE tablename

To get information on constraints, including foreign (referred) table/column information, a query like this may be used (adjust tablename in one of the last lines):
COLUMN consname FORMAT a11;
COLUMN colname FORMAT a10;
COLUMN type FORMAT a11;
COLUMN cond FORMAT a20;
COLUMN ref_tabname FORMAT a11;
COLUMN ref_colname FORMAT a11;
SELECT
  uc.constraint_name consname,
  ucc.column_name colname,
  CASE
    WHEN uc.constraint_type='C' THEN 'CHECK'
    WHEN uc.constraint_type='P' THEN 'PRIMARY KEY'
    WHEN uc.constraint_type='R' THEN 'REFERENTIAL'
    WHEN uc.constraint_type='U' THEN 'UNIQUE'
    ELSE uc.constraint_type
  END as type,
  uc.search_condition cond,
  ucc_r.table_name ref_tabname,
  ucc_r.column_name ref_colname
FROM
  user_constraints uc
  JOIN
  user_cons_columns ucc ON
    uc.constraint_name=ucc.constraint_name AND
    uc.owner=ucc.owner
  LEFT JOIN
  user_constraints uc_r ON
    uc.r_constraint_name=uc_r.constraint_name AND
    uc.owner=uc_r.owner
  LEFT JOIN
  user_cons_columns ucc_r ON
    uc_r.constraint_name=ucc_r.constraint_name AND
    uc_r.owner=ucc_r.owner
WHERE
  uc.TABLE_NAME = UPPER('tablename')
ORDER BY consname,colname;

To get information on indexes on a table, a query like this may be used (adjust tablename in one of the last lines):
COLUMN index_name  FORMAT a11;
COLUMN type        FORMAT a8;
COLUMN uniness     FORMAT a9;
COLUMN column_name FORMAT a20;
      SELECT index_name,
             index_type type,
             uniqueness uniness,
             column_name
        FROM user_indexes ui
NATURAL JOIN user_ind_columns uic
       WHERE dropped='NO'
         AND table_name=upper('tablename')
    ORDER BY index_name,column_name

Documentation:

• DESCRIBE TABLE sqlplus command
• COLUMN sqlplus command
• Static Data Dictionary Views
• USER_CONSTRAINTS data dictionary view
• USER_CONS_COLUMNS data dictionary view
• USER_INDEXES data dictionary view
• USER_IND_COLUMNS data dictionary view

ANALYZE TABLE tablename ESTIMATE STATISTICS;
ANALYZE TABLE tablename ESTIMATE STATISTICS FOR ALL INDEXES;
(It's unclear to me if both are needed to gain the relevant statistics.)

—Or:
ANALYZE TABLE tablename COMPUTE STATISTICS;
ANALYZE TABLE tablename COMPUTE STATISTICS FOR ALL INDEXES;

If you want to stay away from deprecated features (although I doubt that Oracle will remove ANALYZE...STATISTICS... any time soon), you need to use the DBMS_STATS package.

Documentation

The plan explanation may now be viewed by a query like this:
COLUMN operation   FORMAT a30;       -- for output formatting
COLUMN options     FORMAT a10;       -- for output formatting
COLUMN object_name FORMAT a12;       -- for output formatting
COLUMN otype       FORMAT a5;        -- for output formatting
COLUMN cardinality FORMAT 999999999; -- for output formatting
COLUMN cost        FORMAT 999999;    -- for output formatting
          SELECT LPAD(' ',2*(LEVEL-1))||operation operation,
                 options,
                 object_name,
                 object_type otype,
                 cardinality,
                 cost
            FROM plan_table
      START WITH id = 0 AND statement_id = 'explanationX'
CONNECT BY PRIOR id = parent_id
             AND statement_id = 'explanationX'

A bit of documentation reading can probably not be avoided:
Documentation

Documentation

Documentation