oracle

oracle 9i
oracle 11g
oracle 12c
oracle 18c
oracle 19c
oracle 21c
oracle 23ai

# i: internet
# c: cloud
# ai: Artificial Intelligence

• SQL, Structured Query Language
  • DDL, Data Definition Language: create, drop, alter, rename, truncate
  • DML, Data Management Language
    • DML, Data Management Language: insert, delete, update
    • DQL, Data Query Language: select
  • DCL, Data Control Language: grant, invoke, commit, rollback

oci

# https://github.com/gvenzl/oci-oracle-xe
# docker.io/gvenzl/orecle-xe:11.2.0.2
# ghrc.io/gvenzl/orcle-xe:11.2.0.2

# e.g.
ctr container create --env ORACLE_PASSWORD=123456 --mount type=bind,src=/data/var/lib/oradbms,dst=/u01/app/oracle/oradata,options=rbind:rw m.daocloud.io/docker.io/gvenzl/oracle-xe:11.2.0.2 oradbms

# add host name mapping
# /etc/hosts
# restart database instance
sql> shutdown immediate
sql> startup
# reload listener
$ lsnrctl reload
$ lsnrcrl status

sqlplus

# admin user: sys, system
# system role: sysdba, sysoper
# sqlplus <username>[/password][@database] [AS <role>]
# e.g.
$ sqlplus sys/123456 AS sysdba

address

-- host address
SELECT utl_inaddr.get_host_address FROM dual;
-- host name
SELECT utl_inaddr.get_host_name FROM dual;

user

create <username> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name>

tablespace

-- query
SELECT * FROM dba_data_files;
SELECT * FROM dba_users;

-- CREATE TABLESPACE <tablespace_name> DATAFILE <tablespace_name.dbf> SIZE <size> AUTOEXTEND ON;
-- e.g.
create tablespace ts_data_from datafile '/u01/app/oracle/oradata/XE/ts_data_from.dbf' size 100m autoextend on;

-- DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

-- ALTER USER <username> DEFAULT TABLESPACE <tablespace_name>;

privilege

SELECT * FROM dba_sys_privs;
SELECT * FROM dba_role_privs;
SELECT * FROM role_sys_privs;

SELECT * FROM session_roles;
SELECT * FROM session_privs;

-- GRANT [role,...] [priv,...] TO <username>;
-- e.g.
GRANT RESOURCE, CREATE SESSION to utoee;

CREATE SCHEMA AUTHORIZATION utoee;