Oracle Server (Notes)

From Minor Miracle Software
Revision as of 18:07, 30 August 2021 by WikiSysop (talk | contribs) (Internal Links)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Oracle Server (Notes)

Oracle Documentation[1]

Oracle Server Reserved Words

Oracle Materialized Views[2][3]

Main Article: Oracle Materialized Views Oracle uses materialized views (known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

Materialized Views are supported in: Oracle, Sybase, IBM DB2, MS-SQL as "indexed views". There are work-arounds for PostgreSQL and MySQL.

-- Oracle code for a materialized view that updates once a day
CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;

Multiple Clients Installed on One Computer

  • Use a different folder for each. For example:
C:\app\oracl12c
C:\app\oracl19c

SQL Developer is no longer bundled with Oracle Client. It is a separate program now. Post Oracle 19c.

Add or change a Primary Key

This procedure adds a Primary Key to a table that lacks one.

  • old_table is the original table. new_table is the target table.
  • This is most easily done in Oracle SQL Developer.

1. Get the DDL for the table.
2. Add "old_table<_PK> NUMBER NOT NULL PRIMARY KEY;" in the first column position.
This will create the Primary Key constraint and create an index on that column as a side-effect.
3.Right-Click on the table and select "Edit". The "Edit Table" will appear.
3.1 Select the PK column and verity the constraint and index were created.
3.2 Click the "Identity Column" and change the type to "Column Sequence". This will make the column auto increment.
4. The insert will look like this:

INSERT INTO "new_table" (<column names>)
SELECT (<column names>) "old_table";

Be sure to omit the PK column and that the columns are in the correct order.
5. Drop old_table.
6. Rename new_table to old_table. All Views, Triggers, Functions, e.t.c., will still work because they key on the table name.
To move drop the primary constraint from the column and proceed as above.

Tables in a Schema

  • Connect with the schema account and use: SELECT * FROM USER_TABLES;

Column Names in a Table

  • SELECT column_name FROM user_tab_cols WHERE table_name = 'myTableName';
  • CREATE TABLESPACE TABSPACE_2 DATAFILE 'TABSPACE_2.dbf' SIZE 128m;
  • Constraints by name: ELECT * FROM user_cons_columns where constraint_name LIKE 'constraint_name_K%';
  • Column Names from Table: SELECT * FROM user_cons_columns where column_name LIKE 'TABLE_PK%';
  • Alter Constraint Name: ALTER TABLE TURNAROUND_TIMESTABLE rename constraint SYS_C009849 TO TABLE_FK6;
  • Drop Constraint:ALTER TABLE tablename DROP CONSTRAINT SYS_C009391;
  • Rename Constraint: ALTER TABLE test RENAME CONSTRAINT test1_pk TO test_pk;
  • Version: SELECT * FROM v$version;
  • Create User/Database: create user <username> identified by "<password>"; After 18c log connect go "XEPDB1", the pluggable database.[4]
    • Execute "alter session set "_ORACLE_SCRIPT"=true; " first.
  • GRANT CONNECT, RESOURCE, DBA TO books_admin;
 CREATE TABLE books
( book_id            NUMBER PRIMARY KEY,
  book_name          VARCHAR2(30),
  author_name        VARCHAR2(40),
  book_isbn          VARCHAR2(20) )
TABLESPACE users;

Random Data

  • Random Date
SELECT TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J')
                                    ,TO_CHAR(DATE '9999-12-31','J')
                                    )
                    ),'J'
               ) FROM DUAL;

  • Random string
select dbms_random.string('X',3) from dual;
    • U - Upper case
    • L - Lower case
    • A - Alphanumeric
    • X - Alphanumeric with upper case alphabets.
    • P - Printable characters only.

Where "3" is the string length.

  • Random Value: One version returns a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). The other version returns a random Oracle Database NUMBER value x, where x is greater than or equal to the specified low value and less than the specified high value.
DBMS_RANDOM.VALUE( low  IN  NUMBER, high IN  NUMBER);
    • Random Integer with Fixed Length
select round(dbms_random.value(0.5,100.49999999999),0) from dual;

where the number 100 is the upper range.

  • Reorder columns in a table, with or without data.

When you make an invisible column visible, the column is included in the table's column order as the last column.

ALTER TABLE t MODIFY (d INVISIBLE, e INVISIBLE);
ALTER TABLE t MODIFY (d VISIBLE, e VISIBLE);

Start with the first column and work down. Going through all the columns put the first column on top again.


  • DBMS_OUTPUT. Prints to screen.
SET SERVEROUTPUT ON; -- enables the package.
DBMS_OUTPUT.put_line ('Equal');
  • Rename a constraint.
ALTER TABLE rename constraint <old constraint name> to <new constraint name>;
  • Grant
grant dba to <user name>;
  • Create user
alter session set "_ORACLE_SCRIPT"=true; Create USER <user name> IDENTIFIED BY <password>;
  • Create Table
CREATE TABLE books ( book_id NUMBER PRIMARY KEY, book_name VARCHAR2(30), author_name VARCHAR2(40), book_isbn VARCHAR2(20) ) TABLESPACE users;
  • Delete Duplicate Rows
DELETE FROM table WHERE rowid not in (SELECT MIN(rowid) FROM table GROUP BY siteid);
  • Compare just days
-- SELECT * FROM table WHERE my_date LIKE to_date('11/09/2020', 'dd/mm/yyyy');
  • Drop Administrative User
alter session set "_oracle_script"=true;
DROP USER user cascade;
The cascade keyword also drops all the user's objects.

Oracle Client Install

Oracle Client Install[5]

This procedure installs Oracle client development files.

  • Save and delete from System Environmental variables ORACLE_HOME and ORACLE_SID. Restore these after the install. Else the installer is hopelessly confused.
  • Run setup.exe as Administrator.

Use Built-in Windows Account. Select "Run Time". This will install the development files. Install in "C:\app\clients".

  • Post install.

The install folder is Read-Only. Remove that. Change the folder name to something like. "C:\app\clients\19.0.0_x86" Make sure there are no spaces in the path. Don't forget to append x86 or x64.

Oracle Primary Key Add

NUMBER NOT NULL PRIMARY KEY,

SELECT column_name FROM user_tab_cols WHERE table_name = 'tablename';

This procedure adds a Primary Key to a table that lacks one.

  • old_table is the original table. new_table is the target table.
  • This is most easily done in Oracle SQL Developer.
  • The procedure takes about 10 minutes per table.

1. Get the DDL for the table. 2. Paste it into the worksheet. 2.1 Change the table name to "old_table_name" to "old_table_name_TMP". 2.2 Add "old_table<_PK> NUMBER NOT NULL PRIMARY KEY" before the first column. This will create the Primary Key constraint and create an index on that column as a side-effect. 2.3 Delete everything between the last ")" and ";". 2.4 Execute the create statement. 2.5 Close and open the "Tables" folder and hit the refresh button. Then the new table will appear. 3. Right-Click on the table and select "Edit". The "Edit Table" window will appear. 3.1 Select Click the "Identity Column" and change the type to "Column Sequence". This will make the column autoincrement. 4. The insert will look like this: INSERT INTO "new_table" (<column names>) SELECT <column names> FROM "old_table"; Be sure to omit the PK column. 5. Right click on "old_table". Select Drop. Check "Cascade Constraints" and "Purge". Click "Apply". 6. Rename new_table to old_table. All Views, Triggers, Functions, e.t.c., will still work because they key on the table name.

Internal Links

Parent Article:Databases