Oracle Usage FAQ

April 21, 2023

Database

Unlike other databases like MySQL, PostgreSQL, etc., Oracle database have some dialect and different concepts, for example, create database command won’t work in Oracle, instead, it uses user as the isolate mechanisim instead of database.

Here Oracle Database basic usages FAQs record:

Oracle create user/database via sqlplus
Oracle JDBC driver oci8 vs. thin?
Oracle sqlplus show non-system users
Oracle sqlplus show tables and views

Oracle create user/database via sqlplus #

Start a oracle container with the following docker-compose.yaml

version: '2'
services:
orclxe:  # su oracle && sqlplus sys/oracle@localhost:1521/XEPDB1 as sysdba
    image: container-registry.oracle.com/database/express:18.4.0-xe
    ports:
    - 1521:1521
    container_name: orclxe
    environment:
    ORACLE_PWD: oracle
    volumes:
    - ${HOME}/data/orclxe:/opt/oracle/oradata  # permission chown -R 54321:54321 ${HOME}/data/orclxe

Connect to the Oracle server via sqlplus

$ docker exec -it orclxe bash
$ su oracle
$ sqlplus / as sysdba  # connect to oracle as sysdba, run this command with oracle user is a MUST. i.e., su oracle

Create a regular user/database

alter session set container=XEPDB1;
create user tes identified by tes;
grant dba to tes;
grant create session to tes;
grant connect, resource to tes;
grant all privileges to tes;

Connect to oracle with this newly created user
```
$ sqlplus tes/tes@localhost:1521/XEPDB1 
```

Oracle JDBC driver oci8 vs. thin? #

Oracle JDBC drivers come in two main types: OCI (Oracle Call Interface) and Thin. Both drivers allow Java applications to connect to an Oracle database, but they have different features and requirements:

OCI Driver (oci8): The OCI driver is a Type 2 JDBC driver that uses the Oracle Call Interface (OCI) native library to communicate with the Oracle database. It requires the Oracle client software to be installed on the client machine. The OCI driver offers better performance compared to the Thin driver, especially for large data transfers and distributed transactions. It also provides support for advanced Oracle features, such as Oracle Real Application Clusters (RAC), Transparent Application Failover (TAF), and client-side caching. However, the OCI driver is platform-dependent and requires additional setup and configuration.
Thin Driver: The Thin driver is a Type 4 JDBC driver that is written entirely in Java and does not require any native libraries or Oracle client software. It communicates with the Oracle database directly using the SQL*Net protocol (usually over TCP/IP). The Thin driver is platform-independent and easier to set up compared to the OCI driver, making it a popular choice for most Java applications. While the Thin driver may not perform as well as the OCI driver for certain operations, it offers good performance for most use cases and supports most of the Oracle features.

When choosing between the OCI and Thin drivers, consider the following factors:

Performance: If you require optimal performance for large data transfers or distributed transactions, the OCI driver may be a better choice.
Advanced features: If you need support for advanced Oracle features, such as RAC or TAF, the OCI driver is the preferred option.
Platform independence: If you need a platform-independent solution that does not require native libraries or Oracle client software, the Thin driver is the way to go.
Ease of setup: If you prefer a simpler setup with fewer configuration requirements, the Thin driver is more suitable.

In general, the Thin driver is the recommended choice for most Java applications due to its platform independence and ease of setup. The OCI driver can be considered if you have specific requirements for performance or advanced Oracle features.

Oracle sqlplus show non-system users #

In Oracle, you can use SQL*Plus to query the DBA_USERS view to list all non-system users. The DBA_USERS view contains information about all users in the Oracle database. By filtering out the users that belong to the SYS and SYSTEM schemas, you can retrieve a list of non-system users.

Here’s a SQL query to show non-system users:

SELECT username FROM DBA_USERS
WHERE username NOT IN ('SYS', 'SYSTEM')
ORDER BY username;

Keep in mind that the DBA_USERS view is only accessible to users with the necessary privileges, such as users with the DBA role.

Oracle sqlplus show tables and views #

In Oracle, you can use the USER_TABLES, USER_VIEWS, ALL_TABLES, ALL_VIEWS, DBA_TABLES, and DBA_VIEWS views to list tables and views for different scopes: current user/schema, specific user/schema, or all users.

To show tables and views in SQL*Plus, follow these steps:

Launch SQL*Plus and connect to the Oracle database:

sqlplus <your_username>/<your_password>@<host:port/><your_tns_alias>

Depending on your requirement, choose one of the following options:

List tables and views for the current user/schema:

SELECT table_name as "Name", 'TABLE' as "Type" FROM USER_TABLES
UNION ALL
SELECT view_name as "Name", 'VIEW' as "Type" FROM USER_VIEWS
ORDER BY "Name";

List tables and views for a specific user/schema (replace <schema_name> with the actual schema name):

SELECT table_name as "Name", 'TABLE' as "Type" FROM ALL_TABLES WHERE owner = '<schema_name>'
UNION ALL
SELECT view_name as "Name", 'VIEW' as "Type" FROM ALL_VIEWS WHERE owner = '<schema_name>'
ORDER BY "Name";

List tables and views for all users (requires DBA privileges):

SELECT owner, table_name as "Name", 'TABLE' as "Type" FROM DBA_TABLES
UNION ALL
SELECT owner, view_name as "Name", 'VIEW' as "Type" FROM DBA_VIEWS
ORDER BY owner, "Name";

The selected query will display a list of tables and views for the specified user/schema or all users in alphabetical order.

Keep in mind that the DBA_TABLES and DBA_VIEWS views are only accessible to users with the necessary privileges, such as users with the DBA role.