Oracle Database link created was not connecting succesfully and getting an error
ORA-12154: TNS:could not resolve the connect identifier specified
Resolution: Check the connection url / port / SID details used in the Database link connection string for any format / syntax errors.
JDBC_00 - Cannot connect to specified database: com.zaxxer.hikari.pool.HikariPool$
PoolInitializationException: Failed to initialize pool: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
We faced this issue connecting to Oracle 19C PDB using connection string format
jdbc:oracle:thin@<host_url>:<port>:<OracleSID>
Resolution: The fix required is to use a forward slash instead of colon.
jdbc:oracle:thin@<host_url>:<port>/<OracleSID>
SELECT VIEW_NAME FROM dba_views WHERE view_name LIKE ‘DBA%’ ORDER BY VIEW_NAME;
DBA_2PC_NEIGHBORS DBA_2PC_PENDING DBA_ACL_NAME_MAP DBA_ADDM_FDG_BREAKDOWN DBA_ADDM_FINDINGS DBA_ADDM_INSTANCES DBA_ADDM_SYSTEM_DIRECTIVES DBA_ADDM_TASKS DBA_ADDM_TASK_DIRECTIVES DBA_ADVISOR_ACTIONS DBA_ADVISOR_COMMANDS DBA_ADVISOR_DEFINITIONS DBA_ADVISOR_DEF_PARAMETERS DBA_ADVISOR_DIR_DEFINITIONS DBA_ADVISOR_DIR_INSTANCES DBA_ADVISOR_DIR_TASK_INST DBA_ADVISOR_EXECUTIONS DBA_ADVISOR_EXECUTION_TYPES DBA_ADVISOR_EXEC_PARAMETERS DBA_ADVISOR_FDG_BREAKDOWN DBA_ADVISOR_FINDINGS DBA_ADVISOR_FINDING_NAMES DBA_ADVISOR_JOURNAL DBA_ADVISOR_LOG DBA_ADVISOR_OBJECTS DBA_ADVISOR_OBJECT_TYPES DBA_ADVISOR_PARAMETERS DBA_ADVISOR_PARAMETERS_PROJ DBA_ADVISOR_RATIONALE DBA_ADVISOR_RECOMMENDATIONS DBA_ADVISOR_SQLA_COLVOL DBA_ADVISOR_SQLA_REC_SUM DBA_ADVISOR_SQLA_TABLES DBA_ADVISOR_SQLA_TABVOL DBA_ADVISOR_SQLA_WK_MAP DBA_ADVISOR_SQLA_WK_STMTS DBA_ADVISOR_SQLA_WK_SUM DBA_ADVISOR_SQLPLANS DBA_ADVISOR_SQLSTATS DBA_ADVISOR_SQLW_COLVOL DBA_ADVISOR_SQLW_JOURNAL DBA_ADVISOR_SQLW_PARAMETERS DBA_ADVISOR_SQLW_STMTS DBA_ADVISOR_SQLW_SUM DBA_ADVISOR_SQLW_TABLES DBA_ADVISOR_SQLW_TABVOL DBA_ADVISOR_SQLW_TEMPLATES DBA_ADVISOR_TASKS DBA_ADVISOR_TEMPLATES DBA_ADVISOR_USAGE DBA_ALERT_ARGUMENTS DBA_ALERT_HISTORY DBA_ALERT_HISTORY_DETAIL DBA_ALL_TABLES DBA_ANALYTIC_VIEWS DBA_ANALYTIC_VIEW_ATTR_CLASS DBA_ANALYTIC_VIEW_BASE_MEAS DBA_ANALYTIC_VIEW_CALC_MEAS DBA_ANALYTIC_VIEW_CLASS DBA_ANALYTIC_VIEW_COLUMNS DBA_ANALYTIC_VIEW_DIMENSIONS DBA_ANALYTIC_VIEW_DIM_CLASS DBA_ANALYTIC_VIEW_HIERS DBA_ANALYTIC_VIEW_HIER_CLASS DBA_ANALYTIC_VIEW_KEYS DBA_ANALYTIC_VIEW_LEVELS DBA_ANALYTIC_VIEW_LEVEL_CLASS DBA_ANALYTIC_VIEW_LVLGRPS DBA_ANALYTIC_VIEW_MEAS_CLASS DBA_ANALYZE_OBJECTS DBA_APPLICATIONS DBA_APPLICATION_ROLES DBA_APPLY DBA_APPLY_CHANGE_HANDLERS DBA_APPLY_CONFLICT_COLUMNS DBA_APPLY_DML_CONF_COLUMNS DBA_APPLY_DML_CONF_HANDLERS DBA_APPLY_DML_HANDLERS DBA_APPLY_ENQUEUE DBA_APPLY_ERROR DBA_APPLY_ERROR_MESSAGES DBA_APPLY_EXECUTE DBA_APPLY_HANDLE_COLLISIONS DBA_APPLY_INSTANTIATED_GLOBAL DBA_APPLY_INSTANTIATED_OBJECTS DBA_APPLY_INSTANTIATED_SCHEMAS DBA_APPLY_KEY_COLUMNS DBA_APPLY_PARAMETERS DBA_APPLY_PROGRESS DBA_APPLY_REPERROR_HANDLERS DBA_APPLY_SPILL_TXN DBA_APPLY_TABLE_COLUMNS DBA_APP_ERRORS DBA_APP_PATCHES DBA_APP_PDB_STATUS DBA_APP_STATEMENTS DBA_APP_VERSIONS DBA_AQ_AGENTS DBA_AQ_AGENT_PRIVS DBA_ARGUMENTS DBA_ASSEMBLIES DBA_ASSOCIATIONS DBA_ATTRIBUTE_DIMENSIONS DBA_ATTRIBUTE_DIM_ATTRS DBA_ATTRIBUTE_DIM_ATTR_CLASS DBA_ATTRIBUTE_DIM_CLASS DBA_ATTRIBUTE_DIM_JOIN_PATHS DBA_ATTRIBUTE_DIM_KEYS DBA_ATTRIBUTE_DIM_LEVELS DBA_ATTRIBUTE_DIM_LEVEL_ATTRS DBA_ATTRIBUTE_DIM_LVL_CLASS DBA_ATTRIBUTE_DIM_ORDER_ATTRS DBA_ATTRIBUTE_DIM_TABLES DBA_ATTRIBUTE_TRANSFORMATIONS DBA_AUDIT_EXISTS DBA_AUDIT_MGMT_CLEANUP_JOBS DBA_AUDIT_MGMT_CLEAN_EVENTS DBA_AUDIT_MGMT_CONFIG_PARAMS DBA_AUDIT_MGMT_LAST_ARCH_TS DBA_AUDIT_OBJECT DBA_AUDIT_POLICIES DBA_AUDIT_POLICY_COLUMNS DBA_AUDIT_SESSION DBA_AUDIT_STATEMENT DBA_AUDIT_TRAIL DBA_AUTOTASK_CLIENT DBA_AUTOTASK_CLIENT_HISTORY DBA_AUTOTASK_CLIENT_JOB DBA_AUTOTASK_JOB_HISTORY DBA_AUTOTASK_OPERATION DBA_AUTOTASK_SCHEDULE DBA_AUTOTASK_STATUS DBA_AUTOTASK_TASK DBA_AUTOTASK_WINDOW_CLIENTS DBA_AUTOTASK_WINDOW_HISTORY DBA_AUTO_SEGADV_CTL DBA_AUTO_SEGADV_SUMMARY DBA_AWS DBA_AW_OBJ DBA_AW_PROP DBA_AW_PS DBA_BASE_TABLE_MVIEWS DBA_BLOCKERS DBA_CAPTURE DBA_CAPTURE_EXTRA_ATTRIBUTES DBA_CAPTURE_PARAMETERS DBA_CAPTURE_PREPARED_DATABASE DBA_CAPTURE_PREPARED_SCHEMAS DBA_CAPTURE_PREPARED_TABLES DBA_CATALOG DBA_CDB_RSRC_PLANS DBA_CDB_RSRC_PLAN_DIRECTIVES DBA_CHANGE_NOTIFICATION_REGS DBA_CHECKED_ROLES DBA_CHECKED_ROLES_PATH DBA_CLUSTERING_DIMENSIONS DBA_CLUSTERING_JOINS DBA_CLUSTERING_KEYS DBA_CLUSTERING_TABLES DBA_CLUSTERS DBA_CLUSTER_HASH_EXPRESSIONS DBA_CLU_COLUMNS DBA_CODE_ROLE_PRIVS DBA_COLL_TYPES DBA_COL_COMMENTS DBA_COL_PENDING_STATS DBA_COL_PRIVS DBA_COL_USAGE_STATISTICS DBA_COMMON_AUDIT_TRAIL DBA_COMPARISON DBA_COMPARISON_COLUMNS DBA_COMPARISON_ROW_DIF DBA_COMPARISON_SCAN DBA_COMPARISON_SCAN_SUMMARY DBA_COMPARISON_SCAN_VALUES DBA_CONNECTION_TESTS DBA_CONNECT_ROLE_GRANTEES DBA_CONSTRAINTS DBA_CONS_COLUMNS DBA_CONS_OBJ_COLUMNS DBA_CONTAINER_DATA DBA_CONTEXT DBA_CPOOL_INFO DBA_CPU_USAGE_STATISTICS DBA_CQ_NOTIFICATION_QUERIES DBA_CREDENTIALS DBA_CUBES DBA_CUBE_ATTRIBUTES DBA_CUBE_ATTR_MAPPINGS DBA_CUBE_ATTR_UNIQUE_KEYS DBA_CUBE_ATTR_VISIBILITY DBA_CUBE_BUILD_PROCESSES DBA_CUBE_CALCULATED_MEMBERS DBA_CUBE_CLASSIFICATIONS DBA_CUBE_DEPENDENCIES DBA_CUBE_DESCRIPTIONS DBA_CUBE_DIMENSIONALITY DBA_CUBE_DIMENSIONS DBA_CUBE_DIMNL_MAPPINGS DBA_CUBE_DIM_LEVELS DBA_CUBE_DIM_MAPPINGS DBA_CUBE_DIM_MODELS DBA_CUBE_DIM_VIEWS DBA_CUBE_DIM_VIEW_COLUMNS DBA_CUBE_HIERARCHIES DBA_CUBE_HIER_LEVELS DBA_CUBE_HIER_VIEWS DBA_CUBE_HIER_VIEW_COLUMNS DBA_CUBE_MAPPINGS DBA_CUBE_MEASURES DBA_CUBE_MEAS_MAPPINGS DBA_CUBE_NAMED_BUILD_SPECS DBA_CUBE_SUB_PARTITION_LEVELS DBA_CUBE_VIEWS DBA_CUBE_VIEW_COLUMNS DBA_DATAPUMP_JOBS DBA_DATAPUMP_SESSIONS DBA_DATA_FILES DBA_DBFS_HS DBA_DBFS_HS_COMMANDS DBA_DBFS_HS_FIXED_PROPERTIES DBA_DBFS_HS_PROPERTIES DBA_DB_LINKS DBA_DB_LINK_SOURCES DBA_DDL_LOCKS DBA_DEPENDENCIES DBA_DIGEST_VERIFIERS DBA_DIMENSIONS DBA_DIM_ATTRIBUTES DBA_DIM_CHILD_OF DBA_DIM_HIERARCHIES DBA_DIM_JOIN_KEY DBA_DIM_LEVELS DBA_DIM_LEVEL_KEY DBA_DIRECTORIES DBA_DISCOVERY_SOURCE DBA_DML_LOCKS DBA_DMT_FREE_SPACE DBA_DMT_USED_EXTENTS DBA_DV_AUTH DBA_DV_CODE DBA_DV_COMMAND_RULE DBA_DV_COMMAND_RULE_ID DBA_DV_DATAPUMP_AUTH DBA_DV_DBCAPTURE_AUTH DBA_DV_DBREPLAY_AUTH DBA_DV_DDL_AUTH DBA_DV_DEBUG_CONNECT_AUTH DBA_DV_DIAGNOSTIC_AUTH DBA_DV_DICTIONARY_ACCTS DBA_DV_FACTOR DBA_DV_FACTOR_LINK DBA_DV_FACTOR_TYPE DBA_DV_IDENTITY DBA_DV_IDENTITY_MAP DBA_DV_INDEX_FUNCTION DBA_DV_JOB_AUTH DBA_DV_MAC_POLICY DBA_DV_MAC_POLICY_FACTOR DBA_DV_MAINTENANCE_AUTH DBA_DV_ORADEBUG DBA_DV_PATCH_ADMIN_AUDIT DBA_DV_POLICY DBA_DV_POLICY_LABEL DBA_DV_POLICY_OBJECT DBA_DV_POLICY_OWNER DBA_DV_PREPROCESSOR_AUTH DBA_DV_PROXY_AUTH DBA_DV_PUB_PRIVS DBA_DV_REALM DBA_DV_REALM_AUTH DBA_DV_REALM_OBJECT DBA_DV_ROLE DBA_DV_RULE DBA_DV_RULE_SET DBA_DV_RULE_SET_RULE DBA_DV_SIMULATION_LOG DBA_DV_STATUS DBA_DV_STATUS DBA_DV_TTS_AUTH DBA_DV_USER_PRIVS DBA_DV_USER_PRIVS_ALL DBA_EDITIONED_TYPES DBA_EDITIONING_VIEWS DBA_EDITIONING_VIEWS_AE DBA_EDITIONING_VIEW_COLS DBA_EDITIONING_VIEW_COLS_AE DBA_EDITIONS DBA_EDITION_COMMENTS DBA_ENABLED_AGGREGATIONS DBA_ENABLED_TRACES DBA_ENCRYPTED_COLUMNS DBA_EPG_DAD_AUTHORIZATION DBA_ERRORS DBA_ERRORS_AE DBA_ERROR_TRANSLATIONS DBA_EVALUATION_CONTEXTS DBA_EVALUATION_CONTEXT_TABLES DBA_EVALUATION_CONTEXT_VARS DBA_EXPORT_OBJECTS DBA_EXPORT_PATHS DBA_EXPRESSION_STATISTICS DBA_EXP_FILES DBA_EXP_OBJECTS DBA_EXP_VERSION DBA_EXTENTS DBA_EXTERNAL_LOCATIONS DBA_EXTERNAL_SCN_ACTIVITY DBA_EXTERNAL_TABLES DBA_FEATURE_USAGE_STATISTICS DBA_FGA_AUDIT_TRAIL DBA_FILE_GROUPS DBA_FILE_GROUP_EXPORT_INFO DBA_FILE_GROUP_FILES DBA_FILE_GROUP_TABLES DBA_FILE_GROUP_TABLESPACES DBA_FILE_GROUP_VERSIONS DBA_FLASHBACK_ARCHIVE DBA_FLASHBACK_ARCHIVE_TABLES DBA_FLASHBACK_ARCHIVE_TS DBA_FLASHBACK_TXN_REPORT DBA_FLASHBACK_TXN_STATE DBA_FREE_SPACE DBA_FREE_SPACE_COALESCED DBA_FREE_SPACE_COALESCED_TMP1 DBA_FREE_SPACE_COALESCED_TMP2 DBA_FREE_SPACE_COALESCED_TMP3 DBA_FREE_SPACE_COALESCED_TMP4 DBA_FREE_SPACE_COALESCED_TMP5 DBA_FREE_SPACE_COALESCED_TMP6 DBA_GEOMETRY_COLUMNS DBA_GG_AUTO_CDR_COLUMNS DBA_GG_AUTO_CDR_COLUMN_GROUPS DBA_GG_AUTO_CDR_TABLES DBA_GG_INBOUND_PROGRESS DBA_GG_PROCEDURE_ANNOTATION DBA_GG_PROC_OBJECT_EXCLUSION DBA_GG_SUPPORTED_PACKAGES DBA_GG_SUPPORTED_PROCEDURES DBA_GLOBAL_CONTEXT DBA_GOLDENGATE_CONTAINER_RULES DBA_GOLDENGATE_INBOUND DBA_GOLDENGATE_NOT_UNIQUE DBA_GOLDENGATE_PRIVILEGES DBA_GOLDENGATE_RULES DBA_GOLDENGATE_SUPPORT_MODE DBA_HANG_MANAGER_PARAMETERS DBA_HEATMAP_TOP_OBJECTS DBA_HEATMAP_TOP_TABLESPACES DBA_HEAT_MAP_SEGMENT DBA_HEAT_MAP_SEG_HISTOGRAM DBA_HIERARCHIES DBA_HIER_CLASS DBA_HIER_COLUMNS DBA_HIER_HIER_ATTRIBUTES DBA_HIER_HIER_ATTR_CLASS DBA_HIER_JOIN_PATHS DBA_HIER_LEVELS DBA_HIER_LEVEL_ID_ATTRS DBA_HIGH_WATER_MARK_STATISTICS DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_APPLY_SUMMARY DBA_HIST_ASH_SNAPSHOT DBA_HIST_ASM_BAD_DISK DBA_HIST_ASM_DISKGROUP DBA_HIST_ASM_DISKGROUP_STAT DBA_HIST_ASM_DISK_STAT_SUMMARY DBA_HIST_BASELINE DBA_HIST_BASELINE_DETAILS DBA_HIST_BASELINE_METADATA DBA_HIST_BASELINE_TEMPLATE DBA_HIST_BG_EVENT_SUMMARY DBA_HIST_BUFFERED_QUEUES DBA_HIST_BUFFERED_SUBSCRIBERS DBA_HIST_BUFFER_POOL_STAT DBA_HIST_CAPTURE DBA_HIST_CELL_CONFIG DBA_HIST_CELL_CONFIG_DETAIL DBA_HIST_CELL_DB DBA_HIST_CELL_DISKTYPE DBA_HIST_CELL_DISK_NAME DBA_HIST_CELL_DISK_SUMMARY DBA_HIST_CELL_GLOBAL DBA_HIST_CELL_GLOBAL_SUMMARY DBA_HIST_CELL_IOREASON DBA_HIST_CELL_IOREASON_NAME DBA_HIST_CELL_METRIC_DESC DBA_HIST_CELL_NAME DBA_HIST_CELL_OPEN_ALERTS DBA_HIST_CHANNEL_WAITS DBA_HIST_CLUSTER_INTERCON DBA_HIST_COLORED_SQL DBA_HIST_COMP_IOSTAT DBA_HIST_CON_SYSMETRIC_HIST DBA_HIST_CON_SYSMETRIC_SUMM DBA_HIST_CON_SYSSTAT DBA_HIST_CON_SYSTEM_EVENT DBA_HIST_CON_SYS_TIME_MODEL DBA_HIST_CR_BLOCK_SERVER DBA_HIST_CURRENT_BLOCK_SERVER DBA_HIST_DATABASE_INSTANCE DBA_HIST_DATAFILE DBA_HIST_DB_CACHE_ADVICE DBA_HIST_DISPATCHER DBA_HIST_DLM_MISC DBA_HIST_DYN_REMASTER_STATS DBA_HIST_ENQUEUE_STAT DBA_HIST_EVENT_HISTOGRAM DBA_HIST_EVENT_NAME DBA_HIST_FILEMETRIC_HISTORY DBA_HIST_FILESTATXS DBA_HIST_IC_CLIENT_STATS DBA_HIST_IC_DEVICE_STATS DBA_HIST_IM_SEG_STAT DBA_HIST_IM_SEG_STAT_OBJ DBA_HIST_INSTANCE_RECOVERY DBA_HIST_INST_CACHE_TRANSFER DBA_HIST_INTERCONNECT_PINGS DBA_HIST_IOSTAT_DETAIL DBA_HIST_IOSTAT_FILETYPE DBA_HIST_IOSTAT_FILETYPE_NAME DBA_HIST_IOSTAT_FUNCTION DBA_HIST_IOSTAT_FUNCTION_NAME DBA_HIST_JAVA_POOL_ADVICE DBA_HIST_LATCH DBA_HIST_LATCH_CHILDREN DBA_HIST_LATCH_MISSES_SUMMARY DBA_HIST_LATCH_NAME DBA_HIST_LATCH_PARENT DBA_HIST_LIBRARYCACHE DBA_HIST_LMS_STATS DBA_HIST_LOG DBA_HIST_MEMORY_RESIZE_OPS DBA_HIST_MEMORY_TARGET_ADVICE DBA_HIST_MEM_DYNAMIC_COMP DBA_HIST_METRIC_NAME DBA_HIST_MTTR_TARGET_ADVICE DBA_HIST_MUTEX_SLEEP DBA_HIST_MVPARAMETER DBA_HIST_OPTIMIZER_ENV DBA_HIST_OSSTAT DBA_HIST_OSSTAT_NAME DBA_HIST_PARAMETER DBA_HIST_PARAMETER_NAME DBA_HIST_PDB_INSTANCE DBA_HIST_PDB_IN_SNAP DBA_HIST_PERSISTENT_QMN_CACHE DBA_HIST_PERSISTENT_QUEUES DBA_HIST_PERSISTENT_SUBS DBA_HIST_PGASTAT DBA_HIST_PGA_TARGET_ADVICE DBA_HIST_PLAN_OPERATION_NAME DBA_HIST_PLAN_OPTION_NAME DBA_HIST_PROCESS_MEM_SUMMARY DBA_HIST_PROCESS_WAITTIME DBA_HIST_RECOVERY_PROGRESS DBA_HIST_REPLICATION_TBL_STATS DBA_HIST_REPLICATION_TXN_STATS DBA_HIST_REPORTS DBA_HIST_REPORTS_CONTROL DBA_HIST_REPORTS_DETAILS DBA_HIST_REPORTS_TIMEBANDS DBA_HIST_RESOURCE_LIMIT DBA_HIST_ROWCACHE_SUMMARY DBA_HIST_RSRC_CONSUMER_GROUP DBA_HIST_RSRC_METRIC DBA_HIST_RSRC_PDB_METRIC DBA_HIST_RSRC_PLAN DBA_HIST_RULE_SET DBA_HIST_SEG_STAT DBA_HIST_SEG_STAT_OBJ DBA_HIST_SERVICE_NAME DBA_HIST_SERVICE_STAT DBA_HIST_SERVICE_WAIT_CLASS DBA_HIST_SESSMETRIC_HISTORY DBA_HIST_SESS_SGA_STATS DBA_HIST_SESS_TIME_STATS DBA_HIST_SGA DBA_HIST_SGASTAT DBA_HIST_SGA_TARGET_ADVICE DBA_HIST_SHARED_POOL_ADVICE DBA_HIST_SHARED_SERVER_SUMMARY DBA_HIST_SNAPSHOT DBA_HIST_SNAP_ERROR DBA_HIST_SQLBIND DBA_HIST_SQLCOMMAND_NAME DBA_HIST_SQLSTAT DBA_HIST_SQLTEXT DBA_HIST_SQL_BIND_METADATA DBA_HIST_SQL_PLAN DBA_HIST_SQL_SUMMARY DBA_HIST_SQL_WORKAREA_HSTGRM DBA_HIST_STAT_NAME DBA_HIST_STREAMS_APPLY_SUM DBA_HIST_STREAMS_CAPTURE DBA_HIST_STREAMS_POOL_ADVICE DBA_HIST_SYSMETRIC_HISTORY DBA_HIST_SYSMETRIC_SUMMARY DBA_HIST_SYSSTAT DBA_HIST_SYSTEM_EVENT DBA_HIST_SYS_TIME_MODEL DBA_HIST_TABLESPACE DBA_HIST_TABLESPACE_STAT DBA_HIST_TBSPC_SPACE_USAGE DBA_HIST_TEMPFILE DBA_HIST_TEMPSTATXS DBA_HIST_THREAD DBA_HIST_TOPLEVELCALL_NAME DBA_HIST_UNDOSTAT DBA_HIST_WAITCLASSMET_HISTORY DBA_HIST_WAITSTAT DBA_HIST_WR_CONTROL DBA_HIST_WR_SETTINGS DBA_HOST_ACES DBA_HOST_ACLS DBA_IDENTIFIERS DBA_ILMDATAMOVEMENTPOLICIES DBA_ILMEVALUATIONDETAILS DBA_ILMOBJECTS DBA_ILMPARAMETERS DBA_ILMPOLICIES DBA_ILMRESULTS DBA_ILMTASKS DBA_IM_EXPRESSIONS DBA_INDEXES DBA_INDEXTYPES DBA_INDEXTYPE_ARRAYTYPES DBA_INDEXTYPE_COMMENTS DBA_INDEXTYPE_OPERATORS DBA_INDEX_USAGE DBA_IND_COLUMNS DBA_IND_COLUMNS_V$ DBA_IND_EXPRESSIONS DBA_IND_PARTITIONS DBA_IND_PENDING_STATS DBA_IND_STATISTICS DBA_IND_SUBPARTITIONS DBA_INMEMORY_AIMTASKDETAILS DBA_INMEMORY_AIMTASKS DBA_INTERNAL_TRIGGERS DBA_INVALID_OBJECTS DBA_JAVA_ARGUMENTS DBA_JAVA_CLASSES DBA_JAVA_COMPILER_OPTIONS DBA_JAVA_DERIVATIONS DBA_JAVA_FIELDS DBA_JAVA_IMPLEMENTS DBA_JAVA_INNERS DBA_JAVA_LAYOUTS DBA_JAVA_METHODS DBA_JAVA_NCOMPS DBA_JAVA_POLICY DBA_JAVA_RESOLVERS DBA_JAVA_THROWS DBA_JOBS DBA_JOBS_RUNNING DBA_JOINGROUPS DBA_JOIN_IND_COLUMNS DBA_JSON_COLUMNS DBA_JSON_DATAGUIDES DBA_JSON_DATAGUIDE_FIELDS DBA_KEEPSIZES DBA_KGLLOCK DBA_LBAC_DATA_LABELS DBA_LBAC_LABELS DBA_LBAC_LABEL_TAGS DBA_LBAC_POLICIES DBA_LBAC_SCHEMA_POLICIES DBA_LBAC_TABLE_POLICIES DBA_LIBRARIES DBA_LMT_FREE_SPACE DBA_LMT_USED_EXTENTS DBA_LOBS DBA_LOB_PARTITIONS DBA_LOB_SUBPARTITIONS DBA_LOB_TEMPLATES DBA_LOCK DBA_LOCKDOWN_PROFILES DBA_LOCK_INTERNAL DBA_LOGMNR_LOG DBA_LOGMNR_PURGED_LOG DBA_LOGMNR_SESSION DBA_LOGSTDBY_EDS_SUPPORTED DBA_LOGSTDBY_EDS_TABLES DBA_LOGSTDBY_EVENTS DBA_LOGSTDBY_HISTORY DBA_LOGSTDBY_LOG DBA_LOGSTDBY_NOT_UNIQUE DBA_LOGSTDBY_PARAMETERS DBA_LOGSTDBY_PLSQL_MAP DBA_LOGSTDBY_PLSQL_SUPPORT DBA_LOGSTDBY_PROGRESS DBA_LOGSTDBY_SKIP DBA_LOGSTDBY_SKIP_TRANSACTION DBA_LOGSTDBY_UNSUPPORTED DBA_LOGSTDBY_UNSUPPORTED_TABLE DBA_LOG_GROUPS DBA_LOG_GROUP_COLUMNS DBA_MEASURE_FOLDERS DBA_MEASURE_FOLDER_CONTENTS DBA_MEASURE_FOLDER_SUBFOLDERS DBA_METADATA_PROPERTIES DBA_METHOD_PARAMS DBA_METHOD_RESULTS DBA_MINING_MODELS DBA_MINING_MODEL_ATTRIBUTES DBA_MINING_MODEL_PARTITIONS DBA_MINING_MODEL_SETTINGS DBA_MINING_MODEL_TABLES DBA_MINING_MODEL_VIEWS DBA_MINING_MODEL_XFORMS DBA_MVIEWS DBA_MVIEW_AGGREGATES DBA_MVIEW_ANALYSIS DBA_MVIEW_COMMENTS DBA_MVIEW_DETAIL_PARTITION DBA_MVIEW_DETAIL_RELATIONS DBA_MVIEW_DETAIL_SUBPARTITION DBA_MVIEW_JOINS DBA_MVIEW_KEYS DBA_MVIEW_LOGS DBA_MVIEW_LOG_FILTER_COLS DBA_MVIEW_REFRESH_TIMES DBA_MVREF_CHANGE_STATS DBA_MVREF_RUN_STATS DBA_MVREF_STATS DBA_MVREF_STATS_PARAMS DBA_MVREF_STATS_SYS_DEFAULTS DBA_MVREF_STMT_STATS DBA_NESTED_TABLES DBA_NESTED_TABLE_COLS DBA_NETWORK_ACLS DBA_NETWORK_ACL_PRIVILEGES DBA_OBJECTS DBA_OBJECTS_AE DBA_OBJECT_SIZE DBA_OBJECT_TABLES DBA_OBJECT_USAGE DBA_OBJ_AUDIT_OPTS DBA_OBJ_COLATTRS DBA_OLDIMAGE_COLUMNS DBA_OLS_AUDIT_OPTIONS DBA_OLS_STATUS DBA_OLS_USERS DBA_OPANCILLARY DBA_OPARGUMENTS DBA_OPBINDINGS DBA_OPERATORS DBA_OPERATOR_COMMENTS DBA_OPTSTAT_OPERATIONS DBA_OPTSTAT_OPERATION_TASKS DBA_OUTLINES DBA_OUTLINE_HINTS DBA_OUTSTANDING_ALERTS DBA_PARALLEL_EXECUTE_CHUNKS DBA_PARALLEL_EXECUTE_TASKS DBA_PARTIAL_DROP_TABS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_PART_KEY_COLUMNS DBA_PART_KEY_COLUMNS_V$ DBA_PART_LOBS DBA_PART_TABLES DBA_PDBS DBA_PDB_HISTORY DBA_PDB_SAVED_STATES DBA_PDB_SNAPSHOTS DBA_PENDING_CONV_TABLES DBA_PENDING_TRANSACTIONS DBA_PLSQL_COLL_TYPES DBA_PLSQL_OBJECT_SETTINGS DBA_PLSQL_TYPES DBA_PLSQL_TYPE_ATTRS DBA_POLICIES DBA_POLICY_ATTRIBUTES DBA_POLICY_CONTEXTS DBA_POLICY_GROUPS DBA_PRIVATE_TEMP_TABLES DBA_PRIV_AUDIT_OPTS DBA_PRIV_CAPTURES DBA_PROCEDURES DBA_PROFILES DBA_PROPAGATION DBA_PROXIES DBA_QUEUES DBA_QUEUE_PUBLISHERS DBA_QUEUE_SCHEDULES DBA_QUEUE_SUBSCRIBERS DBA_QUEUE_TABLES DBA_RCHILD DBA_RECOVERABLE_SCRIPT DBA_RECOVERABLE_SCRIPT_BLOCKS DBA_RECOVERABLE_SCRIPT_ERRORS DBA_RECOVERABLE_SCRIPT_HIST DBA_RECOVERABLE_SCRIPT_PARAMS DBA_RECYCLEBIN DBA_REDEFINITION_ERRORS DBA_REDEFINITION_OBJECTS DBA_REDEFINITION_STATUS DBA_REDO_DB DBA_REDO_LOG DBA_REFRESH DBA_REFRESH_CHILDREN DBA_REFS DBA_REGISTERED_ARCHIVED_LOG DBA_REGISTERED_MVIEWS DBA_REGISTERED_SNAPSHOTS DBA_REGISTRY DBA_REGISTRY_DATABASE DBA_REGISTRY_DEPENDENCIES DBA_REGISTRY_ERROR DBA_REGISTRY_HIERARCHY DBA_REGISTRY_HISTORY DBA_REGISTRY_LOG DBA_REGISTRY_PROGRESS DBA_REGISTRY_SCHEMAS DBA_REGISTRY_SQLPATCH DBA_REGISTRY_SQLPATCH_RU_INFO DBA_REMOVED_WORKSPACES DBA_REPLICATION_PROCESS_EVENTS DBA_REPL_DBNAME_MAPPING DBA_RESOURCE_INCARNATIONS DBA_RESUMABLE DBA_REWRITE_EQUIVALENCES DBA_RGROUP DBA_ROLES DBA_ROLE_PRIVS DBA_ROLLBACK_SEGS DBA_ROLLING_DATABASES DBA_ROLLING_EVENTS DBA_ROLLING_PARAMETERS DBA_ROLLING_PLAN DBA_ROLLING_STATISTICS DBA_ROLLING_STATUS DBA_ROLLING_UNSUPPORTED DBA_RSRC_CAPABILITY DBA_RSRC_CATEGORIES DBA_RSRC_CONSUMER_GROUPS DBA_RSRC_CONSUMER_GROUP_PRIVS DBA_RSRC_GROUP_MAPPINGS DBA_RSRC_INSTANCE_CAPABILITY DBA_RSRC_IO_CALIBRATE DBA_RSRC_MANAGER_SYSTEM_PRIVS DBA_RSRC_MAPPING_PRIORITY DBA_RSRC_PLANS DBA_RSRC_PLAN_DIRECTIVES DBA_RSRC_STORAGE_POOL_MAPPING DBA_RULES DBA_RULESETS DBA_RULE_SETS DBA_RULE_SET_RULES DBA_SA_AUDIT_OPTIONS DBA_SA_COMPARTMENTS DBA_SA_DATA_LABELS DBA_SA_GROUPS DBA_SA_GROUP_HIERARCHY DBA_SA_LABELS DBA_SA_LEVELS DBA_SA_POLICIES DBA_SA_PROGRAMS DBA_SA_PROG_PRIVS DBA_SA_SCHEMA_POLICIES DBA_SA_TABLE_POLICIES DBA_SA_USERS DBA_SA_USER_COMPARTMENTS DBA_SA_USER_GROUPS DBA_SA_USER_LABELS DBA_SA_USER_LEVELS DBA_SA_USER_PRIVS DBA_SCHEDULER_CHAINS DBA_SCHEDULER_CHAIN_RULES DBA_SCHEDULER_CHAIN_STEPS DBA_SCHEDULER_CREDENTIALS DBA_SCHEDULER_DB_DESTS DBA_SCHEDULER_DESTS DBA_SCHEDULER_EXTERNAL_DESTS DBA_SCHEDULER_FILE_WATCHERS DBA_SCHEDULER_GLOBAL_ATTRIBUTE DBA_SCHEDULER_GROUPS DBA_SCHEDULER_GROUP_MEMBERS DBA_SCHEDULER_INCOMPATS DBA_SCHEDULER_INCOMPAT_MEMBER DBA_SCHEDULER_JOBS DBA_SCHEDULER_JOB_ARGS DBA_SCHEDULER_JOB_CLASSES DBA_SCHEDULER_JOB_DESTS DBA_SCHEDULER_JOB_LOG DBA_SCHEDULER_JOB_ROLES DBA_SCHEDULER_JOB_RUN_DETAILS DBA_SCHEDULER_NOTIFICATIONS DBA_SCHEDULER_PROGRAMS DBA_SCHEDULER_PROGRAM_ARGS DBA_SCHEDULER_REMOTE_DATABASES DBA_SCHEDULER_REMOTE_JOBSTATE DBA_SCHEDULER_RESOURCES DBA_SCHEDULER_RSC_CONSTRAINTS DBA_SCHEDULER_RUNNING_CHAINS DBA_SCHEDULER_RUNNING_JOBS DBA_SCHEDULER_SCHEDULES DBA_SCHEDULER_WINDOWS DBA_SCHEDULER_WINDOW_DETAILS DBA_SCHEDULER_WINDOW_GROUPS DBA_SCHEDULER_WINDOW_LOG DBA_SCHEDULER_WINGROUP_MEMBERS DBA_SDO_MAPS DBA_SDO_STYLES DBA_SDO_THEMES DBA_SECONDARY_OBJECTS DBA_SECUREFILE_LOGS DBA_SECUREFILE_LOG_INSTANCES DBA_SECUREFILE_LOG_PARTITIONS DBA_SECUREFILE_LOG_TABLES DBA_SEC_RELEVANT_COLS DBA_SEGMENTS DBA_SEGMENTS_OLD DBA_SENSITIVE_COLUMN_TYPES DBA_SENSITIVE_DATA DBA_SEQUENCES DBA_SERVER_REGISTRY DBA_SERVICES DBA_SNAPSHOTS DBA_SNAPSHOT_LOGS DBA_SOURCE DBA_SOURCE_AE DBA_SQLJ_TYPES DBA_SQLJ_TYPE_ATTRS DBA_SQLJ_TYPE_METHODS DBA_SQLSET DBA_SQLSET_BINDS DBA_SQLSET_PLANS DBA_SQLSET_REFERENCES DBA_SQLSET_STATEMENTS DBA_SQLTUNE_BINDS DBA_SQLTUNE_PLANS DBA_SQLTUNE_RATIONALE_PLAN DBA_SQLTUNE_STATISTICS DBA_SQL_MANAGEMENT_CONFIG DBA_SQL_MONITOR_USAGE DBA_SQL_PATCHES DBA_SQL_PLAN_BASELINES DBA_SQL_PLAN_DIRECTIVES DBA_SQL_PLAN_DIR_OBJECTS DBA_SQL_PROFILES DBA_SQL_TRANSLATIONS DBA_SQL_TRANSLATION_PROFILES DBA_SR_GRP_STATUS DBA_SR_GRP_STATUS_ALL DBA_SR_OBJ DBA_SR_OBJ_ALL DBA_SR_OBJ_STATUS DBA_SR_OBJ_STATUS_ALL DBA_SR_PARTN_OPS DBA_SR_STLOG_EXCEPTIONS DBA_SR_STLOG_STATS DBA_SSCR_CAPTURE DBA_SSCR_RESTORE DBA_STATEMENTS DBA_STAT_EXTENSIONS DBA_STMT_AUDIT_OPTS DBA_STORED_SETTINGS DBA_STREAMS_ADD_COLUMN DBA_STREAMS_ADMINISTRATOR DBA_STREAMS_COLUMNS DBA_STREAMS_DELETE_COLUMN DBA_STREAMS_GLOBAL_RULES DBA_STREAMS_KEEP_COLUMNS DBA_STREAMS_MESSAGE_CONSUMERS DBA_STREAMS_MESSAGE_RULES DBA_STREAMS_NEWLY_SUPPORTED DBA_STREAMS_RENAME_COLUMN DBA_STREAMS_RENAME_SCHEMA DBA_STREAMS_RENAME_TABLE DBA_STREAMS_RULES DBA_STREAMS_SCHEMA_RULES DBA_STREAMS_SPLIT_MERGE DBA_STREAMS_SPLIT_MERGE_HIST DBA_STREAMS_STMTS DBA_STREAMS_STMT_HANDLERS DBA_STREAMS_TABLE_RULES DBA_STREAMS_TP_COMPONENT DBA_STREAMS_TP_COMPONENT_LINK DBA_STREAMS_TP_COMPONENT_STAT DBA_STREAMS_TP_DATABASE DBA_STREAMS_TP_PATH_BOTTLENECK DBA_STREAMS_TP_PATH_STAT DBA_STREAMS_TRANSFORMATIONS DBA_STREAMS_TRANSFORM_FUNCTION DBA_STREAMS_UNSUPPORTED DBA_SUBPARTITION_TEMPLATES DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS DBA_SUBPART_KEY_COLUMNS DBA_SUBPART_KEY_COLUMNS_V$ DBA_SUBSCR_REGISTRATIONS DBA_SUMMARIES DBA_SUMMARY_AGGREGATES DBA_SUMMARY_DETAIL_TABLES DBA_SUMMARY_JOINS DBA_SUMMARY_KEYS DBA_SUPPLEMENTAL_LOGGING DBA_SYNC_CAPTURE DBA_SYNC_CAPTURE_PREPARED_TABS DBA_SYNC_CAPTURE_TABLES DBA_SYNONYMS DBA_SYS_PRIVS DBA_TABLES DBA_TABLESPACES DBA_TABLESPACE_GROUPS DBA_TABLESPACE_THRESHOLDS DBA_TABLESPACE_USAGE_METRICS DBA_TAB_COLS DBA_TAB_COLS_V$ DBA_TAB_COLUMNS DBA_TAB_COL_STATISTICS DBA_TAB_COMMENTS DBA_TAB_HISTGRM_PENDING_STATS DBA_TAB_HISTOGRAMS DBA_TAB_IDENTITY_COLS DBA_TAB_MODIFICATIONS DBA_TAB_PARTITIONS DBA_TAB_PENDING_STATS DBA_TAB_PRIVS DBA_TAB_STATISTICS DBA_TAB_STATS_HISTORY DBA_TAB_STAT_PREFS DBA_TAB_SUBPARTITIONS DBA_TEMP_FILES DBA_TEMP_FREE_SPACE DBA_THRESHOLDS DBA_TRANSFORMATIONS DBA_TRIGGERS DBA_TRIGGER_COLS DBA_TRIGGER_ORDERING DBA_TSDP_IMPORT_ERRORS DBA_TSDP_POLICY_CONDITION DBA_TSDP_POLICY_FEATURE DBA_TSDP_POLICY_PARAMETER DBA_TSDP_POLICY_PROTECTION DBA_TSDP_POLICY_TYPE DBA_TSM_DESTINATION DBA_TSM_HISTORY DBA_TSM_SOURCE DBA_TSTZ_TABLES DBA_TSTZ_TAB_COLS DBA_TS_QUOTAS DBA_TUNE_MVIEW DBA_TYPES DBA_TYPE_ATTRS DBA_TYPE_METHODS DBA_TYPE_VERSIONS DBA_UMF_LINK DBA_UMF_REGISTRATION DBA_UMF_SERVICE DBA_UMF_TOPOLOGY DBA_UNDO_EXTENTS DBA_UNUSED_COL_TABS DBA_UNUSED_GRANTS DBA_UNUSED_OBJPRIVS DBA_UNUSED_OBJPRIVS_PATH DBA_UNUSED_PRIVS DBA_UNUSED_SYSPRIVS DBA_UNUSED_SYSPRIVS_PATH DBA_UNUSED_USERPRIVS DBA_UNUSED_USERPRIVS_PATH DBA_UPDATABLE_COLUMNS DBA_USED_OBJPRIVS DBA_USED_OBJPRIVS_PATH DBA_USED_PRIVS DBA_USED_PUBPRIVS DBA_USED_SYSPRIVS DBA_USED_SYSPRIVS_PATH DBA_USED_USERPRIVS DBA_USED_USERPRIVS_PATH DBA_USERS DBA_USERS_WITH_DEFPWD DBA_USTATS DBA_VARRAYS DBA_VIEWS DBA_VIEWS_AE DBA_WAITERS DBA_WALLET_ACES DBA_WALLET_ACLS DBA_WARNING_SETTINGS DBA_WI_CAPTURE_FILES DBA_WI_JOBS DBA_WI_OBJECTS DBA_WI_PATTERNS DBA_WI_PATTERN_ITEMS DBA_WI_STATEMENTS DBA_WI_TEMPLATES DBA_WI_TEMPLATE_EXECUTIONS DBA_WM_SYS_PRIVS DBA_WM_VERSIONED_TABLES DBA_WM_VT_ERRORS DBA_WORKLOAD_ACTIVE_USER_MAP DBA_WORKLOAD_CAPTURES DBA_WORKLOAD_CAPTURE_SQLTEXT DBA_WORKLOAD_CONNECTION_MAP DBA_WORKLOAD_DIV_SUMMARY DBA_WORKLOAD_FILTERS DBA_WORKLOAD_GROUP_ASSIGNMENTS DBA_WORKLOAD_LONG_SQLTEXT DBA_WORKLOAD_REPLAYS DBA_WORKLOAD_REPLAY_CLIENTS DBA_WORKLOAD_REPLAY_DIVERGENCE DBA_WORKLOAD_REPLAY_FILTER_SET DBA_WORKLOAD_REPLAY_SCHEDULES DBA_WORKLOAD_REPLAY_THREAD DBA_WORKLOAD_SCHEDULE_CAPTURES DBA_WORKLOAD_SCHEDULE_ORDERING DBA_WORKLOAD_SQL_MAP DBA_WORKLOAD_TRACKED_COMMITS DBA_WORKLOAD_USER_MAP DBA_WORKSPACES DBA_WORKSPACE_PRIVS DBA_WORKSPACE_SAVEPOINTS DBA_WORKSPACE_SESSIONS DBA_XDS_ACL_REFRESH DBA_XDS_ACL_REFSTAT DBA_XDS_LATEST_ACL_REFSTAT DBA_XMLSCHEMA_LEVEL_VIEW DBA_XMLSCHEMA_LEVEL_VIEW_DUP DBA_XMLTYPE_COLS DBA_XML_INDEXES DBA_XML_NESTED_TABLES DBA_XML_OUT_OF_LINE_TABLES DBA_XML_SCHEMAS DBA_XML_SCHEMA_ATTRIBUTES DBA_XML_SCHEMA_COMPLEX_TYPES DBA_XML_SCHEMA_DEPENDENCY DBA_XML_SCHEMA_ELEMENTS DBA_XML_SCHEMA_IMPORTS DBA_XML_SCHEMA_INCLUDES DBA_XML_SCHEMA_NAMESPACES DBA_XML_SCHEMA_SIMPLE_TYPES DBA_XML_SCHEMA_SUBSTGRP_HEAD DBA_XML_SCHEMA_SUBSTGRP_MBRS DBA_XML_TABLES DBA_XML_TAB_COLS DBA_XML_VIEWS DBA_XML_VIEW_COLS DBA_XSTREAM_ADMINISTRATOR DBA_XSTREAM_INBOUND DBA_XSTREAM_INBOUND_PROGRESS DBA_XSTREAM_OUTBOUND DBA_XSTREAM_OUTBOUND_PROGRESS DBA_XSTREAM_OUT_SUPPORT_MODE DBA_XSTREAM_RULES DBA_XSTREAM_SPLIT_MERGE DBA_XSTREAM_SPLIT_MERGE_HIST DBA_XSTREAM_STMTS DBA_XSTREAM_STMT_HANDLERS DBA_XSTREAM_TRANSFORMATIONS DBA_XS_ACES DBA_XS_ACLS DBA_XS_ACL_PARAMETERS DBA_XS_ACTIVE_SESSIONS DBA_XS_APPLIED_POLICIES DBA_XS_AUDIT_POLICY_OPTIONS DBA_XS_AUDIT_TRAIL DBA_XS_COLUMN_CONSTRAINTS DBA_XS_DYNAMIC_ROLES DBA_XS_ENABLED_AUDIT_POLICIES DBA_XS_EXTERNAL_PRINCIPALS DBA_XS_IMPLIED_PRIVILEGES DBA_XS_INHERITED_REALMS DBA_XS_MODIFIED_POLICIES DBA_XS_NS_TEMPLATES DBA_XS_NS_TEMPLATE_ATTRIBUTES DBA_XS_OBJECTS DBA_XS_POLICIES DBA_XS_PRINCIPALS DBA_XS_PRIVILEGES DBA_XS_PRIVILEGE_GRANTS DBA_XS_PROXY_ROLES DBA_XS_REALM_CONSTRAINTS DBA_XS_ROLES DBA_XS_ROLE_GRANTS DBA_XS_SECURITY_CLASSES DBA_XS_SECURITY_CLASS_DEP DBA_XS_SESSIONS DBA_XS_SESSION_NS_ATTRIBUTES DBA_XS_SESSION_ROLES DBA_XS_USERS DBA_XTERNAL_LOC_PARTITIONS DBA_XTERNAL_LOC_SUBPARTITIONS DBA_XTERNAL_PART_TABLES DBA_XTERNAL_TAB_PARTITIONS DBA_XTERNAL_TAB_SUBPARTITIONS DBA_ZONEMAPS DBA_ZONEMAP_MEASURES
--identifying lob segments using more space: select sg.segment_name, lb.table_name , lb.column_name, sum(sg.bytes)/(1024*1024) mb from dba_segments sg, dba_lobs lb where sg.segment_name = lb.segment_name group by sg.segment_name, lb.table_name , lb.column_name order by mb desc;
--generating shrink command to reduce the lob sizes
select sg.segment_name, lb.owner, lb.table_name, lb.column_name, sum (sg.bytes)/(1024*1024) mb
'alter table ||lb.owner||'.'|| lb.table_name || modify lob (' || lb.column_name || )
(shrink space) ;' shrink_command
from dba_segments sg, dba_lobs lb
where sg.segment_name = lb.segment_name
group by sg.segment_name, lb.owner, lb.table_name, lb.column_name order by mb desc;
--example: alter table hr.employee modify lob (emp_address) (shrink space);
Reference:-
https://oracle-base.com/articles/misc/alter-table-shrink-space-online
We wanted to schedule a nightly backup in a new Oracle 19c DB – however, we could not trigger unix script (expdp) from DBMS_SCHEDULER due a linux PAM missing. So, as a temporary arrangement we used DBMS_DATAPUMP API to perform this task as below. DBMS_DATAPUMP API comes handy when you want to perform export / import entirely within SQL Developer and don’t prefer to use the Unix command prompt.
We created a Procedure EXPORT_SCHEMA and scheduled to run daily as below. You can also find the links I referred below to prepare this.
create or replace NONEDITIONABLE procedure SYSTEM.EXPORT_SCHEMA IS -- Data Pump job handle h1 NUMBER; v_current_time DATE := sysdate; V_DBNAME varchar2(8) := ora_database_name; v_schema_expression varchar2(100) := 'LIKE ''%MYSCHEMAS'''; v_dump_file_name VARCHAR2 (100); v_log_file_name VARCHAR2 (100);
-- BEGIN
-- h1 := dbms_datapump.open('EXPORT', 'SCHEMA', NULL, 'EXP_JOB_' | to char(v_current_time, "YYYY_MM_DD_HH24MI'), 'LATEST');
-- v_ dump_file_name := V_DBNAME || 'DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY')) || '%U.dmp;
-- v_log_file_name := V_DBNAME || '_DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY')) || '_' || to_char(v_current_time, 'YYYY-MM-DD-HH24MI') || '.log';
-- dbms_datapump.add_file (handle => h1, filename => v_dump_file_name, directory => 'DATA_PUMP_DIR', fíletype => dbms_datapump.ku$_file_type_dump_file, reusefile => 1);
-- dbms_datapump.add_file (handle => h1, filename > v_log_file_name, directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
-- --A metadata filter is used to specify the schema that will be exported dbms_datapump.metadata_filter(h1, 'SCHEMA_EXPR', v_schema_expression) ;
-- dbms_datapump.set_parallel (h1, 8);
-- dbms_datapump.set_parameter(handle=>h1,name=>'COMPRESSION',value=>'ALL');
--
--exclude jobs
DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
'EXCLUDE_PATH_EXPR',
'IN (''JOB'')');
-- dbms_datapump.start_job(h1);
-- dbms_datapump.detach(h1); END / grant execute on SYSTEM.EXPORT_SCHEMA TO MYUSER; / begin dbms_scheduler.create_job( job_name=>'EXPORT_DATA_PUMP', job_type=> 'PLSQL_BLOCK', job_action=> 'BEGIN SYSTEM.EXPORT_SCHEMA; END;' , start_date => SYSTIMESTAMP end_date => SYSTIMESTAMP + INTERVAL '3650' day, repeat_interval => 'freq=daily; byday=mon, tue, wed, thu, fri, sat, sun; byhour=20; byminute=0', comments=>'Nightly Backup', enabled=>TRUE, auto_drop=>TRUE); end;
The above job will have 7 backup files being used that have weekday names in the file name and recycled / reused every week. If you cannot accommodate 7 backup files, you can use the following.
create or replace NONEDITIONABLE procedure SYSTEM.EXPORT_SCHEMA IS -- Data Pump job handle h1 NUMBER; v_current_time DATE := sysdate; V_DBNAME varchar2(8) := ora_database_name; v_schema_expression varchar2(100) := 'LIKE ''%MYSCHEMAS''';
v_num_dumps_to_retain number :=4; v_dump_file_name VARCHAR2 (100); v_log_file_name VARCHAR2 (100);
-- BEGIN
-- h1 := dbms_datapump.open('EXPORT', 'SCHEMA', NULL, 'EXP_JOB_' | to char(v_current_time, "YYYY_MM_DD_HH24MI'), 'LATEST');
-- v_ dump_file_name := V_DBNAME || 'DAILY_BACKUP' ||
--trim(to_char(v_current_time, 'DAY'))
mod(to_number(trim(to_char(v_current_time,'J'))),v_num_dumps_to_retain)
|| '%U.dmp;
-- v_log_file_name := V_DBNAME || '_DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY'))
|| '_' || to_char(v_current_time, 'YYYY-MM-DD-HH24MI') || '.log';
-- dbms_datapump.add_file (handle => h1, filename => v_dump_file_name, directory => 'DATA_PUMP_DIR', fíletype => dbms_datapump.ku$_file_type_dump_file, reusefile => 1);
-- dbms_datapump.add_file (handle => h1, filename > v_log_file_name, directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
-- --A metadata filter is used to specify the schema that will be exported dbms_datapump.metadata_filter(h1, 'SCHEMA_EXPR', v_schema_expression) ;
-- dbms_datapump.set_parallel (h1, 8);
-- dbms_datapump.set_parameter(handle=>h1,name=>'COMPRESSION',value=>'ALL');
--
--exclude jobs
DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
'EXCLUDE_PATH_EXPR',
'IN (''JOB'')');
-- dbms_datapump.start_job(h1);
-- dbms_datapump.detach(h1); END /
Reference Links:-
https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_api.htm
https://docs.oracle.com/database/121/SUTIL/GUID-5AAC848B-5A2B-4FD1-97ED-D3A048263118.htm#SUTIL977
The dba tables in Oracle that display the redaction information (Oracle Advanced security) are redaction_columns & redaction_policies tables.
PUBLIC.redaction_columns
PUBLIC.redaction_policies
SYS.redaction_columns
SYS.redaction policiesSELECT * FROM redaction_columns; OBJECT_OWNER|OBJECT_NAME|COLUMN_NAME|FUNCTION_TYPE|FUNCTION_PARAMETERS|REGEXP_PATTERN|REGEXP_REPLACE_STRING|REGEXP_POSITION|REGEXP_OCCURRENCE|REGEXP_MATCH_PARAMETER|COLUMN_DESCRIPTION| SELECT * FROM redaction_policies; OBJECT_OWNER|OBJECT_NAME|POLICY_NAME|EXPRESSION|ENABLE|POLICY_DESCRIPTION|
Oracle indexes may get into UNUSABLE state due to multiple reasons – one of them could be a partition of a Partitioned table containing Global indexes was dropped without using ‘UPDATE INDEXES’ option.
Identifying indexes that are in UNUSABLE state (for a table):-select *
from all_indexes where status = 'UNUSABLE' AND TABLE_NAME = 'my_table';
Rebuild the indexes so that they change to ‘VALID’ state. (use ‘Online’ option if the table is continuously used)
select 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild online;'
from all_indexes where status = 'UNUSABLE' AND TABLE_NAME = 'my_table';
Find out which session is currently locking (causing deadlock) a particular object:-
select a.sid, a.serial# from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='MY_TABLE';
Find out which session(s) have locked an object in the history:-
Step 1:-
Scan the active session history for your session which is blocked by another session
select * from dba_hist_active_sess_history
where blocking_session is not null and current_obj# IN
(select object_id from dba_objects where owner = 'MY_SCHEMA' --Enter the object owner here:
--and object_name = 'TEST1 --Enter the object name here,
);
Step 2:-
Find the SQL ID to see what the blocking session was exactly doing
SELECT * FROM dba_hist_active_sess_history
WHERE SESSION_ID = 1234 AND SESSION_SERIAL# = 12345
and
current_obj# IN
(select object_id from dba_objects where owner = 'MY_SCHEMA' --Enter the object owner here:
--and object_name = 'TEST1 --Enter the object name here,
);
Step 3:-
Check the sql_fulltext of the sql_id in v$sql.
select sql_fulltext from v$sql;
Based on the following sql I got from stackoverflow.
SELECT *
from gv$active_session_history
--Or use this table for further back.
--from dba_hist_active_sess_history
where blocking_session is not null
and current_obj# =
(
select object_id
from dba_objects
where owner = 'JHELLER' --Enter the object owner here.
and object_name = 'TEST1' --Enter the object name here.
);
-- checking tablespace sizes select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name ;
Tablespace|Used MB|Free MB|Total MB|Pct. Free| ----------|-------|-------|--------|---------| SYSAUX | 606| 34| 640| 5| UNDOTBS1 | 23| 2| 25| 8| USERS | 2| 98| 100| 98| SYSTEM | 352| 8| 360| 2|
--check auto extend for a datafile select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;
TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES | ---------------|---------------------------------------|--------------|-----------| USERS |/u01/app/oracle/oradata/XE/users.dbf |YES |11811160064| SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984| UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984| SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|
--set auto extend off for a datafile (I think you cannot change it back) ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' AUTOEXTEND OFF; select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;
TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES | ---------------|---------------------------------------|--------------|-----------| USERS |/u01/app/oracle/oradata/XE/users.dbf |NO | 0| SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984| UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984| SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|
--Resizing a datafile ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' RESIZE 100M;
--adding a new datafile for a tablespace (with Auto extend) to add more memory ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/XE/users3.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M; --adding a new datafile for a tablespace (without Auto extend) to add more memory ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/XE/users2.dbf' SIZE 20M AUTOEXTEND OFF;
The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas. Read more
Checking SGA size of your database:-
SELECT * FROM v$sgainfo;NAME |BYTES |RESIZEABLE| ----------------------------------|----------|----------| Fixed SGA Size | 2233344|No | Redo Buffers | 5541888|No | Buffer Cache Size | 444596224|Yes | Shared Pool Size | 176160768|Yes | Large Pool Size | 4194304|Yes | Java Pool Size | 4194304|Yes | Streams Pool Size | 0|Yes | Shared IO Pool Size | 0|Yes | Granule Size | 4194304|No | Maximum SGA Size |1068937216|No | Startup overhead in Shared Pool | 75497472|No | Free SGA Memory Available | 432013312| |
As you see above, I have 1 GB of SGA in my local DB. Enterprise databases having around 1 TB of SGA is not unusual (to have more buffer cache to reduce the disk reads).
Also, you can see that I have around 424 MB buffer cache and 412 MB free SGA. Let me try to add the free SGA to Buffer cache.
SQL> show parameter cache_size;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_size big integer 0 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_cache_size big integer 0 db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0
SQL> alter system set db_cache_size = 410M scope=both;
System altered.
SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 412M
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
Query to check SGA target advise:-
select * from v$sga_target_advice order by sga_size;
Use this code 