The blog post shows screenshots of how to set up an Oracle Autonomous Data Warehouse (ADW) service in the Oracle Cloud. The blog post contains three parts:

  • Create the ADW service
  • Client Credentials
  • Client configuration (SQL Developer, sqlplus)
  • Parameter list


Create the ADW service

Log into your account dashboard and select services.





Scroll down and select to Autonomous Data Warehouse.


















Now check if the correct compartment on the left is selected. A compartment is a logical container used to organize and isolate cloud resources. Compartments are global and logical while physical containers belong to regions and availability domains.

Then proceed and click the blue button to create a new instance. Now configure the details of the service.

The upper part contains the details about naming the service and sizing it. Default values for CPU Core count and storage are currently one core and 1 TB.












The lower part of the window contains the password for the admin user. The admin user is required for tThe initial connection. Ensure to select the appropriate license and click on “Create Autonomous Data Warehouse.”
















The service gets provisioned now is available after a few minutes. The ADW page looks like the following screenshot after one day. ADW creates backups automatically.










Client Credentials

The client credentials are needed to connect to the service by using tools like sqlplus. Click on “Service Console.” A new window will come up.







The admin credentials defined above need to be entered now.







The next two images show the Overview page and the Activity page.



















The Administration page lets you download client credentials to connect with tools like sqlplus or SQL Developer.






Click to download the client credentials. A window will come up for the admin password.









If you click on download, you will get a zip file containing the client credentials. The content of the zip file






Client configuration (SQL Developer, sqlplus)

Finally, start SQL Developer and configure the connection to the ADW instance. SQL Developer uses the zip file. There is no unzipping necessary. Connection type is Cloud-PDB.











Now you are ready to connect.







Alternatively, a connection with sqlplus requires the following steps:

  • Unzip the client credentials file and edit sqlnet.ora
    Adjust the directory to the folder into which you unzipped the client credentials
    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=”?/network/admin”)))
  • set TNS_ADMIN to the path containing the unzipped client credentials
    export TNS_ADMIN=…
  • and start sqlplus admin@adw02_high (replace with a connect string in your tnsnames.ora-file)






Swingbench is available for ADW and can be used to make some load tests. A description of the steps to run a load test is available on Dominic Giles blog “Setting up Swingbench for Oracle Autonomous Data Warehousing (ADW).”


The blog Managing Autonomous Data Warehouse Using oci-curl describes how to create and manage an Autonomous Data Warehouse (ADW) instance using REST APIs.

Parameter list

Default parameter values of the created service:

name display_value default_value isdefault
active_instance_count 4294967295 TRUE
allow_global_dblinks FALSE FALSE TRUE
allow_group_access_to_sga FALSE FALSE TRUE
approx_for_aggregation FALSE FALSE TRUE
approx_for_count_distinct FALSE FALSE TRUE
approx_for_percentile none none TRUE
aq_tm_processes 40 1 FALSE
archive_lag_target 0 0 TRUE
asm_diskgroups NULL TRUE
asm_diskstring NULL TRUE
asm_io_processes 20 20 TRUE
asm_power_limit 1 1 TRUE
asm_preferred_read_failure_groups NULL TRUE
audit_file_dest /u02/app/oracle/admin/eig1pod/adump $ORACLE_BASE/admin/@/adump FALSE
audit_syslog_level NULL TRUE
audit_sys_operations TRUE TRUE TRUE
audit_trail DB none FALSE
_auto_start_pdb_services TRUE FALSE FALSE
autotask_max_active_pdbs 10 2 FALSE
awr_pdb_autoflush_enabled TRUE FALSE FALSE
awr_snapshot_time_offset 0 0 TRUE
background_core_dump partial part TRUE
background_dump_dest /u02/app/oracle/product/
$ORACLE_BASE/admin/@/bdump TRUE
backup_tape_io_slaves FALSE FALSE TRUE
bitmap_merge_area_size 1048576 1048576 TRUE
blank_trimming FALSE FALSE TRUE
buffer_pool_keep NONE TRUE
buffer_pool_recycle NONE TRUE
cdb_cluster FALSE FALSE TRUE
cdb_cluster_name NONE TRUE
_cdb_port 5950 0 FALSE
cell_offload_compaction ADAPTIVE ADAPTIVE TRUE
cell_offload_decryption TRUE TRUE TRUE
cell_offloadgroup_name NULL TRUE
cell_offload_parameters NONE TRUE
cell_offload_plan_display AUTO AUTO TRUE
cell_offload_processing TRUE TRUE TRUE
_cell_offload_vector_groupby FALSE TRUE FALSE
circuits 4294967295 TRUE
client_result_cache_lag 3000 3000 TRUE
client_result_cache_size 0 0 TRUE
clonedb_dir NULL TRUE
_cloud_service_type DWCS NULL FALSE
cluster_database TRUE FALSE FALSE
cluster_database_instances 8 4294967295 TRUE
cluster_interconnects NONE TRUE
commit_logging NULL TRUE
commit_point_strength 1 1 TRUE
commit_wait NULL TRUE
commit_write NULL TRUE
common_user_prefix NONE TRUE
compatible 12.02.2000 FALSE
connection_brokers ((TYPE=DEDICATED)(BROKERS=1)),
containers_parallel_degree 65535 65535 TRUE
control_file_record_keep_time 7 7 TRUE
control_files #NAME? ?=/dbs/cntrl@.dbf FALSE
core_dump_dest /u02/app/oracle/diag/rdbms/eig1pod/
$ORACLE_BASE/admin/@/cdump TRUE
cpu_count 6 0 TRUE
create_bitmap_area_size 8388608 8388608 TRUE
create_stored_outlines NULL TRUE
cursor_bind_capture_destination memory+disk memory+disk TRUE
cursor_invalidation IMMEDIATE IMMEDIATE TRUE
cursor_sharing EXACT EXACT TRUE
cursor_space_for_time FALSE FALSE TRUE
data_guard_sync_latency 0 0 TRUE
_datapump_gather_stats_on_load TRUE FALSE FALSE
_datapump_inherit_svcname TRUE FALSE FALSE
data_transfer_cache_size 0 0 TRUE
db_big_table_cache_percent_target 0 0 TRUE
db_block_buffers 0 0 TRUE
db_block_checking MEDIUM FALSE FALSE
db_block_checksum TYPICAL TYPICAL TRUE
db_block_size 8192 8192 FALSE
db_cache_advice ON OFF TRUE
db_cache_size 0 0 TRUE
db_create_file_dest #NAME? NONE FALSE
db_create_online_log_dest_1 #NAME? NONE FALSE
db_create_online_log_dest_2 NONE TRUE
db_create_online_log_dest_3 NONE TRUE
db_create_online_log_dest_4 NONE TRUE
db_create_online_log_dest_5 NONE TRUE
db_domain NULL TRUE
db_file_multiblock_read_count 128 0 TRUE
db_file_name_convert NULL TRUE
db_files 25 200 FALSE
db_flashback_retention_target 480 1440 FALSE
db_flash_cache_file NONE TRUE
db_flash_cache_size 0 0 TRUE
_db_full_caching FALSE TRUE FALSE
db_index_compression_inheritance NONE NONE TRUE
db_keep_cache_size 0 0 TRUE
db_lost_write_protect TYPICAL NONE TRUE
db_name eig1pod NONE FALSE
db_performance_profile NULL TRUE
db_recovery_file_dest #NAME? NONE FALSE
db_recovery_file_dest_size 44733530M 0 FALSE
db_recycle_cache_size 0 0 TRUE
db_securefile ALWAYS P FALSE
db_ultra_safe OFF OFF TRUE
db_unique_name eig1pod NONE TRUE
db_unrecoverable_scn_tracking TRUE TRUE TRUE
dbwr_io_slaves 0 4294967295 TRUE
db_writer_processes 10 0 TRUE
db_16k_cache_size 0 0 TRUE
db_2k_cache_size 0 0 TRUE
db_32k_cache_size 0 0 TRUE
db_4k_cache_size 0 0 TRUE
db_8k_cache_size 0 0 TRUE
ddl_lock_timeout 0 0 TRUE
_default_pct_free 1 0 FALSE
default_sharing METADATA metadata TRUE
deferred_segment_creation TRUE TRUE TRUE
dg_broker_config_file1 /u02/app/oracle/product/
dg_broker_config_file2 /u02/app/oracle/product/
dg_broker_start FALSE FALSE TRUE
diagnostic_dest /u02/app/oracle ?#/log FALSE
_disable_cell_optimized_backups TRUE FALSE FALSE
disable_pdb_feature 0 0 TRUE
disk_asynch_io TRUE TRUE TRUE
distributed_lock_timeout 60 60 TRUE
dml_locks 66156 4294967295 TRUE
dnfs_batch_size 4096 4096 TRUE
dst_upgrade_insert_conv TRUE TRUE TRUE
_edition_enable_oracle_users C##CLOUD$SERVICE, OML, C##OMLREST NULL FALSE
enable_automatic_maintenance_pdb TRUE TRUE TRUE
enable_ddl_logging FALSE FALSE TRUE
enable_dnfs_dispatcher FALSE FALSE TRUE
enabled_PDBs_on_standby * * TRUE
enable_goldengate_replication TRUE FALSE FALSE
_enable_guid_endpoint_service FALSE TRUE FALSE
_enable_parallel_dml TRUE FALSE FALSE
enable_pluggable_database TRUE FALSE FALSE
encrypt_new_tablespaces CLOUD_ONLY CLOUD_ONLY TRUE
exafusion_enabled 1 1 TRUE
external_keystore_credential_location #NAME? NONE FALSE
fal_client NONE TRUE
fal_server NONE TRUE
fast_start_io_target 0 0 TRUE
fast_start_mttr_target 0 0 TRUE
fast_start_parallel_rollback LOW LOW TRUE
fileio_network_adapters NULL TRUE
file_mapping FALSE FALSE TRUE
filesystemio_options none asynch TRUE
_fix_control 28210382:OFF NULL FALSE
fixed_date NONE TRUE
gcs_server_processes 4 0 TRUE
global_names FALSE FALSE TRUE
global_txn_processes 1 1 TRUE
hash_area_size 131072 0 TRUE
heat_map OFF OFF TRUE
hi_shared_memory_address 0 0 TRUE
hs_autoregister TRUE TRUE TRUE
inmemory_adg_enabled TRUE TRUE TRUE
inmemory_clause_default NONE TRUE
inmemory_expressions_usage ENABLE ENABLE TRUE
inmemory_max_populate_servers 42 65535 TRUE
inmemory_query ENABLE ENABLE TRUE
inmemory_size 1G 0 FALSE
1 1 TRUE
inmemory_virtual_columns MANUAL MANUAL TRUE
instance_abort_delay_time 0 0 TRUE
instance_groups NULL TRUE
instance_mode READ-WRITE read-write TRUE
instance_name eig1pod5 NULL TRUE
instance_number 5 0 FALSE
instance_type RDBMS RDBMS TRUE
instant_restore FALSE FALSE TRUE
java_jit_enabled TRUE TRUE TRUE
java_max_sessionspace_size 0 0 TRUE
java_pool_size 0 25165824 TRUE
java_restrict none none TRUE
java_soft_sessionspace_limit 0 0 TRUE
job_queue_processes 4000 4000 TRUE
_kd_rows_chk FALSE TRUE TRUE
large_pool_size 0 134217728 TRUE
ldap_directory_access NONE NONE TRUE
ldap_directory_sysauth no no TRUE
_ldr_io_size 33554432 262144 FALSE
_ldr_io_size2 33554432 1048576 FALSE
license_max_sessions 0 0 TRUE
license_max_users 0 0 TRUE
license_sessions_warning 0 0 TRUE
lock_name_space NULL TRUE
log_archive_config NONE TRUE
log_archive_dest NONE TRUE
log_archive_dest_state_1 enable enable TRUE
log_archive_dest_state_10 enable enable TRUE
log_archive_dest_state_11 enable enable TRUE
log_archive_dest_state_12 enable enable TRUE
log_archive_dest_state_13 enable enable TRUE
log_archive_dest_state_14 enable enable TRUE
log_archive_dest_state_15 enable enable TRUE
log_archive_dest_state_16 enable enable TRUE
log_archive_dest_state_17 enable enable TRUE
log_archive_dest_state_18 enable enable TRUE
log_archive_dest_state_19 enable enable TRUE
log_archive_dest_state_2 enable enable TRUE
log_archive_dest_state_20 enable enable TRUE
log_archive_dest_state_21 enable enable TRUE
log_archive_dest_state_22 enable enable TRUE
log_archive_dest_state_23 enable enable TRUE
log_archive_dest_state_24 enable enable TRUE
log_archive_dest_state_25 enable enable TRUE
log_archive_dest_state_26 enable enable TRUE
log_archive_dest_state_27 enable enable TRUE
log_archive_dest_state_28 enable enable TRUE
log_archive_dest_state_29 enable enable TRUE
log_archive_dest_state_3 enable enable TRUE
log_archive_dest_state_30 enable enable TRUE
log_archive_dest_state_31 enable enable TRUE
log_archive_dest_state_4 enable enable TRUE
log_archive_dest_state_5 enable enable TRUE
log_archive_dest_state_6 enable enable TRUE
log_archive_dest_state_7 enable enable TRUE
log_archive_dest_state_8 enable enable TRUE
log_archive_dest_state_9 enable enable TRUE
log_archive_dest_1 NONE TRUE
log_archive_dest_10 NONE TRUE
log_archive_dest_11 NONE TRUE
log_archive_dest_12 NONE TRUE
log_archive_dest_13 NONE TRUE
log_archive_dest_14 NONE TRUE
log_archive_dest_15 NONE TRUE
log_archive_dest_16 NONE TRUE
log_archive_dest_17 NONE TRUE
log_archive_dest_18 NONE TRUE
log_archive_dest_19 NONE TRUE
log_archive_dest_2 NONE TRUE
log_archive_dest_20 NONE TRUE
log_archive_dest_21 NONE TRUE
log_archive_dest_22 NONE TRUE
log_archive_dest_23 NONE TRUE
log_archive_dest_24 NONE TRUE
log_archive_dest_25 NONE TRUE
log_archive_dest_26 NONE TRUE
log_archive_dest_27 NONE TRUE
log_archive_dest_28 NONE TRUE
log_archive_dest_29 NONE TRUE
log_archive_dest_3 NONE TRUE
log_archive_dest_30 NONE TRUE
log_archive_dest_31 NONE TRUE
log_archive_dest_4 NONE TRUE
log_archive_dest_5 NONE TRUE
log_archive_dest_6 NONE TRUE
log_archive_dest_7 NONE TRUE
log_archive_dest_8 NONE TRUE
log_archive_dest_9 NONE TRUE
log_archive_duplex_dest NONE TRUE
log_archive_format %t_%s_%r.dbf %t_%s_%r.dbf FALSE
log_archive_max_processes 4 4 TRUE
log_archive_min_succeed_dest 1 1 TRUE
log_archive_start FALSE FALSE TRUE
log_archive_trace 0 0 TRUE
log_buffer 479840K 0 TRUE
log_checkpoint_interval 0 0 TRUE
log_checkpoints_to_alert FALSE FALSE TRUE
log_checkpoint_timeout 1800 1800 TRUE
log_file_name_convert NULL TRUE
long_module_action TRUE TRUE TRUE
max_datapump_jobs_per_pdb 100 100 TRUE
max_dispatchers 4294967295 TRUE
max_dump_file_size 1073741824 unlimited FALSE
max_idle_blocker_time 5 0 FALSE
max_idle_time 60 0 FALSE
max_iops 0 0 TRUE
_max_io_size 33554432 1048576 FALSE
max_mbps 0 0 TRUE
max_pdbs 4098 4098 TRUE
max_shared_servers 4294967295 TRUE
memory_max_target 0 0 TRUE
memory_target 0 0 TRUE
nls_calendar NONE TRUE
nls_currency NONE TRUE
nls_date_format NONE TRUE
nls_date_language NONE TRUE
nls_dual_currency NONE TRUE
nls_iso_currency NONE TRUE
nls_length_semantics BYTE BYTE TRUE
nls_nchar_conv_excp FALSE FALSE TRUE
nls_numeric_characters NONE TRUE
nls_sort NONE TRUE
nls_time_format NONE TRUE
nls_timestamp_format NONE TRUE
nls_timestamp_tz_format NONE TRUE
nls_time_tz_format NONE TRUE
_no_catalog XDB NULL FALSE
noncdb_compatible FALSE FALSE TRUE
object_cache_max_size_percent 10 10 TRUE
object_cache_optimal_size 10240000 10240000 TRUE
ofs_threads 40 4 FALSE
olap_page_pool_size 0 0 TRUE
one_step_plugin_for_pdb_with_tde FALSE FALSE TRUE
open_cursors 300 50 FALSE
open_links 4 4 TRUE
open_links_per_instance 4 4 TRUE
optimizer_adaptive_plans TRUE TRUE TRUE
optimizer_adaptive_reporting_only FALSE FALSE TRUE
optimizer_adaptive_statistics FALSE FALSE TRUE
_optimizer_allow_all_access_paths FALSE TRUE FALSE
_optimizer_answering_query_using_stats TRUE FALSE FALSE
optimizer_capture_sql_plan_baselines FALSE FALSE TRUE
optimizer_dynamic_sampling 2 2 TRUE
optimizer_features_enable TRUE
_optimizer_gather_stats_on_load_all TRUE FALSE FALSE
_optimizer_gather_stats_on_load_hist TRUE FALSE FALSE
optimizer_ignore_hints TRUE FALSE FALSE
optimizer_ignore_parallel_hints TRUE FALSE FALSE
optimizer_index_caching 0 0 TRUE
optimizer_index_cost_adj 100 100 TRUE
optimizer_inmemory_aware TRUE TRUE TRUE
optimizer_mode ALL_ROWS all_rows TRUE
optimizer_secure_view_merging TRUE TRUE TRUE
optimizer_use_invisible_indexes FALSE FALSE TRUE
optimizer_use_pending_statistics FALSE FALSE TRUE
optimizer_use_sql_plan_baselines TRUE TRUE TRUE
os_authent_prefix ops$ ops$ TRUE
outbound_dblink_protocols ALL ALL TRUE
parallel_adaptive_multi_user FALSE FALSE TRUE
_parallel_cluster_cache_policy ADAPTIVE ADAPTIVE FALSE
parallel_degree_limit CPU CPU TRUE
parallel_degree_policy AUTO MANUAL FALSE
parallel_execution_message_size 32768 4294967295 FALSE
parallel_force_local FALSE FALSE TRUE
parallel_instance_group NONE TRUE
parallel_max_servers 72 4294967295 FALSE
parallel_min_degree CPU 1 FALSE
parallel_min_percent 0 0 TRUE
parallel_min_servers 704 0 FALSE
parallel_min_time_threshold AUTO AUTO TRUE
parallel_servers_target 72 0 TRUE
parallel_threads_per_cpu 1 2 FALSE
pdb_file_name_convert NULL TRUE
_pdb_inherit_cfd TRUE FALSE FALSE
_pdb_ldp_cascade 0 0 FALSE
pdb_lockdown DWCS NULL FALSE
_pdb_lockdown_ddl_clauses 131071 0 FALSE
_pdb_max_audit_size 200M 0 FALSE
_pdb_max_diag_size 100M 0 FALSE
pdb_os_credential NULL TRUE
pdb_template NULL TRUE
permit_92_wrap_format TRUE TRUE TRUE
pga_aggregate_limit 61200M 0 TRUE
pga_aggregate_target 30600M 0 FALSE
plscope_settings identifiers:all IDENTIFIERS:NONE TRUE
plsql_ccflags NONE TRUE
plsql_debug FALSE FALSE TRUE
plsql_optimize_level 2 2 TRUE
plsql_v2_compatibility FALSE FALSE TRUE
plsql_warnings DISABLE:ALL NONE TRUE
pre_page_sga TRUE TRUE TRUE
processes 10000 0 FALSE
processor_group_name NULL TRUE
_px_xtgranule_size 128000 10000 FALSE
query_rewrite_enabled TRUE true TRUE
query_rewrite_integrity TRUSTED enforced FALSE
rdbms_server_dn NONE TRUE
read_only_open_delayed FALSE FALSE TRUE
recovery_parallelism 0 0 TRUE
recyclebin on on TRUE
redo_transport_user NONE TRUE
remote_dependencies_mode TIMESTAMP timestamp TRUE
remote_login_passwordfile EXCLUSIVE exclusive FALSE
remote_os_authent FALSE FALSE TRUE
remote_os_roles FALSE FALSE TRUE
remote_recovery_file_dest NONE TRUE
replication_dependency_tracking TRUE TRUE TRUE
resource_limit TRUE TRUE TRUE
resource_manage_goldengate FALSE FALSE TRUE
resource_manager_cpu_allocation 84 0 TRUE
resource_manager_plan FORCE:DWCS_PLAN NULL FALSE
_restrict_pdb_gv TRUE FALSE FALSE
result_cache_max_result 1 5 FALSE
result_cache_max_size 60M 1 TRUE
result_cache_mode FORCE MANUAL FALSE
result_cache_remote_expiration 0 0 TRUE
resumable_timeout 0 0 TRUE
_rm_exadata_partition_fc TRUE FALSE FALSE
_rm_exadata_pdb_cpu_cnt TRUE FALSE FALSE
rollback_segments NULL TRUE
sec_case_sensitive_logon TRUE TRUE TRUE
sec_max_failed_login_attempts 3 3 TRUE
sec_protocol_error_further_action (DROP,3) (DROP,3) TRUE
sec_protocol_error_trace_action ALERT TRACE FALSE
sec_return_server_release_banner FALSE FALSE TRUE
serial_reuse disable disable TRUE
service_names eig1pod NULL TRUE
session_cached_cursors 50 50 TRUE
session_max_open_files 10 10 TRUE
sessions 600 4294967295 TRUE
sga_max_size 300G 0 FALSE
sga_min_size 0 0 TRUE
sga_target 20400M 0 FALSE
shadow_core_dump partial partial TRUE
shared_memory_address 0 0 TRUE
shared_pool_reserved_size 1422707916 0 TRUE
shared_pool_size 0 134217728 TRUE
shared_servers 1 4294967294 TRUE
shared_server_sessions 4294967295 TRUE
shrd_dupl_table_refresh_rate 60 60 TRUE
skip_unusable_indexes TRUE TRUE TRUE
smtp_out_server NULL TRUE
sort_area_retained_size 0 0 TRUE
sort_area_size 65536 65536 TRUE
spatial_vector_acceleration FALSE FALSE TRUE
_sqlmon_max_plan 4000 0 FALSE
sqltune_category DEFAULT DEFAULT TRUE
sql92_security TRUE TRUE TRUE
standby_archive_dest ?#/dbs/arch ?#/dbs/arch TRUE
standby_db_preserve_states NONE NONE TRUE
standby_file_management MANUAL MANUAL TRUE
standby_pdb_source_file_dblink NULL TRUE
standby_pdb_source_file_directory NULL TRUE
star_transformation_enabled FALSE FALSE TRUE
statistics_level TYPICAL TYPICAL TRUE
streams_pool_size 0 0 TRUE
tape_asynch_io TRUE TRUE TRUE
target_pdbs 600 1 TRUE
temp_undo_enabled FALSE FALSE TRUE
thread 5 0 FALSE
threaded_execution FALSE FALSE TRUE
timed_os_statistics 0 0 TRUE
timed_statistics TRUE FALSE TRUE
trace_enabled TRUE TRUE TRUE
tracefile_identifier NULL TRUE
transactions 16539 4294967295 TRUE
transactions_per_rollback_segment 5 5 TRUE
_undo_autotune FALSE TRUE FALSE
undo_management AUTO AUTO TRUE
undo_retention 900 900 TRUE
undo_tablespace UNDOTBS1 NONE FALSE
unified_audit_sga_queue_size 1048576 1048576 TRUE
uniform_log_timestamp_format TRUE TRUE TRUE
use_dedicated_broker FALSE TRUE TRUE
use_large_pages TRUE TRUE TRUE
user_dump_dest /u02/app/oracle/product/
$ORACLE_BASE/admin/@/udump TRUE
utl_file_dir NULL TRUE
workarea_size_policy AUTO AUTO TRUE
xml_db_events enable enable TRUE