Category: Autonomous Database
-
Generating SQL to Create an External Table- External Tables
If you are currently working with SQL*Loader and want to convert to using external tables, you can use the SQL*Loader to generate the SQL required to create the external table, using the EXTERNAL_TABLE option. A small example will help demonstrate this process. Suppose you have the following table DDL: SQL> create table books (book_id number,…
-
Automating Jobs with Oracle Scheduler- Automation and Troubleshooting
Oracle Scheduler is a tool that provides a way of automating the scheduling of jobs. Oracle Scheduler is implemented via the DBMS_SCHEDULER internal PL/SQL package. Oracle Scheduler offers a sophisticated set of features for scheduling jobs, such as detailed scheduling, privileged-based models, and storing of schedules. There are more than 70 procedures and functions available…
-
Inline SQL from External Table- External Tables
It is possible to select directly from the file with the use of EXTERNAL without actually creating an external table in the data dictionary. This allows for external data to be part of a subquery, virtual view, or another transformation type of process. Here is an example of how this works: SELECT columns FROM EXTERNAL…
-
Viewing Job Details- Automation and Troubleshooting
To view details about how a job is configured, query the DBA_SCHEDULER_JOBS view. This query selects information for the RMAN_BACKUP job: SQL> select job_name ,last_start_date ,last_run_duration ,next_run_date ,repeat_interval from dba_scheduler_jobs where job_name=’RMAN_BACKUP’; Each time a job runs, a record of the job execution is logged in the data dictionary. To check the status of a…
-
Identifying System Bottlenecks – Automation and Troubleshooting
Whenever there are application performance issues or availability problems, seemingly (from the DBA’s perspective), the first question asked is, “What is wrong with the database?” Regardless of the source of the problem, the DBA is often required to look if the database is behaving well. Approaching these issues when looking at the database and system-wide…
-
Checking for Locked Production Accounts- Automation and Troubleshooting
Usually having a database profile should be in place that specifies that a database account become locked after a designated number of failed login attempts. For example, set the DEFAULT profile FAILED_LOGIN_ATTEMPTS to 5. Sometimes, however, a rogue user or developer will attempt to guess the production account password and, after five attempts, lock the…
-
Tablespace Level- External Tables
A tablespace-level export/import operates on objects contained within specific tablespaces. This example exports all objects contained in the USERS tablespace: $ expdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=tbsp.dmp tablespaces=users You can initiate a tablespace-level import by using a full export but specifying the TABLESPACES parameter: $ impdp mv_maint/Pa$$w0rd123! directory=dp_dir dumpfile=full.dmp tablespaces=users A tablespace-level import will attempt to create…
-
Using AWR – Automation and Troubleshooting
An AWR report is good for viewing the entire system’s performance and identifying the top resource-consuming SQL queries. Run the following script to generate an AWR report: SQL> @?/rdbms/admin/awrrpt You can run the AWR reports from the PDB or CDB, root container. The reports will be from PDB using AWR_PDB views in that PDB, or…
-
Loading a Regular Table from the External Table- External Tables
Now, you can load data contained in the external table into a regular database table. When you do this, you can take advantage of Oracle’s direct-path loading and parallel features. This example creates a regular database table that will be loaded with data from the external table: SQL> create table exa_info( exa_id NUMBER ,machine_count NUMBER…
-
Locating the Alert Log and Trace Files – Automation and Troubleshooting
The default alert log directory path has this structure: $ORACLE_HOME/rdbms/log Or find it easily with the show parameter command: SQL> show parameter background You can override the default directory path for the alert log by setting the DIAGNOSTIC_DEST initialization parameter. Usually, the db_unique_name is the same as the instance_name, but it depends on the environment.…