Oracle Autonomous Database comes with a highly optimized and secure architecture managed automatically by Oracle. While most administration tasks are automated, DBAs and developers can still explore several internal database views to better understand the environment.
In this blog, we will explore:
- Default users available in Autonomous Database
- Locked vs Open accounts
- Available tablespaces
- SGA and PGA memory configuration
- What these components mean internally
Checking Database Users
One of the first things many Oracle professionals explore is the DBA_USERS view.
SELECT username, account_status FROM dba_users;
Output
USERNAME ACCOUNT_STATUS _________________________ _________________ APEX_PUBLIC_ROUTER OPEN ORDS_PLSQL_GATEWAY OPEN ORDS_PUBLIC_USER OPEN ODI_REPO_USER OPEN C##DATA$SHARE OPEN SYSRAC OPEN SCOTT OPEN ADMIN OPEN ODI$PROXY OPEN GRAPH$PROXY_USER OPEN OML$PROXY OPEN RMAN$CATALOG OPEN SYSTEM LOCKED XS$NULL LOCKED SYS LOCKED LBACSYS LOCKED OUTLN LOCKED C##CLOUD$SERVICE LOCKED DBSNMP LOCKED APPQOSSYS LOCKED VECSYS LOCKED ... 56 rows selected.
Understanding OPEN Accounts
Several accounts are marked as OPEN because they are actively used by Autonomous Database services and internal components.
Important OPEN Users
- ADMIN → Primary administrative user for Autonomous Database
- SCOTT → Sample schema provided for learning and testing
- ORDS_PUBLIC_USER → Used by Oracle REST Data Services (ORDS)
- APEX_PUBLIC_ROUTER → Supports Oracle APEX routing
- GRAPH$PROXY_USER → Used for graph analytics functionality
- OML$PROXY → Oracle Machine Learning integration
- RMAN$CATALOG → Recovery Manager metadata operations
These users support Autonomous Database features such as:
- Oracle APEX
- REST APIs
- Machine Learning
- Graph processing
- Data sharing
- Backup automation
Why SYS and SYSTEM are LOCKED
One interesting observation in Autonomous Database is:
SYSis LOCKEDSYSTEMis LOCKED
This is intentional and part of Oracle Autonomous Database security architecture.
In Autonomous Database:
- Oracle manages the infrastructure
- Direct SYS access is restricted
- Patch management is automated
- Security hardening is enforced automatically
- Users operate primarily using the ADMIN account
This reduces operational risk and prevents accidental modifications to critical internal components.
Interesting Internal Users
Oracle APEX Related
- APEX_240200
- FLOWS_FILES
- APEX_PUBLIC_ROUTER
These support Oracle APEX runtime and metadata management.
Security Related Users
- DVSYS
- DVF
- LBACSYS
- AUDSYS
These accounts are related to:
- Database Vault
- Label Security
- Auditing
- Compliance controls
GoldenGate Related Users
- GGSYS
- GGADMIN
- GGSHAREDCAP
These users support Oracle GoldenGate replication services.
Machine Learning and AI
- OML$PROXY
- PYQSYS
- VECSYS
These accounts are associated with:
- Oracle Machine Learning
- Python execution
- AI Vector Search capabilities
Exploring Tablespaces
Next, let us check available tablespaces.
SELECT name FROM v$tablespace;
Output
NAME _______________ SYSTEM SYSAUX UNDO_21871 DATA DBFS_DATA TEMP SAMPLESCHEMA UNDO_4F8D9
Understanding Tablespaces
| Tablespace | Purpose |
|---|---|
| SYSTEM | Core Oracle data dictionary |
| SYSAUX | Auxiliary database components |
| TEMP | Temporary operations and sorting |
| DATA | Primary user data storage |
| DBFS_DATA | Database File System storage |
| SAMPLESCHEMA | Sample schemas and demo objects |
| UNDO_* | Undo management for transactions |
Checking SGA Memory
The System Global Area (SGA) represents shared memory structures used by Oracle Database.
SHOW PARAMETER sga
Output
NAME TYPE VALUE ------------ ----------- ------- sga_max_size big integer 219008M sga_min_size big integer 0 sga_target big integer 3400M
Understanding SGA Parameters
- sga_target → Current target memory allocation
- sga_max_size → Maximum possible SGA allocation
- sga_min_size → Minimum guaranteed SGA
Oracle Autonomous Database dynamically manages memory allocation depending on workload and scaling operations.
Checking PGA Memory
The Program Global Area (PGA) contains process-specific memory used for sorting, hashing, and query execution.
SHOW PARAMETER pga
Output
NAME TYPE VALUE -------------------- ----------- ------ pga_aggregate_limit big integer 10200M pga_aggregate_target big integer 5100M
Understanding PGA Parameters
- pga_aggregate_target → Target PGA memory allocation
- pga_aggregate_limit → Maximum PGA memory allowed
PGA is crucial for:
- Sorting operations
- Hash joins
- Parallel execution
- SQL work areas
Autonomous Database Memory Management
One key advantage of Autonomous Database is automatic memory tuning.
Oracle automatically:
- Tunes SGA and PGA
- Optimizes workloads
- Balances memory usage
- Improves SQL execution performance
- Handles scaling internally
This reduces DBA overhead significantly compared to traditional on-premises databases.
Final Thoughts
Exploring internal views inside Oracle Autonomous Database provides valuable insight into how Oracle manages security, memory, services, and infrastructure automatically.
Even though Autonomous Database abstracts much of the administration layer, understanding these components helps DBAs and developers better optimize workloads and understand Oracle's cloud-native architecture.
Features such as:
- Locked SYS/SYSTEM accounts
- Automatic memory management
- Pre-configured internal schemas
- Integrated AI and Machine Learning users
- Managed tablespaces
show how Oracle Autonomous Database is designed for automation, security, and enterprise-grade scalability.
Conclusion
Oracle Autonomous Database is far more than just a managed database service. It includes a sophisticated ecosystem of internal schemas, automated services, and intelligent memory management working together behind the scenes.
Understanding these internals can help Oracle professionals better appreciate the architecture powering Autonomous Database in Oracle Cloud Infrastructure (OCI).
No comments:
Post a Comment