Category: Tablespace Level
-
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,…
-
Autonomous Database- Automation and Troubleshooting
The previous scripts and tasks when scheduled are just scratching the surface of automating jobs for the Oracle database. Processes that start to take the results of these scripts and apply the fixes and perform the next actions are getting closer to automation. Oracle 23c database has many processes and hooks that allow for environments…
-
SQL Plan Management – Automation and Troubleshooting
SQL execution plans come in the form of SQL plan baselines for comparing and testing for regressions in SQL statements. SQL plans are captured, and with 23c, plan changes are detected at parse time and validated if the plan has changed. If you think machine learning is taking away the DBA’s performance tuning job, you…
-
Export and Import an Entire Database- External Tables
When you export an entire database, this is referred to as a full export, and the resultant export file contains everything required to make a copy of your database. Unless restricted by filtering parameters, a full export consists of the following: • All DDL required to re-create tablespaces, users, user tables, indexes, constraints, triggers, sequences,…
-
List Contents of Dump Files- External Tables
Data Pump has a robust method of creating a file that contains all the SQL that is executed when an import job runs. Data Pump uses the DBMS_METADATA package to create the DDL that you can use to re-create objects in the Data Pump dump file. Use the SQLFILE option of Data Pump import to…
-
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…
-
Using top – Automation and Troubleshooting
Another quick utility for identifying resource-intensive processes is the top command. Use this tool to quickly identify which processes are the highest consumers of resources on the server. By default, top will repeatedly refresh every three seconds with information regarding the most CPU-intensive processes. $ top And you can run it batch mode and send…
-
Database Troubleshooting- Automation and Troubleshooting
So far there have been scripts provided to help avoid issues with the database. Even in the brief discussion about Autonomous Database, self-repairing was discussed as there are tasks that can be done automatically to fix and have the database up and available. But there are things that might need to be investigated such as…
-
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…
-
External Tables with Oracle Cloud Database- External Tables
Cloud databases let us easily use files from cloud storage, and this is just a quick example of leveraging files in your cloud environment with your database. You don’t have to have a directory created to use external tables, but you need to know the URI for the file that is the namespace and tenancy…