Monday, 25 May 2026

Generating OCI Security List JSON Templates Using OCI CLI

Oracle Cloud Infrastructure (OCI) Networking provides Security Lists to control inbound and outbound traffic for resources inside a Virtual Cloud Network (VCN).

Using OCI CLI, administrators can generate ready-made JSON templates for security rules, making automation and Infrastructure as Code much easier.


OCI CLI Command

The following command generates a sample JSON structure for ingress security rules:

oci network security-list create \
--generate-param-json-input ingress-security-rules

Real Example

(base) karandodwal@Karans-MacBook-Air ~ % oci network security-list create \
--generate-param-json-input ingress-security-rules

Sample Output

[
  {
    "description": "string",
    "icmpOptions": {
      "code": 0,
      "type": 0
    },
    "isStateless": true,
    "protocol": "string",
    "source": "string",
    "sourceType": "string",
    "tcpOptions": {
      "destinationPortRange": {
        "max": 0,
        "min": 0
      },
      "sourcePortRange": {
        "max": 0,
        "min": 0
      }
    },
    "udpOptions": {
      "destinationPortRange": {
        "max": 0,
        "min": 0
      },
      "sourcePortRange": {
        "max": 0,
        "min": 0
      }
    }
  }
]

What Does This Command Do?

The command does not create a Security List.

Instead, it generates a JSON template that can later be customized and used with OCI CLI commands.

This is extremely useful for:

  • Automation
  • Infrastructure as Code
  • Terraform-style workflows
  • Repeatable deployments
  • Complex networking configurations

Understanding OCI Security Lists

A Security List in OCI acts like a virtual firewall at the subnet level.

It controls:

  • Ingress traffic (incoming)
  • Egress traffic (outgoing)

OCI Networking Architecture

VCN
 └── Subnet
      └── Security List
            ├── Ingress Rules
            └── Egress Rules

Understanding the JSON Template

The generated JSON contains all possible parameters for an ingress rule.


Field-by-Field Explanation

Field Description
description Description of the security rule
icmpOptions ICMP protocol settings
isStateless Defines whether the rule is stateful or stateless
protocol Network protocol number
source Allowed source CIDR block
sourceType Source type definition
tcpOptions TCP-specific port rules
udpOptions UDP-specific port rules

Understanding the Protocol Field

The protocol field specifies network protocol numbers.

Protocol Value
ICMP 1
TCP 6
UDP 17
All Protocols all

Example TCP Rule

Allow SSH traffic on port 22:

{
  "description": "Allow SSH",
  "protocol": "6",
  "source": "0.0.0.0/0",
  "sourceType": "CIDR_BLOCK",
  "tcpOptions": {
    "destinationPortRange": {
      "min": 22,
      "max": 22
    }
  }
}

Understanding TCP Options

TCP options define source and destination port ranges.

Destination Port Range

Defines which ports are allowed on the destination system.

Example:

"destinationPortRange": {
  "min": 22,
  "max": 22
}

This allows SSH traffic.


Understanding UDP Options

UDP options work similarly to TCP options but apply to UDP traffic.

Examples:

  • DNS
  • NTP
  • Streaming services

Understanding ICMP Options

ICMP rules are used for:

  • Ping requests
  • Network diagnostics
  • Troubleshooting

Example:

"icmpOptions": {
  "type": 3,
  "code": 4
}

Stateful vs Stateless Rules

The template includes:

"isStateless": true

Stateful Rule

  • Return traffic automatically allowed
  • Easier to manage
  • Default behavior in OCI

Stateless Rule

  • Return traffic must be explicitly allowed
  • Higher performance
  • Useful for specialized workloads

Understanding Source and SourceType

Example

"source": "0.0.0.0/0",
"sourceType": "CIDR_BLOCK"

Meaning

  • 0.0.0.0/0 means all IP addresses
  • CIDR_BLOCK specifies source is defined using CIDR notation

Security Warning

Using:

0.0.0.0/0

opens access from the entire internet.

Use carefully, especially for:

  • SSH ports
  • Database ports
  • Application ports

How JSON Templates Are Used

The generated JSON can be:

  • Saved to a file
  • Edited manually
  • Passed back to OCI CLI commands

Save JSON Template to File

oci network security-list create \
--generate-param-json-input ingress-security-rules \
> ingress-rules.json

Edit the JSON File

Modify the template with real values:

{
  "description": "Allow HTTPS",
  "protocol": "6",
  "source": "0.0.0.0/0",
  "sourceType": "CIDR_BLOCK",
  "tcpOptions": {
    "destinationPortRange": {
      "min": 443,
      "max": 443
    }
  }
}

Create Security List Using JSON

oci network security-list create \
--compartment-id <compartment_ocid> \
--vcn-id <vcn_ocid> \
--display-name MySecurityList \
--ingress-security-rules file://ingress-rules.json

Understanding the Warning Message

The command also displayed:

SyntaxWarning: "\." is an invalid escape sequence

Why Does This Warning Appear?

This warning originates from:

  • Python 3.14 compatibility changes
  • OCI CLI internal Python libraries
  • Regular expression formatting updates

It usually does not impact OCI CLI functionality.


Benefits of Generate-Param-JSON-Input

  • Reduces manual syntax errors
  • Shows all available parameters
  • Useful for automation
  • Simplifies complex configurations
  • Ideal for scripting

OCI Security List Best Practices

  • Use least privilege rules
  • Avoid open internet access when unnecessary
  • Use stateful rules unless stateless is required
  • Document security rule purposes
  • Regularly audit security lists

Security Lists vs Network Security Groups (NSGs)

Security Lists NSGs
Subnet-level firewall VNIC-level firewall
Applies to entire subnet Applies to specific resources
Simpler management More granular control

Conclusion

The OCI CLI command:

oci network security-list create \
--generate-param-json-input ingress-security-rules

is a powerful way to generate networking rule templates for OCI Security Lists.

It helps cloud administrators:

  • Understand rule structures
  • Automate networking
  • Create reusable configurations
  • Build Infrastructure as Code workflows

Combined with OCI CLI automation and JSON templates, OCI networking becomes highly flexible and script-friendly for enterprise cloud deployments.

How to List OCI Object Storage Buckets Using OCI CLI

Oracle Cloud Infrastructure (OCI) Object Storage provides highly scalable and durable cloud storage for storing unstructured data such as:

  • Database backups
  • Application files
  • Terraform state files
  • Logs
  • Images and videos
  • Archive files

Using OCI CLI, administrators can quickly list all available buckets directly from the terminal.


OCI CLI Command to List Buckets

The following command lists all Object Storage buckets inside a compartment:

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace <namespace>

Real Example

(base) karandodwal@Karans-MacBook-Air oci % oci os bucket list \
--compartment-id ocid1.tenancy.oc1..aaaaaaaa3vnisivl47yhbkewows6ga6xawrhmg2p37nzqj656rz4j7wt55gq \
--namespace bmsabuehgvp5

Understanding the Command

Parameter Description
oci OCI CLI executable
os Object Storage service
bucket Bucket resource type
list Action to display buckets
--compartment-id OCI compartment or tenancy OCID
--namespace Object Storage namespace

Sample Output

{
  "data": [
    {
      "bucket-scope": "NAMESPACE",
      "compartment-id": "ocid1.tenancy.oc1..aaaaaaaa3vnisivl47yhbkewows6ga6xawrhmg2p37nzqj656rz4j7wt55gq",
      "created-by": "ocid1.saml2idp.oc1..aaaaaaaagk67dtxfsgsyudgppijtx3gkcipqmoq33o2bh2z55pjryeol4juq/karandodwal@gmail.com",
      "etag": "2c6154b3-0051-44fd-82cc-b05d00409442",
      "name": "bucket-1031",
      "namespace": "bmsabuehgvp5",
      "time-created": "2022-06-26T19:07:15.549000+00:00"
    }
  ]
}

Understanding the JSON Output

OCI CLI returns bucket information in JSON format.

The response contains:

"data": [ ]

which is an array of bucket objects.


Important Output Fields Explained

Field Description
bucket-scope Defines bucket visibility scope
compartment-id OCI compartment OCID where bucket exists
created-by User or identity that created the bucket
etag Unique identifier used for object version tracking
name Name of the bucket
namespace Object Storage namespace
time-created Bucket creation timestamp

Understanding the Bucket Name

From the output:

"name": "bucket-1031"

This is the Object Storage bucket name.

The bucket stores objects such as:

  • Files
  • Backups
  • Images
  • Application data

Understanding Namespace

The output shows:

"namespace": "bmsabuehgvp5"

A namespace is a unique Object Storage identifier assigned to an OCI tenancy.

All buckets exist inside a namespace.


How to Get Namespace

oci os ns get

Example output:

{
  "data": "bmsabuehgvp5"
}

Understanding Bucket Scope

The output shows:

"bucket-scope": "NAMESPACE"

This means the bucket exists within the tenancy namespace.


Understanding the Created-By Field

Example:

"created-by": "ocid1.saml2idp.oc1.../karandodwal@gmail.com"

This indicates:

  • The bucket creator identity
  • The user or federated identity used
  • Audit tracking information

Understanding the ETag

Example:

"etag": "2c6154b3-0051-44fd-82cc-b05d00409442"

ETag is used internally by OCI for:

  • Object version tracking
  • Concurrency control
  • API request validation

Understanding Time-Created

Example:

"time-created": "2022-06-26T19:07:15.549000+00:00"

This indicates when the bucket was created in UTC format.


What About the Warning Message?

The command output also displayed:

FutureWarning: The 'strict' parameter is no longer needed on Python 3+

Why Does This Warning Appear?

This is a Python library compatibility warning from:

urllib3

It usually appears because:

  • OCI CLI uses Python internally
  • Python 3.14 introduced changes
  • Some older library parameters are deprecated

This warning does not usually affect bucket operations.


Display Bucket List in Table Format

JSON output is useful for automation, but table output is easier to read.

Example

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace <namespace> \
--output table

Filter Bucket Output Using Queries

Display Only Bucket Names

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace <namespace> \
--query "data[*].name"

Display Only Creation Time

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace <namespace> \
--query "data[*].time-created"

Useful Related Bucket Commands

Get Bucket Details

oci os bucket get \
--bucket-name bucket-1031 \
--namespace bmsabuehgvp5

List Objects Inside Bucket

oci os object list \
--bucket-name bucket-1031

Upload File to Bucket

oci os object put \
--bucket-name bucket-1031 \
--file backup.zip

OCI Object Storage Use Cases

  • RMAN backups
  • Database exports
  • Terraform remote state
  • Log archival
  • Application storage
  • Static website hosting
  • Data lake storage

OCI CLI Benefits

  • Automation friendly
  • Supports scripting
  • Useful for DevOps pipelines
  • Faster than manual console operations
  • Supports Infrastructure as Code workflows

Conclusion

The OCI CLI command:

oci os bucket list

is one of the most commonly used Object Storage operations in Oracle Cloud Infrastructure.

It helps administrators:

  • Discover available buckets
  • Verify storage configuration
  • Manage Object Storage resources
  • Automate cloud storage operations

Combined with Object Storage APIs, Cloud Shell, and automation tools, OCI CLI provides a powerful way to manage enterprise cloud storage directly from the terminal.

Understanding OCI Object Storage Bucket Commands Using OCI CLI

Oracle Cloud Infrastructure (OCI) Object Storage is a highly scalable cloud storage service used to store unstructured data such as:

  • Backups
  • Images
  • Videos
  • Database exports
  • Logs
  • Application files
  • Terraform state files

Using OCI CLI, administrators can manage Object Storage buckets directly from the terminal.


Checking Available Bucket Commands

The following command displays all available bucket operations in OCI CLI:

oci os bucket -h

Example Terminal Output

(base) karandodwal@Karans-MacBook-Air oci % oci os bucket -h
Usage: oci os bucket [OPTIONS] COMMAND [ARGS]...

  A bucket is a container for storing objects in a compartment within a
  namespace. A bucket is associated with a single compartment.

Commands:
  create
  delete
  get
  list
  reencrypt
  update

Understanding OCI Object Storage Buckets

A bucket is a logical container inside OCI Object Storage where objects (files) are stored.

Examples of objects:

  • ZIP files
  • Backups
  • Images
  • JSON files
  • Database dump files
  • Terraform state files

OCI Object Storage Hierarchy

Tenancy
 └── Compartment
      └── Namespace
           └── Bucket
                └── Objects

What the Help Output Explains

The OCI CLI help output provides:

  • Command usage syntax
  • Bucket descriptions
  • Available bucket operations
  • IAM authorization requirements

Basic OCI Bucket Command Structure

oci os bucket <command>

Available Bucket Commands Explained

Command Description
create Create a new bucket
delete Delete an empty bucket
get Display bucket details
list List all buckets in a compartment
reencrypt Re-encrypt bucket encryption keys
update Modify bucket settings

1. Create Bucket

Creates a new Object Storage bucket.

Example

oci os bucket create \
--compartment-id <compartment_ocid> \
--name mybucket \
--namespace-name <namespace>

2. Delete Bucket

Deletes a bucket if it is empty.

Example

oci os bucket delete \
--bucket-name mybucket \
--namespace-name <namespace> \
--force

Important:

  • Bucket must be empty before deletion
  • All objects should be removed first

3. Get Bucket Details

Displays detailed information about a bucket.

Example

oci os bucket get \
--bucket-name mybucket \
--namespace-name <namespace>

4. List Buckets

Lists all buckets inside a compartment.

Example

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace-name <namespace>

5. Re-encrypt Bucket

The reencrypt command is used to re-encrypt bucket encryption keys.

This is useful when:

  • Rotating encryption keys
  • Improving security compliance
  • Changing KMS configurations

Example

oci os bucket reencrypt \
--bucket-name mybucket \
--namespace-name <namespace>

6. Update Bucket

Updates bucket settings and properties.

Example

oci os bucket update \
--bucket-name mybucket \
--namespace-name <namespace> \
--public-access-type NoPublicAccess

IAM Authorization Requirement

The help output also mentions:

To use any of the API operations, you must be authorized in an IAM policy.

This means OCI users need proper IAM permissions to work with buckets.


Example IAM Policy for Object Storage

Allow group StorageAdmins to manage object-family in compartment Production

Understanding OCI Namespace

Object Storage buckets exist inside a namespace.

A namespace is automatically generated for each OCI tenancy.


Get Namespace

oci os ns get

Example output:

{
  "data": "mytenancynamespace"
}

Useful Related OCI Object Storage Commands

List Objects in a Bucket

oci os object list \
--bucket-name mybucket

Upload Object

oci os object put \
--bucket-name mybucket \
--file backup.zip

Download Object

oci os object get \
--bucket-name mybucket \
--name backup.zip \
--file backup.zip

Display Bucket Commands Help

You can get detailed help for individual bucket commands.

Example

oci os bucket create --help

Display Output in Table Format

OCI CLI supports table formatting for readability.

Example

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace-name <namespace> \
--output table

Why OCI CLI is Useful for Object Storage

  • Automates storage management
  • Useful for scripting
  • Supports backups and restore automation
  • Works well with DevOps pipelines
  • Faster than manual console operations
  • Supports Terraform and Infrastructure as Code

OCI CLI and Cloud Shell

OCI CLI can run from:

  • macOS Terminal
  • Linux servers
  • Windows WSL
  • Oracle Cloud Shell

Oracle Cloud Shell already includes OCI CLI pre-installed and authenticated.


Best Practices for OCI Buckets

  • Use private buckets unless public access is required
  • Enable encryption
  • Use IAM least privilege policies
  • Organize buckets by workload
  • Monitor bucket usage
  • Use lifecycle policies for cost optimization

Conclusion

The command:

oci os bucket -h

is an excellent starting point for learning OCI Object Storage management using OCI CLI.

It provides visibility into all major bucket operations including:

  • Bucket creation
  • Listing buckets
  • Updating bucket settings
  • Deleting buckets
  • Encryption management

OCI Object Storage combined with OCI CLI provides a powerful and automation-friendly cloud storage platform for enterprise workloads and cloud-native applications.

Exploring OCI Regions Using OCI CLI

Oracle Cloud Infrastructure (OCI) provides cloud regions across multiple countries and continents to support high availability, disaster recovery, low latency, and global cloud deployments.

Using OCI CLI, administrators can quickly list all available Oracle Cloud regions directly from the terminal.


OCI CLI Command to List Regions

The following command displays all OCI regions available globally:

oci iam region list

Sample Output

karandodwa@cloudshell:~ (ap-mumbai-1)$ oci iam region list

The command returns JSON output similar to:

{
  "data": [
    {
      "key": "BOM",
      "name": "ap-mumbai-1"
    },
    {
      "key": "HYD",
      "name": "ap-hyderabad-1"
    },
    {
      "key": "ONM",
      "name": "ap-delhi-1"
    },
    {
      "key": "IAD",
      "name": "us-ashburn-1"
    },
    {
      "key": "PHX",
      "name": "us-phoenix-1"
    }
  ]
}

The actual output contains dozens of OCI regions distributed globally.


Understanding the Output

Each OCI region entry contains:

Field Description
key Short region identifier
name Full OCI region name

Example Region Entry

{
  "key": "BOM",
  "name": "ap-mumbai-1"
}

Meaning of This Region

  • BOM → Short code for Mumbai
  • ap → Asia Pacific
  • mumbai → Region city/location
  • 1 → Region sequence number

OCI Region Naming Convention

OCI regions generally follow this format:

<geography>-<city>-<number>

Examples

Region Name Description
ap-mumbai-1 Asia Pacific - Mumbai
ap-hyderabad-1 Asia Pacific - Hyderabad
us-ashburn-1 United States - Ashburn
eu-frankfurt-1 Europe - Frankfurt
me-dubai-1 Middle East - Dubai

OCI Regions Available in India

From the output, Oracle currently provides multiple cloud regions in India:

Key Region Location
BOM ap-mumbai-1 Mumbai
HYD ap-hyderabad-1 Hyderabad
ONM ap-delhi-1 Delhi NCR
DES ap-chennai-1 Chennai

Benefits of Multiple OCI Regions

1. High Availability

Applications can be deployed across multiple regions for better uptime.


2. Disaster Recovery

Organizations can replicate workloads between regions.


3. Low Latency

Users can choose cloud regions closer to end users.


4. Regulatory Compliance

Data residency requirements can be satisfied using local regions.


Popular OCI Regions from the Output

Key Region Country/Area
IAD us-ashburn-1 USA
PHX us-phoenix-1 USA
FRA eu-frankfurt-1 Germany
LHR uk-london-1 United Kingdom
NRT ap-tokyo-1 Japan
SIN ap-singapore-1 Singapore
SYD ap-sydney-1 Australia
JNB af-johannesburg-1 South Africa

OCI Geographic Categories

OCI region prefixes indicate geographical areas:

Prefix Meaning
ap Asia Pacific
us United States
eu Europe
me Middle East
sa South America
ca Canada
af Africa
mx Mexico
uk United Kingdom

Filter OCI Regions Using Queries

OCI CLI supports JMESPath queries for filtering output.

Display Only Region Names

oci iam region list \
--query "data[*].name"

Display Only Region Keys

oci iam region list \
--query "data[*].key"

Display Output in Table Format

oci iam region list --output table

Get Current Configured OCI Region

You can verify your configured default region using:

cat ~/.oci/config

Example:

region=ap-mumbai-1

Switch OCI Region Temporarily

You can target another region using:

oci --region us-ashburn-1 iam region list

Why OCI Regions Matter

Choosing the correct OCI region affects:

  • Application performance
  • Latency
  • Disaster recovery architecture
  • Compliance requirements
  • Cost optimization
  • Service availability

OCI Multi-Region Architecture

Many enterprise deployments use multiple OCI regions for:

  • Cross-region replication
  • Backup strategies
  • Autonomous Database replication
  • Disaster recovery
  • Global application deployments

Oracle Cloud Shell and OCI CLI

The command was executed from Oracle Cloud Shell:

karandodwa@cloudshell:~ (ap-mumbai-1)$

This indicates:

  • The user is logged into Cloud Shell
  • The current configured region is Mumbai
  • OCI CLI is already installed and authenticated

Conclusion

The command:

oci iam region list

is one of the first and most useful OCI CLI commands for understanding Oracle Cloud Infrastructure global availability.

It helps administrators:

  • Discover OCI regions
  • Plan deployments
  • Select low-latency regions
  • Design disaster recovery architectures
  • Build multi-region cloud solutions

With OCI continuously expanding globally, understanding OCI regions becomes an important foundation for cloud architecture and administration.

Getting Started with Basic OCI CLI Commands

Oracle Cloud Infrastructure (OCI) provides a powerful command line interface called OCI CLI that allows administrators, developers, and cloud engineers to manage cloud resources directly from the terminal.

OCI CLI is extremely useful for:

  • Cloud automation
  • Managing compute instances
  • Working with Object Storage
  • Managing networking resources
  • Database administration
  • Terraform integrations
  • Scripting and DevOps operations

What is OCI CLI?

OCI CLI stands for:

Oracle Cloud Infrastructure Command Line Interface

It allows you to interact with OCI services using commands instead of the web console.


OCI CLI Command Structure

Most OCI CLI commands follow this structure:

oci <service> <resource> <action>

Example

oci iam region list

Breakdown:

  • iam → OCI Identity service
  • region → Resource type
  • list → Action

Check OCI CLI Installation

To verify OCI CLI is installed:

oci --version

Display OCI CLI Help

oci --help

or simply:

oci

Useful Global OCI CLI Options

Option Description
--help Show help information
--version Display OCI CLI version
--output table Display output in table format
--debug Enable debug output
--region Specify OCI region
-i Interactive mode

Enable Interactive Mode

OCI CLI supports interactive mode for auto-completion and easier command discovery.

oci -i

Basic OCI Commands

1. List OCI Regions

oci iam region list

2. Display Output in Table Format

oci iam region list --output table

3. Get Current Object Storage Namespace

oci os ns get

4. List Compartments

oci iam compartment list --all

5. List Availability Domains

oci iam availability-domain list

Working with Compute Instances

List Compute Instances

oci compute instance list \
--compartment-id <compartment_ocid>

Get Instance Details

oci compute instance get \
--instance-id <instance_ocid>

Start a Compute Instance

oci compute instance action \
--instance-id <instance_ocid> \
--action START

Stop a Compute Instance

oci compute instance action \
--instance-id <instance_ocid> \
--action STOP

Working with Object Storage

List Buckets

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace-name <namespace>

Create a Bucket

oci os bucket create \
--compartment-id <compartment_ocid> \
--name mybucket \
--namespace-name <namespace>

Upload a File

oci os object put \
--bucket-name mybucket \
--file test.txt

List Objects Inside Bucket

oci os object list \
--bucket-name mybucket

Working with Networking

List Virtual Cloud Networks (VCNs)

oci network vcn list \
--compartment-id <compartment_ocid>

List Subnets

oci network subnet list \
--compartment-id <compartment_ocid>

List Internet Gateways

oci network internet-gateway list \
--compartment-id <compartment_ocid>

Working with Databases

List Autonomous Databases

oci db autonomous-database list \
--compartment-id <compartment_ocid>

List DB Systems

oci db system list \
--compartment-id <compartment_ocid>

Using Queries in OCI CLI

OCI CLI supports JMESPath queries for filtering output.

Example

oci iam compartment list \
--all \
--query "data[*].name"

Get Only OCIDs

oci iam compartment list \
--all \
--query "data[*].id"

Using Raw Output

oci os ns get --query data --raw-output

This returns only the namespace value without JSON formatting.


Generate JSON Templates

OCI CLI can automatically generate JSON input templates.

Example

oci os bucket create \
--generate-full-command-json-input

Working with Configuration Files

OCI CLI configuration file location:

~/.oci/config

Typical configuration:

[DEFAULT]
user=ocid1.user.oc1...
fingerprint=xx:xx:xx
tenancy=ocid1.tenancy.oc1...
region=ap-mumbai-1
key_file=/home/opc/.oci/oci_api_key.pem

OCI CLI Authentication Types

OCI CLI supports multiple authentication methods:

Authentication Type Description
api_key Standard API key authentication
instance_principal Authentication from OCI compute instances
security_token Token-based authentication
resource_principal Used for OCI services and functions

OCI Cloud Shell Advantage

OCI Cloud Shell already includes:

  • OCI CLI pre-installed
  • Authentication pre-configured
  • Terraform tools
  • kubectl
  • Git
  • Python SDKs

This makes it very easy to start working with OCI immediately.


Useful Troubleshooting Commands

Enable Debugging

oci --debug iam region list

Check Current Region

cat ~/.oci/config

Validate Authentication

oci iam region list

Best Practices

  • Use OCI CLI interactive mode for learning
  • Store scripts securely
  • Avoid exposing OCIDs publicly
  • Use instance principals where possible
  • Use table output for readability
  • Use queries to simplify output

Conclusion

OCI CLI is one of the most powerful tools available for managing Oracle Cloud Infrastructure resources.

With simple commands, administrators can manage:

  • Compute instances
  • Networking
  • Object Storage
  • Databases
  • Kubernetes
  • Security services

Combined with Oracle Cloud Shell, OCI CLI becomes an excellent platform for automation, cloud administration, and DevOps operations.

Learning basic OCI commands is the first step toward mastering Oracle Cloud Infrastructure administration and automation.

Understanding Encryption Algorithms in Oracle Autonomous Database and OCI

Data encryption is one of the most critical security features in modern cloud databases. Oracle Cloud Infrastructure (OCI) and Oracle Autonomous Database use Transparent Data Encryption (TDE) to secure data stored inside database tablespaces.

When we query the dynamic performance view V$ENCRYPTED_TABLESPACES, we can see the encryption algorithm currently being used by Oracle Database.


Checking Tablespace Encryption Details

The following query displays encrypted tablespace information:

SQL> select * from v$encrypted_tablespaces;

Sample output:

TS# ENCRYPTIONALG ENCRYPTEDTS STATUS  CIPHERMODE CON_ID
--- ------------- ----------- ------- ----------- ------
0   AES256        YES         NORMAL  XTS         600
1   AES256        YES         NORMAL  XTS         600
4   AES256        YES         NORMAL  XTS         600
5   AES256        YES         NORMAL  XTS         600
6   AES256        YES         NORMAL  XTS         600
9   AES256        YES         NORMAL  XTS         600
10  AES256        YES         NORMAL  XTS         600

What Does This Output Tell Us?

From the output, we can clearly see:

  • Tablespaces are encrypted
  • Encryption algorithm used is AES256
  • Cipher mode is XTS
  • Tablespace status is NORMAL

This confirms that Oracle Autonomous Database and OCI databases use enterprise-grade encryption by default.


Important Columns Explained

Column Description
ENCRYPTIONALG Encryption algorithm used for tablespace encryption
ENCRYPTEDTS Indicates whether the tablespace is encrypted
ENCRYPTEDKEY Internal encrypted encryption key
MASTERKEYID Master encryption key identifier
BLOCKS_ENCRYPTED Number of encrypted blocks
BLOCKS_DECRYPTED Number of decrypted blocks accessed
STATUS Encryption status of the tablespace
CIPHERMODE Cipher mode used during encryption
CON_ID Container ID in multitenant architecture

Why Does Oracle OCI Use AES256?

OCI and Autonomous Database primarily use:

AES256 + XTS Cipher Mode

because it provides:

  • Very strong encryption security
  • Industry-standard compliance
  • Efficient hardware acceleration
  • High performance with modern CPUs
  • Strong protection against storage-level attacks

What is AES?

AES stands for:

Advanced Encryption Standard

AES is a symmetric encryption algorithm widely used across:

  • Cloud providers
  • Banking systems
  • Government systems
  • Enterprise databases
  • Military-grade security solutions

Available AES Encryption Strengths in Oracle Database

Oracle Database supports multiple AES key sizes:

Algorithm Key Size Security Level
AES128 128-bit Strong
AES192 192-bit Very Strong
AES256 256-bit Maximum Enterprise Security

AES128 vs AES192 vs AES256

AES128

  • Fastest AES variant
  • Lower CPU overhead
  • Still considered secure
  • Often used in general applications

AES192

  • Balanced option between speed and security
  • Less commonly used
  • Higher cryptographic strength than AES128

AES256

  • Highest encryption strength
  • Preferred for enterprise databases
  • Used in OCI Autonomous Database
  • Recommended for sensitive data
  • Widely accepted for compliance requirements

Does Oracle Support Other Encryption Algorithms?

Yes. Oracle Database historically supported multiple encryption algorithms through TDE and Oracle Wallet technologies.

Some supported algorithms include:

Algorithm Description
AES128 128-bit AES encryption
AES192 192-bit AES encryption
AES256 256-bit AES encryption
3DES168 Triple DES encryption
ARIA128 Korean standard encryption algorithm
ARIA192 192-bit ARIA encryption
ARIA256 256-bit ARIA encryption
GOST256 Russian standard encryption algorithm
SEED128 Korean block cipher standard

Why Are Older Algorithms Rarely Used?

Modern cloud environments prefer AES because:

  • Better performance
  • Hardware acceleration support
  • Higher industry adoption
  • Better security validation
  • Compliance acceptance

Algorithms like DES and 3DES are considered older and slower compared to AES.


What is XTS Cipher Mode?

The output also shows:

CIPHERMODE = XTS

XTS Explained

XTS stands for:

XEX-based Tweaked CodeBook mode with CipherText Stealing

It is specifically designed for storage encryption.

XTS provides:

  • Improved storage block protection
  • Better resistance against block manipulation
  • Enhanced disk-level encryption security
  • Better suitability for database storage

Why XTS is Better for Databases

Databases work with storage blocks continuously. Traditional cipher modes like CBC were not optimized for storage encryption.

XTS improves security for:

  • Tablespace files
  • Datafiles
  • ASM storage
  • Redo logs
  • Temporary files

Understanding BLOCKS_ENCRYPTED and BLOCKS_DECRYPTED

Example:

BLOCKS_ENCRYPTED   BLOCKS_DECRYPTED
----------------   ----------------
107896             333437

These columns indicate:

  • How many blocks have been encrypted
  • How many encrypted blocks were later decrypted during access

Decryption happens automatically when Oracle reads encrypted data into memory. Applications never notice this process because TDE is transparent.


What is MASTERKEYID?

Example:

MASTERKEYID
-----------------------------------
F79629044E3C4F9ABFC5AEB94442C972

This identifies the master encryption key used to protect tablespace encryption keys.

Oracle stores and manages these keys securely using:

  • Oracle Wallet
  • OCI Vault
  • Key Management Services (KMS)

Encryption in Autonomous Database

Oracle Autonomous Database automatically enables:

  • Transparent Data Encryption (TDE)
  • Encrypted backups
  • Encrypted redo logs
  • Encrypted temporary tablespaces
  • Encrypted undo tablespaces

No manual encryption setup is usually required.


Benefits of Oracle Cloud Encryption

  • Automatic encryption by default
  • Strong AES256 protection
  • XTS cipher mode for storage security
  • Integrated key management
  • Compliance-ready architecture
  • Minimal application impact
  • Transparent encryption and decryption

Conclusion

The V$ENCRYPTED_TABLESPACES view clearly shows that Oracle Autonomous Database and OCI databases use:

AES256 encryption with XTS cipher mode

This combination provides enterprise-grade security for protecting database storage.

Although Oracle supports multiple encryption algorithms such as AES128, AES192, ARIA, GOST, and 3DES, modern Oracle Cloud environments primarily standardize on AES256 because of its:

  • Strong security
  • High performance
  • Industry acceptance
  • Compliance compatibility

Combined with Transparent Data Encryption (TDE), Oracle Cloud databases provide secure, automatic, and efficient encryption for modern enterprise workloads.

Getting Started with Oracle Cloud Shell in Oracle Cloud Infrastructure (OCI)

Oracle Cloud Infrastructure (OCI) provides a powerful browser-based terminal called Oracle Cloud Shell that allows administrators, developers, and cloud engineers to work directly from the OCI Console without installing any local tools.

Cloud Shell comes preconfigured with useful utilities such as:

  • OCI CLI
  • Terraform
  • Git
  • Kubectl
  • Python SDKs
  • Java SDKs
  • SQL tools
  • Linux utilities

This makes Oracle Cloud Shell one of the easiest ways to start managing Oracle Cloud resources.


Launching Oracle Cloud Shell

When Cloud Shell starts, you will see a welcome screen similar to the following:

Welcome to Oracle Cloud Shell.

Your Cloud Shell machine comes with 5GB of storage for your home directory.
Your Cloud Shell (machine and home directory) are located in: India West (Mumbai).

You are using Cloud Shell in tenancy karandodwal as OCI local user
karandodwal@gmail.com

Type `help` for more info.

Understanding the Welcome Message

1. Browser-Based Linux Terminal

Oracle Cloud Shell provides a fully functional Linux terminal directly inside the OCI web console. No SSH setup or local installation is required.


2. Persistent 5GB Storage

Your Cloud Shell machine comes with 5GB of storage for your home directory.

Oracle provides persistent storage for your home directory. This means:

  • Your scripts remain saved
  • Terraform files persist
  • Downloaded files remain available
  • Git repositories stay intact
  • OCI CLI configurations are preserved

Even after you close the browser session, your files remain available.


3. Region Information

Your Cloud Shell (machine and home directory) are located in:
India West (Mumbai)

The Cloud Shell environment runs in your OCI region. In this example, the Cloud Shell session is hosted in:

  • OCI Region: India West (Mumbai)
  • Region Identifier: ap-mumbai-1

This helps reduce latency when working with resources in the same region.


4. Tenancy and User Information

You are using Cloud Shell in tenancy karandodwal
as OCI local user karandodwal@gmail.com

This confirms:

  • The OCI tenancy being used
  • The authenticated OCI user
  • The identity under which OCI CLI commands will execute

Cloud Shell automatically authenticates your OCI session, so there is usually no need to manually configure API keys.


Cloud Shell Tutorial Prompt

When Cloud Shell launches for the first time, OCI may offer an interactive tutorial:

==================================================================

Welcome to the Oracle Cloud Shell Tutorial

Cloud Shell is a web-based terminal which includes many useful tools
including current versions of the OCI CLI and SDKs.

Would you like to run a tutorial to learn more about all the features
included in Cloud Shell? (Type N to quit) [Y|N] N

If you want to run it in the future you can just type cstutorial.

What is cstutorial?

Oracle provides a built-in interactive learning tutorial for Cloud Shell.

You can launch it anytime using:

cstutorial

The tutorial explains:

  • OCI CLI basics
  • File management
  • Cloud Shell features
  • Editor usage
  • Terminal customization
  • Working with OCI resources

Checking OCI CLI in Cloud Shell

One of the biggest advantages of Cloud Shell is that the OCI CLI is already installed and configured.

Running the command:

oci

displays the OCI CLI help menu:

karandodwa@cloudshell:~ (ap-mumbai-1)$ oci
Usage: oci [OPTIONS] COMMAND [ARGS]...

What is OCI CLI?

OCI CLI (Oracle Cloud Infrastructure Command Line Interface) is a tool that allows you to manage OCI services directly from the terminal.

Instead of using the web console, administrators can automate operations using scripts and commands.


OCI CLI Architecture

OCI CLI commands generally follow this structure:

oci <service> <resource> <action>

Example

oci iam user list --compartment-id <compartment_ocid>

This command:

  • Uses the IAM service
  • Works with users
  • Performs the list action

Interactive OCI CLI Mode

OCI CLI supports an interactive mode that helps users with command completion and syntax guidance.

Example:

oci -i

This mode is very useful for beginners learning OCI CLI commands.


Important OCI CLI Options

Check OCI CLI Version

oci --version

Display Output in Table Format

oci iam region list --output table

Enable Debug Mode

oci --debug

Useful for troubleshooting API requests and authentication issues.


Specify OCI Region

oci --region ap-mumbai-1

Allows commands to target a specific OCI region.


OCI CLI Services Available in Cloud Shell

The OCI CLI supports almost every Oracle Cloud service.

From the output, we can see major service categories such as:

  • Compute
  • Networking
  • Storage
  • Database
  • AI Services
  • Monitoring
  • Security
  • DevOps
  • Kubernetes
  • Load Balancing
  • Object Storage
  • Generative AI

Examples of OCI CLI Services

Service Description
compute Manage OCI Compute Instances
network Manage VCNs, subnets, gateways, route tables
os Manage Object Storage buckets and files
db Manage Oracle Database Services
ce Manage Kubernetes Engine (OKE)
vault Manage OCI Vault and secrets
logging Manage OCI logging services
generative-ai Manage OCI Generative AI services

Example OCI CLI Commands

List OCI Regions

oci iam region list

List Compartments

oci iam compartment list --all

List Compute Instances

oci compute instance list \
--compartment-id <compartment_ocid>

List Object Storage Buckets

oci os bucket list \
--compartment-id <compartment_ocid> \
--namespace-name <namespace>

Why Use Oracle Cloud Shell?

1. No Local Installation

No need to install:

  • OCI CLI
  • Terraform
  • SDKs
  • kubectl

2. Pre-Authenticated Environment

Cloud Shell automatically authenticates to OCI using your logged-in OCI identity.


3. Accessible Anywhere

You only need:

  • A browser
  • OCI Console access

4. Ideal for Automation

Cloud Shell is excellent for:

  • Automation scripts
  • Terraform deployments
  • Kubernetes management
  • Database administration
  • OCI resource management

Cloud Shell for Database Administrators

Oracle Cloud Shell is extremely useful for DBAs working with:

  • Autonomous Database
  • Base Database Service
  • Exadata Cloud Service
  • Oracle Database@AWS
  • Oracle Database@Azure

DBAs can use OCI CLI to:

  • Create databases
  • Manage backups
  • Scale databases
  • Monitor resources
  • Automate administration tasks

Cloud Shell Security Benefits

  • No need to store API keys locally
  • Integrated OCI authentication
  • Runs inside Oracle Cloud Infrastructure
  • Secure browser-based access
  • Temporary compute environment with persistent storage

Conclusion

Oracle Cloud Shell provides a powerful and convenient way to manage Oracle Cloud Infrastructure directly from the browser.

With built-in OCI CLI, SDKs, Terraform, Kubernetes tools, and persistent storage, Cloud Shell eliminates the complexity of setting up local environments.

Whether you are a cloud administrator, developer, DevOps engineer, or DBA, Oracle Cloud Shell offers a secure and efficient environment for managing OCI resources and automating cloud operations.

Combined with OCI CLI support for hundreds of Oracle Cloud services, Cloud Shell becomes an essential productivity tool for modern cloud administration.

Understanding Tablespace Encryption in Oracle Autonomous Database

Security is one of the most important aspects of modern cloud databases. In Oracle Autonomous Database, encryption is enabled by default to protect data at rest and ensure compliance with enterprise security standards.

Oracle Autonomous Database uses Transparent Data Encryption (TDE) to automatically encrypt database files, tablespaces, backups, redo logs, and temporary data without requiring changes to applications.


What is Tablespace Encryption?

Tablespace encryption protects the physical database files stored on disk. Even if someone gains access to the storage files, the data remains unreadable without the encryption keys.

Oracle uses Transparent Data Encryption (TDE) for this purpose. Encryption and decryption happen automatically in the background, making it completely transparent to applications and users.

In Oracle Autonomous Database, most system tablespaces are already encrypted by default.


Checking Encryption Parameters

You can verify encryption-related initialization parameters using:

SQL> show parameter encrypt

NAME                                      TYPE   VALUE
----------------------------------------- ------ ------
encrypt_new_tablespaces                   string ALWAYS
tablespace_encryption_default_algorithm   string AES256
tablespace_encryption_default_cipher_mode string XTS

These parameters define how new tablespaces are encrypted in the database.


1. ENCRYPT_NEW_TABLESPACES Parameter

The ENCRYPT_NEW_TABLESPACES parameter controls whether newly created user tablespaces are automatically encrypted.

Current Value

encrypt_new_tablespaces = ALWAYS

This means every newly created tablespace will automatically be encrypted even if the CREATE TABLESPACE statement does not explicitly mention encryption.


Parameter Syntax

ENCRYPT_NEW_TABLESPACES = { CLOUD_ONLY | ALWAYS | DDL }

Possible Values Explained

1. CLOUD_ONLY

This is the default behavior for many Oracle Cloud environments.

  • Tablespaces created in Oracle Cloud are automatically encrypted using AES128.
  • On-premises databases follow the encryption settings specified in the CREATE TABLESPACE statement.
  • If encryption is not specified on-premises, the tablespace may remain unencrypted.

2. ALWAYS

This is the most secure option and commonly used in Autonomous Database.

  • Every newly created user tablespace is automatically encrypted.
  • Works both in Oracle Cloud and on-premises databases.
  • Even if no ENCRYPTION clause is specified, Oracle encrypts the tablespace automatically.

Example:

CREATE TABLESPACE secure_tbs
DATAFILE 'secure01.dbf' SIZE 100M;

Even though encryption was not specified, the tablespace will still be encrypted because:

ENCRYPT_NEW_TABLESPACES = ALWAYS

3. DDL

This option gives full control to the DBA.

  • Oracle follows exactly what is specified in the CREATE TABLESPACE statement.
  • If encryption is not specified, the tablespace will not be encrypted.
  • If ENCRYPTION USING clause is specified, Oracle uses that algorithm.

Example:

CREATE TABLESPACE finance_tbs
DATAFILE 'finance01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

2. TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM

This parameter defines the default encryption algorithm used for tablespace encryption.

tablespace_encryption_default_algorithm = AES256

Oracle supports multiple AES encryption strengths:

  • AES128
  • AES192
  • AES256

In Autonomous Database, AES256 provides very strong encryption and is widely accepted for enterprise-grade security and compliance requirements.


Why AES256?

  • Stronger encryption strength
  • Industry-standard security
  • Suitable for sensitive enterprise workloads
  • Compliance-friendly for regulated environments

3. TABLESPACE_ENCRYPTION_DEFAULT_CIPHER_MODE

This parameter defines the cipher mode used by the encryption algorithm.

tablespace_encryption_default_cipher_mode = XTS

What is XTS Mode?

XTS (XEX-based Tweaked CodeBook mode with CipherText Stealing) is a modern encryption mode designed specifically for storage encryption.

It provides:

  • Better protection for database storage blocks
  • Improved resistance against block manipulation attacks
  • Enhanced security for encrypted filesystems and tablespaces

XTS is considered more secure than older CBC-based encryption modes for storage encryption use cases.


Checking Tablespace Encryption Status

You can verify which tablespaces are encrypted using:

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME    ENCRYPTED
__________________ ____________
SYSTEM             YES
SYSAUX             YES
DATA               YES
DBFS_DATA          YES
TEMP               YES
SAMPLESCHEMA       NO
UNDO_21871         YES
UNDO_4F8D9         YES

8 rows selected.

Understanding the Output

Tablespace Encrypted Description
SYSTEM YES Contains core database dictionary objects
SYSAUX YES Auxiliary system tablespace
DATA YES Main user/application data tablespace
DBFS_DATA YES Database File System storage
TEMP YES Temporary tablespace used for sorting and operations
SAMPLESCHEMA NO User-created tablespace not encrypted
UNDO_21871 YES Undo tablespace for transaction rollback
UNDO_4F8D9 YES Additional undo tablespace

Why is SAMPLESCHEMA Not Encrypted?

The SAMPLESCHEMA tablespace shows:

SAMPLESCHEMA       NO

Possible reasons:

  • The tablespace was created before encryption policies changed.
  • The database previously used DDL mode.
  • The tablespace was explicitly created without encryption.

Creating an Encrypted Tablespace

Example of explicitly creating an encrypted tablespace:

CREATE TABLESPACE secure_data
DATAFILE 'secure_data01.dbf' SIZE 500M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);

Benefits of Encryption in Autonomous Database

  • Automatic data protection
  • No application changes required
  • Protection against stolen storage files
  • Compliance with security regulations
  • Integrated with Oracle Key Management
  • Secure backups and redo logs
  • Enterprise-grade AES256 encryption

Autonomous Database Security Advantage

One of the major advantages of Oracle Autonomous Database is that security best practices are enabled automatically.

Features such as:

  • Automatic tablespace encryption
  • Automatic patching
  • Automatic backups
  • Integrated key management
  • Secure default configurations

help reduce operational overhead while improving overall database security posture.


Conclusion

Oracle Autonomous Database provides strong built-in encryption capabilities using Transparent Data Encryption (TDE). Parameters such as:

  • ENCRYPT_NEW_TABLESPACES
  • TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
  • TABLESPACE_ENCRYPTION_DEFAULT_CIPHER_MODE

allow administrators to control how encryption behaves for newly created tablespaces.

With settings like:

ENCRYPT_NEW_TABLESPACES = ALWAYS
AES256 encryption
XTS cipher mode

Oracle Autonomous Database ensures enterprise-grade protection for sensitive data stored inside the database.

Sunday, 24 May 2026

Understanding GATHER_TABLE_STATS with GRANULARITY in Oracle Database

Optimizer statistics are one of the most important components of Oracle Database performance tuning.

Oracle’s Cost-Based Optimizer (CBO) depends heavily on accurate statistics to generate efficient execution plans.

In this blog, we will explore:

  • What table statistics are
  • Why statistics matter
  • What GRANULARITY means in DBMS_STATS
  • How GATHER_TABLE_STATS works
  • Understanding DEGREE parameter
  • Reading DBA_TAB_STATISTICS
  • Analyzing real output from Oracle Autonomous Database

Gathering Table Statistics

The following command was executed:

SQL> exec dbms_stats.gather_table_stats(
    'TEST_USER',
    'TAB1',
    degree=>4,
    granularity=>'ALL'
)

Output:

PL/SQL procedure successfully completed.

What Does GATHER_TABLE_STATS Do?

The procedure:

DBMS_STATS.GATHER_TABLE_STATS

collects optimizer statistics for a table.

These statistics help Oracle understand:

  • How many rows exist
  • How much storage is used
  • Average row size
  • Data distribution
  • Whether statistics are stale
  • How expensive a query may be

Oracle’s optimizer uses this information to choose:

  • Index scans
  • Full table scans
  • Hash joins
  • Nested loops
  • Parallel execution plans

Understanding the Parameters

1. Schema Name

'TEST_USER'

This is the schema owner.


2. Table Name

'TAB1'

The target table whose statistics will be gathered.


3. DEGREE => 4

degree=>4

This enables parallelism during statistics gathering.

Oracle can use:

  • 4 parallel execution workers

to scan the table faster.

This is especially useful for:

  • Large tables
  • Data warehouse systems
  • Partitioned tables
  • High-volume analytics

4. GRANULARITY => 'ALL'

granularity=>'ALL'

This is one of the most important parts of the command.

GRANULARITY determines:

At which levels Oracle gathers statistics.

What Does GRANULARITY Mean?

For partitioned tables, Oracle can gather statistics at multiple levels:

  • Global level
  • Partition level
  • Subpartition level

The GRANULARITY parameter controls which levels are analyzed.


Common GRANULARITY Options

Granularity Meaning
AUTO Oracle decides automatically
GLOBAL Only global table statistics
PARTITION Only partition statistics
SUBPARTITION Only subpartition statistics
ALL Global + Partition + Subpartition statistics

What Happens with GRANULARITY='ALL'?

When using:

granularity=>'ALL'

Oracle gathers:

  • Table-level statistics
  • Partition-level statistics
  • Subpartition-level statistics

if the table is partitioned.

For non-partitioned tables, Oracle simply gathers normal table statistics.


Checking Row Count

The table contains:

SQL> select count(*) from test_user.tab1;

COUNT(*)
--------
93743

This confirms:

  • 93,743 rows exist in the table

Understanding DBA_TAB_STATISTICS

Oracle stores optimizer statistics metadata in:

DBA_TAB_STATISTICS

This view contains:

  • Row counts
  • Block counts
  • Sampling information
  • Last analyzed time
  • Stale statistics status

Checking Gathered Statistics

The following query was executed:

SQL> select
       TABLE_NAME,
       AVG_ROW_LEN,
       SAMPLE_SIZE,
       LAST_ANALYZED,
       STALE_STATS,
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS
     from dba_tab_statistics
     where table_name='TAB1';

Output

TABLE_NAME       AVG_ROW_LEN    SAMPLE_SIZE LAST_ANALYZED    STALE_STATS       NUM_ROWS    BLOCKS    EMPTY_BLOCKS
_____________ ______________ ______________ ________________ ______________ ___________ _________ _______________

TAB1                     158          93743 24-05-26         NO                   93743       108               0

Analyzing the Statistics Output

1. AVG_ROW_LEN

158

This means:

  • Average row length is 158 bytes

Oracle uses this to estimate:

  • I/O costs
  • Memory usage
  • Join costs
  • Hash area requirements

2. SAMPLE_SIZE

93743

Oracle sampled:

  • 93,743 rows

which equals the entire table.

This indicates:

Oracle performed a full statistics collection rather than partial sampling.

3. LAST_ANALYZED

24-05-26

This shows when statistics were last gathered.

The optimizer uses this timestamp to determine statistics freshness.


4. STALE_STATS

NO

This means:

  • Statistics are current
  • Oracle considers them fresh

If significant table modifications occur later:

  • INSERTS
  • UPDATES
  • DELETES

Oracle may eventually mark statistics as:

YES

5. NUM_ROWS

93743

Oracle estimates the table contains:

  • 93,743 rows

This matches the COUNT(*) query exactly.


6. BLOCKS

108

The table occupies:

  • 108 Oracle blocks

Oracle uses this for:

  • Cost calculations
  • Full scan estimates
  • I/O predictions

7. EMPTY_BLOCKS

0

This indicates:

  • No completely empty blocks exist

which is usually a healthy sign for storage utilization.


Why Statistics Matter So Much

Without statistics, Oracle’s optimizer may generate poor execution plans.

Examples:

  • Choosing full table scans unnecessarily
  • Ignoring indexes
  • Using incorrect join methods
  • Underestimating cardinality

Good statistics are critical for:

  • Performance tuning
  • SQL optimization
  • Parallel query execution
  • Data warehouse workloads

Statistics in Autonomous Database

Oracle Autonomous Database automatically gathers optimizer statistics in most situations.

However, manual gathering is still useful when:

  • Large data loads occur
  • Bulk inserts happen
  • Partition maintenance is performed
  • Testing optimizer behavior
  • Benchmarking workloads

Why GRANULARITY is Important for Partitioned Tables

Partitioned tables can have:

  • Millions or billions of rows
  • Different data distributions per partition

Global statistics alone may not be sufficient.

Using:

granularity=>'ALL'

allows Oracle to maintain:

  • Detailed partition-level statistics
  • Accurate pruning estimates
  • Better optimizer decisions

Best Practices

  • Gather statistics after large data loads
  • Use parallel DEGREE carefully
  • Use GRANULARITY='ALL' for partitioned tables
  • Monitor STALE_STATS regularly
  • Avoid unnecessary repeated statistics gathering

Key Takeaways

  • DBMS_STATS.GATHER_TABLE_STATS collects optimizer statistics
  • GRANULARITY controls which levels of statistics are gathered
  • ALL gathers global, partition, and subpartition statistics
  • DEGREE enables parallel statistics gathering
  • DBA_TAB_STATISTICS stores optimizer metadata
  • Fresh statistics improve query performance significantly

Conclusion

Optimizer statistics are the foundation of Oracle SQL performance tuning.

Understanding parameters like:

degree=>4
granularity=>'ALL'

helps DBAs and developers control how Oracle analyzes tables and generates execution plans.

Even in Autonomous Database, understanding statistics remains extremely important for:

  • Performance optimization
  • Partition tuning
  • Large-scale analytics
  • SQL troubleshooting

The output shown above demonstrates how Oracle successfully gathered accurate statistics for:

  • 93,743 rows
  • 108 blocks
  • Fresh optimizer metadata

allowing the Cost-Based Optimizer to make smarter execution decisions.

Can We Gather Dictionary Statistics in Oracle Autonomous Database?

One of the most common Oracle DBA maintenance tasks in traditional databases is gathering optimizer statistics.

DBAs often run:

DBMS_STATS.GATHER_DICTIONARY_STATS

to collect statistics for Oracle data dictionary objects.

But what happens if we try the same operation inside Oracle Autonomous Database?

In this blog, we will explore:

  • What dictionary statistics are
  • Why they are important
  • What happens in Autonomous Database
  • Why ORA-20000 occurs
  • Understanding ORA-06512 stack traces
  • How Autonomous Database handles statistics internally
  • Important architectural differences from traditional Oracle databases

Attempting to Gather Dictionary Statistics

Let us execute the following PL/SQL block:

SQL> begin
  2      dbms_stats.gather_dictionary_stats;
  3  end;
  4  /

The Error Output

begin
*
ERROR at line 1:

ORA-20000: Insufficient privileges to analyze
"AUDSYS"."AUD$UNIFIED"."SYS_P16148"(TABLE) in Dictionary Schema

ORA-06512: at "SYS.DBMS_STATS", line 51408
ORA-06512: at "SYS.DBMS_STATS", line 51363
ORA-06512: at "SYS.DBMS_STATS", line 46595
ORA-06512: at "SYS.DBMS_STATS", line 45745
ORA-06512: at "SYS.DBMS_STATS", line 46470
ORA-06512: at "SYS.DBMS_STATS", line 51295
ORA-06512: at "SYS.DBMS_STATS", line 51398
ORA-06512: at line 2

Understanding ORA-20000

According to Oracle documentation, ORA-20000 is a generic user-defined error raised by PL/SQL packages when an operation cannot proceed successfully.

Official Oracle documentation:

https://docs.oracle.com/error-help/db/ora-20000/

In this specific case, the message clearly states:

Insufficient privileges to analyze
"AUDSYS"."AUD$UNIFIED"

This means the current user does not have sufficient permissions to gather statistics on Oracle internal dictionary objects.


Understanding ORA-06512

The ORA-06512 messages are PL/SQL stack trace entries.

Official Oracle documentation:

https://docs.oracle.com/error-help/db/ora-06512/

ORA-06512 itself is not the root error.

Instead, it shows:

  • Which PL/SQL package failed
  • The internal line number
  • The execution path of the error

Example:

ORA-06512: at "SYS.DBMS_STATS", line 51408

This tells us the failure happened internally inside the:

SYS.DBMS_STATS

package implementation.


What is GATHER_DICTIONARY_STATS?

The procedure:

DBMS_STATS.GATHER_DICTIONARY_STATS

collects optimizer statistics for:

  • SYS objects
  • Data dictionary tables
  • Internal Oracle metadata
  • System schemas

These statistics help the Oracle optimizer generate efficient execution plans for internal SQL operations.


Why DBAs Use Dictionary Statistics

In traditional Oracle databases, DBAs may gather dictionary stats:

  • After upgrades
  • After patching
  • After catalog changes
  • During performance tuning
  • After large metadata changes

Example in traditional databases:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

This operation is usually performed by:

  • SYS
  • SYSDBA

Why Does It Fail in Autonomous Database?

Oracle Autonomous Database is a fully managed platform.

Oracle intentionally restricts access to:

  • Internal dictionary schemas
  • Oracle-managed objects
  • System statistics maintenance
  • Low-level database administration

Even users with:

DBA role

do not receive complete SYSDBA-level privileges.


The AUDSYS Schema Explained

The error references:

AUDSYS.AUD$UNIFIED

This belongs to Oracle Unified Auditing infrastructure.

Oracle protects these internal audit tables because they are critical for:

  • Security auditing
  • Compliance
  • Internal monitoring
  • Cloud governance

Allowing unrestricted analysis or modification could impact:

  • Performance
  • Security
  • Cloud automation
  • Audit integrity

Important Autonomous Database Concept

In Autonomous Database:

Oracle itself manages optimizer statistics automatically.

This includes:

  • Dictionary statistics
  • System statistics
  • Fixed object statistics
  • Optimizer maintenance
  • Automatic statistics gathering jobs

Customers are intentionally prevented from modifying many internal optimizer components.


Traditional Oracle vs Autonomous Database

Feature Traditional Oracle DB Autonomous Database
SYSDBA Access Available Restricted
Gather Dictionary Stats Allowed Restricted
Analyze SYS Objects Possible Blocked
Optimizer Maintenance DBA Managed Oracle Managed
Internal Audit Tables Access SYS Only Protected by Oracle

Can We Gather User Schema Statistics?

Yes.

Autonomous Database allows gathering statistics for user-owned schemas and tables.

Example:

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS('TEST_USER');
END;
/

or:

EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST_USER','EMP');

These operations are fully supported for customer schemas.


What Statistics Does Autonomous Database Manage Automatically?

Oracle Autonomous Database automatically handles:

  • Optimizer statistics collection
  • Statistics refresh
  • Histogram management
  • SQL plan optimization
  • Adaptive optimizer tuning
  • Automatic indexing
  • Dictionary statistics

This is one of the major benefits of Autonomous Database.


Why Oracle Restricts Internal Statistics Operations

Oracle Autonomous Database is built around:

  • Automation
  • Self-healing infrastructure
  • Managed operations
  • Security isolation
  • Cloud-scale consistency

Allowing customers to directly manipulate internal dictionary statistics could:

  • Destabilize optimizer behavior
  • Break cloud automation
  • Impact multi-tenant infrastructure
  • Create unpredictable execution plans

Understanding the Full Error Stack

The sequence:

ORA-20000
ORA-06512
ORA-06512
ORA-06512

should be interpreted as:

  • ORA-20000 = Root failure reason
  • ORA-06512 = PL/SQL execution trace information

This is extremely common in Oracle PL/SQL package errors.


Best Practices in Autonomous Database

  • Allow Oracle to manage dictionary statistics automatically
  • Avoid attempting SYS-level maintenance operations
  • Gather statistics only for user schemas when needed
  • Use automatic optimizer management whenever possible
  • Focus on application-level tuning instead of internal database maintenance

What Makes Autonomous Database Different?

Traditional DBAs are used to:

  • Managing optimizer statistics manually
  • Running maintenance scripts
  • Gathering fixed object stats
  • Controlling optimizer behavior

Autonomous Database changes this model completely.

Oracle now automates:

  • Statistics gathering
  • Performance tuning
  • Index optimization
  • Storage management
  • Patch management
  • Security operations

Key Takeaways

  • DBMS_STATS.GATHER_DICTIONARY_STATS fails in Autonomous Database
  • ORA-20000 indicates insufficient privileges on internal dictionary objects
  • ORA-06512 provides PL/SQL stack trace information
  • AUDSYS objects are protected by Oracle
  • Oracle automatically manages dictionary statistics internally
  • User schema statistics gathering is still supported
  • Autonomous Database restricts many SYS-level DBA operations

Conclusion

Oracle Autonomous Database fundamentally changes the DBA operating model.

Operations that were traditionally performed manually — such as gathering dictionary statistics — are now handled automatically by Oracle Cloud infrastructure.

The ORA-20000 error shown while executing:

DBMS_STATS.GATHER_DICTIONARY_STATS

is expected behavior in Autonomous Database and demonstrates Oracle’s managed-cloud architecture.

Instead of manually maintaining internal optimizer statistics, DBAs can now focus more on:

  • Application design
  • SQL tuning
  • Data modeling
  • Analytics
  • Business workloads

while Oracle handles the complex internal database maintenance automatically behind the scenes.

Free Oracle Cloud OCI VM Instance — Exploring the Resources

One of the best things about Oracle Cloud Infrastructure (OCI) is its extremely generous Always Free Tier.

For anyone learning Linux, cloud computing, Oracle databases, DevOps, Kubernetes, or infrastructure automation, OCI Free Tier is an excellent platform to start with.

Recently, I created my very first OCI virtual machine instance and explored its system resources directly from the Linux terminal.

In this blog, I will share:

  • Storage details
  • CPU information
  • Memory allocation
  • Swap configuration
  • Filesystem layout
  • What these resources mean for beginners

The OCI Free Tier VM

The instance was created successfully and came with:

  • Oracle Linux
  • AMD EPYC processor
  • Persistent block storage
  • Swap memory
  • XFS filesystem

After logging in as root, I started inspecting the server resources.


Checking Disk Space

The first command I executed was:

[root@instance-vm1 ~]# df -h

Output:

Filesystem                  Size  Used Avail Use% Mounted on
devtmpfs                    4.0M     0  4.0M   0% /dev
tmpfs                       250M     0  250M   0% /dev/shm
tmpfs                       100M  9.8M   90M  10% /run
efivarfs                    256K   17K  235K   7% /sys/firmware/efi/efivars
/dev/mapper/ocivolume-root   30G  5.6G   24G  19% /
/dev/sda2                   2.0G  451M  1.5G  23% /boot
/dev/mapper/ocivolume-oled   15G  185M   15G   2% /var/oled
/dev/sda1                   100M  7.5M   93M   8% /boot/efi

Understanding the Storage Layout

1. Root Filesystem

The primary root filesystem:

/dev/mapper/ocivolume-root

has:

  • Total Size: 30 GB
  • Used Space: 5.6 GB
  • Free Space: 24 GB

This is where the operating system, applications, packages, and user data are stored.

For a free-tier instance, 30 GB is actually very good for:

  • Linux learning
  • Docker containers
  • Small databases
  • Web servers
  • Development environments
  • Terraform labs

2. Separate OLED Volume

An interesting mount point exists:

/var/oled

mounted from:

/dev/mapper/ocivolume-oled

This dedicated 15 GB volume is related to Oracle Linux system components and telemetry services.

It currently uses only:

185 MB

which means almost the entire space is free.


3. Boot Partition

The system also includes:

/boot

with:

  • 2 GB size
  • 451 MB used

This partition stores:

  • Linux kernel
  • initramfs
  • bootloader files

4. EFI Partition

The instance uses UEFI boot mode:

/boot/efi

This confirms the VM uses modern EFI firmware instead of legacy BIOS.


Checking Memory Resources

Next, I checked RAM usage:

[root@instance-vm1 ~]# free -m

Output:

               total        used        free      shared  buff/cache   available
Mem:             498         205         174           0         140         293
Swap:            497         145         352

Understanding the Memory

The instance contains:

  • ~500 MB RAM
  • ~500 MB swap memory

Although this is a lightweight VM, it is still sufficient for many tasks:

  • Linux practice
  • Shell scripting
  • Web hosting
  • NGINX/Apache
  • Python applications
  • OCI CLI usage
  • Terraform labs
  • Small Oracle client tools

Why Swap Memory Matters

The system also includes swap:

Swap: 497 MB

Swap acts as virtual memory when RAM becomes full.

This helps prevent crashes during memory spikes.

OCI automatically configured swap using:

/.swapfile

as shown in:

/etc/fstab

Checking CPU Information

Now comes the most exciting part — the processor.

I executed:

[root@instance-vm1 ~]# cat /proc/cpuinfo

CPU Details

The VM is powered by:

AMD EPYC 7551 32-Core Processor

This is an enterprise-grade server processor commonly used in cloud data centers.

Important details:

  • Processor Vendor: AMD
  • Architecture: x86_64
  • CPU Speed: ~2 GHz
  • Virtualized Environment
  • AVX2 support
  • AES encryption support

Interesting Observation About CPUs

The output shows:

processor : 0
processor : 1

which means the VM exposes:

  • 2 virtual CPUs (vCPUs)

This is impressive for a free cloud VM.

Many free-tier offerings from other cloud providers provide far fewer resources.


Filesystem Type — XFS

The root filesystem uses:

xfs

XFS is a high-performance enterprise filesystem commonly used in:

  • Oracle Linux
  • Red Hat Enterprise Linux
  • Large storage systems
  • Cloud environments

It offers:

  • Excellent scalability
  • Fast metadata operations
  • High reliability
  • Good performance for databases

Exploring /etc/fstab

The file:

/etc/fstab

contains persistent mount configuration.

Interesting entries include:

/dev/mapper/ocivolume-root
/dev/mapper/ocivolume-oled
/.swapfile

OCI also provides important warnings:

SCSI device names are not stable across reboots

This is why Oracle recommends using:

  • UUIDs
  • Persistent mappings

instead of raw device names.


What Can You Learn Using This Free OCI VM?

Even this small free-tier instance is powerful enough for learning:

  • Linux administration
  • Bash scripting
  • Python automation
  • Docker basics
  • Terraform
  • Ansible
  • OCI CLI
  • Networking
  • Web hosting
  • SSH security
  • Git and DevOps workflows

Why OCI Free Tier is Amazing for Beginners

OCI Free Tier stands out because it offers:

  • Always Free compute instances
  • Block storage
  • Public IP addresses
  • ARM and AMD shapes
  • Cloud networking
  • Autonomous Database free instances

For students, developers, and DBAs, this becomes an excellent free cloud lab environment.


My First Impression

I was honestly surprised by:

  • The enterprise-grade AMD EPYC CPU
  • Persistent storage
  • Dedicated swap space
  • XFS filesystem
  • Clean Oracle Linux setup

Even though the VM has limited RAM, it is more than enough for learning and lightweight workloads.


Key Resource Summary

Resource Value
CPU 2 vCPUs (AMD EPYC 7551)
RAM ~500 MB
Swap ~500 MB
Root Storage 30 GB
Additional Volume 15 GB (/var/oled)
Filesystem XFS
Boot Mode UEFI
Operating System Oracle Linux

Conclusion

Creating my first OCI free-tier VM was an exciting experience.

It demonstrated how Oracle Cloud provides real enterprise-grade infrastructure even in its free offerings.

From AMD EPYC processors to XFS storage and persistent block volumes, the environment feels very similar to production cloud infrastructure.

For anyone wanting to learn:

  • Cloud computing
  • Linux
  • Oracle technologies
  • DevOps
  • Infrastructure automation

OCI Free Tier is one of the best free platforms available today.

And this was just the beginning of the journey.

Can We Change the Default Tablespace for a User in Oracle Autonomous Database?

  • Storage segregation
  • Performance management
  • Application isolation
  • Quota control

However, things work differently in Oracle Autonomous Database.

A very interesting behavior appears when trying to change the default tablespace for a user.

In this article, we will explore:

  • How default tablespaces work in Autonomous Database
  • What happens when ALTER USER is executed
  • Why Oracle silently keeps DATA tablespace
  • How Autonomous Database internally manages storage
  • Important limitations of Autonomous Database

Checking the Current Default Tablespace

Let us first check the current default tablespace of a user:

SQL> select DEFAULT_TABLESPACE
     from dba_users
     where username='TEST_USER';

Output:

DEFAULT_TABLESPACE
---------------------
DATA

This confirms that the user TEST_USER uses the:

DATA tablespace

by default.


Available Tablespaces in Autonomous Database

Now let us inspect the available tablespaces:

SQL> select name from v$tablespace;

Output:

NAME
---------------
SYSTEM
SYSAUX
UNDO_21871
DATA
DBFS_DATA
TEMP
SAMPLESCHEMA
UNDO_4F8D9

We can clearly see another tablespace called:

SAMPLESCHEMA

So naturally, the next step is trying to assign it as the default tablespace.


Attempting to Change the Default Tablespace

Now let us execute:

SQL> alter user TEST_USER
     default tablespace SAMPLESCHEMA;

User TEST_USER altered.

Interesting — Oracle reports:

User TEST_USER altered.

At first glance, it appears the operation succeeded successfully.


Verifying the Change

Now let us verify the result:

SQL> select DEFAULT_TABLESPACE
     from dba_users
     where username='TEST_USER';

Output:

DEFAULT_TABLESPACE
---------------------
DATA

Surprising Result: The Tablespace Did NOT Change

Even though Oracle displayed:

User TEST_USER altered.

the default tablespace still remains:

DATA

This reveals an important behavior of Oracle Autonomous Database:

Oracle Autonomous Database internally enforces DATA as the default user tablespace.

Why Does This Happen?

Oracle Autonomous Database is a fully managed cloud platform.

Oracle intentionally abstracts storage administration from users and DBAs.

Internally, Oracle wants:

  • Simplified storage management
  • Automated scaling
  • Consistent storage architecture
  • Automatic optimization
  • Cloud-native automation

Allowing unrestricted tablespace management would complicate:

  • Auto scaling
  • Performance tuning
  • Storage balancing
  • Compression management
  • Internal automation

Important Observation

The most interesting part is:

  • The ALTER USER command does not throw an error
  • The command reports success
  • But the default tablespace remains unchanged

This behavior demonstrates that Autonomous Database may internally override or ignore unsupported storage-level administrative changes.


Understanding the DATA Tablespace

In Autonomous Database, the:

DATA

tablespace is the primary managed tablespace used for:

  • User tables
  • Indexes
  • LOB storage
  • Application schemas
  • Partitions

Oracle automatically optimizes this tablespace behind the scenes.


Can SAMPLESCHEMA Be Used?

Although:

SAMPLESCHEMA

appears in:

v$tablespace

it may be:

  • Oracle-managed internally
  • Reserved for sample/demo schemas
  • Protected from user assignment
  • Not intended for customer workloads

Autonomous Database exposes several internal tablespaces that are not fully user-manageable.


Traditional Oracle vs Autonomous Database

Feature Traditional Oracle DB Autonomous Database
Create Tablespaces Allowed Restricted
Change Default Tablespace Fully Supported Internally Controlled
Manage Datafiles DBA Managed Oracle Managed
ASM Access Available Hidden
User Storage Layout Flexible Standardized

What Happens When TEST_USER Creates Objects?

Suppose the user creates a table:

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
);

The table will still be stored inside:

DATA tablespace

because Oracle continues enforcing DATA as the effective default tablespace.


How Oracle Autonomous Database Thinks Differently

Traditional DBAs often think in terms of:

  • Separate tablespaces per application
  • Storage tiering
  • Manual datafile allocation
  • Dedicated index tablespaces

Autonomous Database shifts the focus toward:

  • Automation
  • Self-management
  • Elastic scaling
  • Simplified operations
  • Cloud-native administration

Best Practices in Autonomous Database

  • Use the default DATA tablespace for user objects
  • Avoid relying on manual tablespace architecture
  • Allow Oracle to manage storage automatically
  • Focus on schema and workload optimization
  • Use OCI Console for storage scaling instead of DBA-managed datafiles

Key Takeaways

  • New users in Autonomous Database use the DATA tablespace
  • ALTER USER DEFAULT TABLESPACE may appear successful
  • But Autonomous Database internally keeps DATA as the effective tablespace
  • Storage management is heavily controlled by Oracle
  • Autonomous Database prioritizes automation over manual storage customization

Conclusion

Oracle Autonomous Database fundamentally changes how storage administration works.

Even though SQL commands like:

ALTER USER ... DEFAULT TABLESPACE

may execute successfully, Oracle Autonomous Database internally enforces its managed storage architecture.

The experiment clearly demonstrates that:

Oracle Autonomous Database prefers all user workloads to remain inside the managed DATA tablespace.

This design helps Oracle deliver:

  • Automatic scaling
  • Self-healing infrastructure
  • Performance optimization
  • Simplified cloud operations

For DBAs transitioning from traditional Oracle environments, this is one of the most important architectural shifts to understand in Autonomous Database.

Which Tablespace Does a New User Use in Oracle Autonomous Database?

When working with Oracle Autonomous Database, many DBAs and developers wonder:

“If I create a new user, which tablespace will Oracle use automatically?”

Unlike traditional Oracle databases where DBAs manually create and assign tablespaces, Oracle Autonomous Database simplifies storage management significantly.

In this article, we will explore:

  • How user creation works in Autonomous Database
  • What default tablespace is assigned
  • How Oracle automatically manages storage
  • Where user objects are stored
  • How to verify the default tablespace

Creating a New User

Let us create a new database user:

SQL> create user test_user identified by testoracle12345U#;

User TEST_USER created.

The user was successfully created.


Granting Privileges

Now let us grant privileges to the user:

SQL> grant connect, dba to test_user;

Grant succeeded.

The user now has CONNECT and DBA privileges.


Checking DBA_USERS Metadata

Oracle stores user-related information inside the DBA_USERS data dictionary view.

Let us inspect its structure:

SQL> desc dba_users

Relevant columns include:

USERNAME
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
ACCOUNT_STATUS
CREATED
PROFILE

The most important column for this discussion is:

DEFAULT_TABLESPACE

Finding the Default Tablespace

Now let us check which tablespace Oracle assigned to the new user:

SQL> select DEFAULT_TABLESPACE
     from dba_users
     where username='TEST_USER';

Output:

DEFAULT_TABLESPACE
---------------------
DATA

Answer: New Users Use the DATA Tablespace

In Oracle Autonomous Database, newly created users automatically use:

DATA tablespace

This means all objects created by the user will be stored in the DATA tablespace unless explicitly specified otherwise.

Examples include:

  • Tables
  • Indexes
  • LOBs
  • Partitions
  • Materialized views

Why Does Autonomous Database Use DATA Tablespace?

Oracle Autonomous Database is designed as a fully managed cloud service.

Oracle simplifies storage management by automatically directing user objects into a managed application tablespace called:

DATA

This removes the need for:

  • Manual tablespace planning
  • Storage allocation management
  • Datafile administration
  • ASM configuration
  • Storage balancing

Traditional Oracle vs Autonomous Database

Feature Traditional Oracle DB Autonomous Database
Create Custom Tablespaces Common Restricted
Assign User Tablespaces Manual Automatic
Manage Datafiles DBA Managed Oracle Managed
ASM Administration Required Hidden
Default User Storage User-defined DATA tablespace

What Happens When TEST_USER Creates a Table?

Suppose the user creates a table:

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
);

Oracle automatically stores this table inside:

DATA tablespace

because it is the user's default tablespace.


How to Verify Where Objects Are Stored

You can verify the tablespace of user objects using:

SELECT owner,
       segment_name,
       tablespace_name
FROM dba_segments
WHERE owner='TEST_USER';

Expected output:

OWNER       SEGMENT_NAME    TABLESPACE_NAME
----------- --------------- ----------------
TEST_USER   EMPLOYEES       DATA

Temporary Tablespace

Along with a default permanent tablespace, Oracle also assigns a temporary tablespace.

You can check it using:

SELECT username,
       default_tablespace,
       temporary_tablespace
FROM dba_users
WHERE username='TEST_USER';

Typically, Autonomous Database uses:

TEMP

for sorting and temporary operations.


Can We Change the Default Tablespace?

In traditional Oracle databases, DBAs often use:

ALTER USER test_user
DEFAULT TABLESPACE users;

However, in Autonomous Database, options are limited because Oracle manages storage internally.

Since manual tablespace creation is restricted, most users continue using the default DATA tablespace.


Benefits of This Approach

1. Simplified Administration

Developers do not need to worry about storage allocation.

2. Better Automation

Oracle automatically handles:

  • Storage growth
  • Autoextend
  • Compression
  • Performance optimization

3. Reduced DBA Overhead

No need to manually manage dozens of application tablespaces.

4. Cloud-Native Design

The architecture aligns with fully managed cloud database principles.


Important Note About DBA Role in Autonomous Database

Even though:

grant connect, dba to test_user;

was successful, Autonomous Database still restricts several administrative operations.

For example:

  • CREATE TABLESPACE may fail
  • ASM access is blocked
  • OS-level access is unavailable
  • Datafile management is restricted

This is expected behavior in Autonomous Database.


Best Practices

  • Use the default DATA tablespace for application objects
  • Avoid attempting ASM operations
  • Monitor tablespace usage periodically
  • Let Oracle manage storage automatically
  • Focus on schema and application design instead of storage administration

Key Takeaways

  • New users in Autonomous Database automatically use the DATA tablespace
  • Oracle manages storage internally
  • Manual tablespace creation is restricted
  • User objects are automatically stored inside DATA
  • The DBA role in Autonomous Database is still partially restricted

Conclusion

Oracle Autonomous Database changes the traditional DBA experience by abstracting low-level storage management.

When a new user is created, Oracle automatically assigns the DATA tablespace as the default storage location for user objects.

This cloud-native approach simplifies administration, reduces operational complexity, and allows developers and DBAs to focus more on application workloads rather than storage infrastructure.

So the next time you create a user in Autonomous Database, remember:

Oracle automatically handles the tablespace management for you.