Difference between revisions of "Oracle Server (Notes)"
Latest revision as of 18:07, 30 August 2021
Oracle Server (Notes)
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:
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.
- 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 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.