Oracle

Docs.oracle.com/en/database…

One, foreword

It is true that it has not been updated for a long time, even the first sentence seems difficult to say. Then a little comb, this article is mainly for the same as the master “holding a hammer, see what is a nail” readers, through the collection of data and other database analogies, looking for a suitable posture to understand Oracle, after all, beginners found unfriendly is mostly wrong posture, although…… It seems a long way off. At present, the reading progress of the master’s document is still very full, so most of the understanding is based on the results to guess the cause, so if there is something wrong, please point out the correction. Of course, there are many documents, blogs, and videos referenced in this article.

Second, the installation

Build the mirror

Github.com/oracle/dock…

git clone https://github.com/oracle/docker-images.git

cdOracleDatabase/SingleInstance/dockerfiles /. / buildContainerImage. Sh - v 18.4.0 - xCopy the code

Run the container

version: "3"
services:
  oracle: 
    image: Oracle/database: 18.4.0 - xe
    environment: 
      - ORACLE_PWD=0000
    ports: 
      - 1521: 1521
      - 5500: 5500
Copy the code

docker-compose up -d

docker exec -it <CONTAINER_ID> bash
Copy the code

Q&A

SQL *Plus login failed:ORA-01017: invalid username/password; logon denied

www.jianshu.com/p/7367b8882…

cd $ORACLE_HOME/bin

ls -lh | awk '{if($9 ~ /oracle/) print}'

chmod 6751 oracle
Copy the code

The OEM 5500 port is inaccessible

If you don’t just need it, please skip it.

Docs.oracle.com/en/database…

SQL> EXEC dbms_xdb.setlistenerlocalaccess(false);
Copy the code

Adobe Flash Player support is terminated on 2020-12-31. The 360 browser compatibility mode is still available.

Third, Quick Start

Pluggable Database (Pluggable Database)

# Log in to CDB$ROOT as user sys
sqlplus sys/0000@//localhost:1521/XE AS sysdba

Copy the code

-- Create PDB and DBA users
CREATE PLUGGABLE DATABASE db1 ADMIN USER pdbadmin IDENTIFIED BY pdbadmin DEFAULT TABLESPACE users DATAFILE '/opt/oracle/oradata/XE/db1/users01.dbf' SIZE 100M FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed'.'/opt/oracle/oradata/XE/db1');

- start the PDB
ALTER PLUGGABLE DATABASE db1 OPEN;

-- Automatically starts the PDB
ALTER PLUGGABLE DATABASE db1 SAVE STATE;

Switch to the new db1 container as the sys user
CONN sys/0000@//localhost:1521/db1 AS sysdba;

-- Grant DBA privileges
GRANT dba TO pdbadmin;

-- Log in to the PDB as the DBA
CONN pdbadmin/pdbadmin@//localhost:1521/db1;

-- Execute script to create sample HR
@/opt/oracle/product/18c/dbhomeXE/demo/schema/human_resources/hr_main_new.sql

Copy the code

Configure service name listener
vim $ORACLE_HOME/network/admin/tnsnames.ora
Copy the code

Db1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1) ) )Copy the code

The sample

4. Basic concepts

Schema (Schema)

Many of the concepts that are difficult to understand in Oracle may be stuck in the fact that its implementation of Schema is different from most vendors’ databases. For details, see Wikipedia:

Zh.wikipedia.org/wiki/%E7%B6…

It can be summed up as follows:

Schema is the standard and Database is the implementation.
MySQL implements Schema as Database.
Oracle’s implementation of Schema is User (such as HR)
MSSQL and PG have both databases and schemas (such as DBO and public).
In MSSQL 2000, Schema and User are implicitly the same, more like Oracle. After MSSQL 2005, Schema exists independently of User and is more like PG.
Oracle 12C introduces Multitenant Architecture, and Pluggable Database is more like Database.

Table space

Create table space
CREATE TABLESPACE t_data DATAFILE '/opt/oracle/oradata/XE/db1/t_data001.dbf' SIZE 100M;

Delete tablespaces
DROP TABLESPACE t_data INCLUDING CONTENTS AND DATAFILES CACADE CONSTRAINTS;
Copy the code

Bbs.csdn.net/topics/3902…

Multi-tenant architecture

Detailed video link: www.bilibili.com/video/BV12i…

Fifth, grammar arrangement

Matters needing attention

Oracle table alias cannot add AS!

Oracle column names are not case sensitive when they are not qualified by “”, but Navicat is case sensitive when creating tables!

Oracle has implicit data type conversions!

Oracle needs to commit transactions manually!

Oracle does not implement NOW() and uses SYSDATE instead!

JDBC: Oracle :thin:@//

: /

The default Oracle date parameter takes the current date value!

Oracle identifiers (table names, column names, index names, sequence names, etc.) are up to 30 characters long!

Container management

-- View the current container
show con_name;

-- Check whether it is CDB
SELECT name, cdb, con_id FROM v$database;

-- Switch containers
alter session set container=CDB$ROOT;
alter session set container=<PDB>;
Copy the code

-- View all PDBS
SHOW pdbs;

- the PDB on | off
ALTER PLUGGABLE DATABASE <PDB> <OPEN | CLOSE>;
ALTER PLUGGABLE DATABASE ALL <OPEN | CLOSE>;

- delete the PDB
ALTER PLUGGABLE DATABASE <PDB> close;
DROP PLUGGABLE DATABASE <PDB> INCLUDING DATAFILES;
Copy the code

Create PDB from PDB#SEED
CREATE ALTER PLUGGABLE DATABASE zero ADMIN USER pdbadmin IDENTIFIED BY 0000 DEFAULT TABLESPACE users DATAFILE '/opt/oracle/oradata/XE/zero/users01.dbf' SIZE 100M file_name_convert=('/opt/oracle/oradata/XE/pdbseed'.'/opt/oracle/oradata/XE/zero');

ALTER ALTER PLUGGABLE DATABASE zero OPEN;
ALTER ALTER PLUGGABLE DATABASE zero SAVE STATE;
Copy the code

ORA-65010: maximum number of pluggable databases created

SHOW PARAMETER max_pd;

ALTER SYSTEM SET max_pdbs=10;

Copy the code

The data type

Docs.oracle.com/en/database…

-- Oracle implicitly converts it to the data type if it appears in a numeric expression
SELECT salary + '10' FROM employees;

-- Oracle implicitly converts '200' to 200
SELECT last_name FROM employees WHERE employee_id = '200';

-- Oracle implicitly converts '24-JUN-06 to a value using the default date format
SELECT last_name FROM employees WHERE hire_date = '24-JUN-06';
Copy the code

A character encoding

You can safely use UTF-8

Note: Starting from Oracle Database 12c Release 2, if you use Oracle Universal Installer (OUI) or Oracle Database Configuration Assistant (DBCA) to create a database, then the default database character set used is the Unicode character set AL32UTF8.

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'
Copy the code

Single-line functions are often used

SELECT 
    -- Capitalization ==> Hello World
    INITCAP('hello world'),
    
    -- Find (starting from 1) ==> 7
    INSTR('hello world'.'world'),
    
    -- Search (starting from 1) ==> 06
    SUBSTR('2021-06-25'.6.2),
    
    -- String length ==> 11
    LENGTH('hello world'),
    
    -- Replace ==> hi world
    REPLACE('hello world'.'hello'.'hi')
FROM dual;

Copy the code

SELECT 
    - = = > 125
    ROUND(125.455.0),
    
    -- Rounding to one decimal place ==> 125.5
    ROUND(125.455.1),
    
    -- Round to the first decimal place ==> 130
    ROUND(125.455.- 1),
    
    - = = > 125
    TRUNC(125.455.0),
    
    -- Reserve one decimal place ==> 125.4
    TRUNC(125.455.1),
    
    -- Round down the first decimal place
    TRUNC(125.455.- 1)
FROM dual;
Copy the code

SELECT
    -- 1 month ago ==> 2021-05-25
    ADD_MONTHS(TO_DATE('2021-06-25'.'YYYY-MM-DD'), - 1),
    
    -- Next Monday ==> 2021-06-28
    NEXT_DAY(TO_DATE('2021-06-25'.'YYYY-MM-DD'), 'MONDAY'),
    
    -- End of month date ==> 2021-06-30
    LAST_DAY(TO_DATE('2021-06-25'.'YYYY-MM-DD')),
    
    -- Rounded based on YYYY-07-01 ==> 2021-01-01
    ROUND(TO_DATE('2021-06-25'.'YYYY-MM-DD'), 'YEAR'),
    
    -- Rounded to YYYY-MM-16 ==> 2021-07-01
    ROUND(TO_DATE('2021-06-25'.'YYYY-MM-DD'), 'MONTH'),
    
    -- Round on Thursday (Sunday is the first day) ==> 2021-06-27
    ROUND(TO_DATE('2021-06-25'.'YYYY-MM-DD'), 'DAY')
FROM dual;
Copy the code

SELECT
    -- col == null ? v : col
    NVL(col, v),
    
    -- col == null ? v2 : v1
    NVL2(col, v1, v2)
FROM dual;
Copy the code

SELECT 
    1
FROM dual
WHERE 
    REGEXP_LIKE('A Lu Ban kneeling in the street, stating the evil of the whole valley! '.'(ruban | | halogen eggs little short legs)')

Copy the code

SQL collection

Transfer line column

WITH temp AS (
    SELECT 'Tim'    rowkey, 'chinese' course, 80 score FROM dual UNION ALL 
    SELECT 'Tim'    rowkey, 'math'    course, 72 score FROM dual UNION ALL 
    SELECT 'Tim'    rowkey, 'english' course, 72 score FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 'chinese' course, 80 score FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 'math'    course, 82 score FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 'english' course, 68 score FROM dual UNION ALL 
    SELECT 'Vivian' rowkey, 'chinese' course, 90 score FROM dual UNION ALL 
    SELECT 'Vivian' rowkey, 'math'    course, 70 score FROM dual UNION ALL 
    SELECT 'Vivian' rowkey, 'english' course, 82 score FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 'chinese' course, 67 score FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 'math'    course, 77 score FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 'english' course, 80 score FROM dual
)
SELECT
     rowkey,
     MAX(CASE WHEN course = 'chinese' THEN score END) "score:chinese",
     MAX(CASE WHEN course = 'math'    THEN score END) "score:math",
     MAX(CASE WHEN course = 'english' THEN score END) "score:english"
FROM temp 
GROUP BY rowkey;
Copy the code

Column turned

WITH temp AS (
    SELECT 'Vivian' rowkey, 90 "score:chinese", 70 "score:math", 82 "score:english" FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 67 "score:chinese", 77 "score:math", 80 "score:english" FROM dual UNION ALL
    SELECT 'Tim'    rowkey, 80 "score:chinese", 82 "score:math", 68 "score:english" FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 80 "score:chinese", 72 "score:math", 72 "score:english" FROM dual 
)
SELECT rowkey,'chinese' course, "score:chinese" score FROM temp UNION ALL
SELECT rowkey,'math'    course, "score:math"    score FROM temp UNION ALL
SELECT rowkey,'english' course, "score:english" score FROM temp
Copy the code

Tree recursion (adjacency list)

www.cnblogs.com/boboxing/p/…

WITH temp AS (
    SELECT '1'     id, '0'   parent_id, 'System Administration' title, 'menu' type FROM dual UNION ALL
    SELECT '1/1'   id, '1'   parent_id, 'User Management' title, 'menu' type FROM dual UNION ALL
    SELECT '1/1/1' id, '1/1' parent_id, 'new'    title, 'button' type FROM dual UNION ALL
    SELECT '1/1/2' id, '1/1' parent_id, 'change'    title, 'button' type FROM dual UNION ALL
    SELECT '1/1/3' id, '1/1' parent_id, 'delete'    title, 'button' type FROM dual UNION ALL
    SELECT 'half'   id, '1'   parent_id, 'Menu Management' title, 'menu' type FROM dual UNION ALL
    SELECT 'one third'   id, '1'   parent_id, 'Role Management' title, 'menu' type FROM dual UNION ALL
    SELECT '2'     id, '0'   parent_id, 'System Monitoring' title, 'menu' type FROM dual UNION ALL
    SELECT '3'     id, '0'   parent_id, 'System Tools' title, 'menu' type FROM dual
)
SELECT LEVEL, temp.* FROM temp START WITH id = '1' CONNECT BY parent_id = PRIOR id
Copy the code

Paging (`11 ~ [20]`)

SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM t WHERE ROWNUM < = 20
) WHERE rn > 10
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Oracle18c quick to get started

Oracle

One, foreword

Second, the installation

Build the mirror

Run the container

Q&A

Third, Quick Start

Pluggable Database (Pluggable Database)

4. Basic concepts

Schema (Schema)

Table space

Multi-tenant architecture

Fifth, grammar arrangement

Matters needing attention

Container management

The data type

A character encoding

Single-line functions are often used

SQL collection

Transfer line column

Column turned

Tree recursion (adjacency list)

Paging (`11 ~ [20]`)

Oracle18c quick to get started

Oracle

One, foreword

Second, the installation

Build the mirror

Run the container

Q&A

Third, Quick Start

Pluggable Database (Pluggable Database)

4. Basic concepts

Schema (Schema)

Table space

Multi-tenant architecture

Fifth, grammar arrangement

Matters needing attention

Container management

The data type

A character encoding

Single-line functions are often used

SQL collection

Transfer line column

Column turned

Tree recursion (adjacency list)

Paging (11 ~ [20])

Related Posts

Write an everyday cool run in Python

SpringBoot Tomcat (4) Route mapper and pipeline

Talk about code: Functional Programming | Java Development in action

Paging (`11 ~ [20]`)