DBA Scripts and Articles
I recently came across an issue while exporting a huge partitioned table for a data migration. The export took years without any obvious reason. After some research I found that the parallelism wasn’t working, I could only see one datapump worker at a time, then I found this note explaining the behaviour (Doc ID 1467662.1) … Continue reading Datapump in parallel with a shell script
It can be quite useful to be able to monitor long running operations on your database, the view v$session_longops can be used to estimate the time necessary for certain operations to finish. Monitoring long running operations scripts Here is a script to monitor a RMAN backup: [crayon-5863774448cb1162851675/] Here is a sample result: [crayon-5863774448cbb212560092/] Here is … Continue reading Monitoring long running operations
This script will help you to move all objects from one tablespace to another, note that you will need downtime to move tables to other tablespace with this script, the operation can be done online using the dbms_redefinition package thought. Moving objects [crayon-5820d56021c6e347798014/] You can also modify the script to move objects of a specific … Continue reading Moving objects from one tablespace to another
The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows … Continue reading Database maintenance tasks
Purpose Statistics are primordial for the optimizer to choose the best execution plan possible. Sometimes you encounter a sub-optimal plan and need to find out if the tables involved in the statement are up to date. This query will help you find out which tables have been modified significantly since the last statistics gathering. For … Continue reading Find missing or stale statistics
The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database. v$log_history queries You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the … Continue reading Analyze database activity using v$log_history
What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable. The tool does not need … Continue reading SQLcl, a revolution for SQL*Plus users
This query returns the top 5 wait events for the last hour from the v$active_session_history view. Be careful, this view is part of the diagnostic pack, you should not query this view if you not licensed for it. Top 5 wait events from v$active_session_history [crayon-5820d56023beb531338939/] This is obviously an approximation, because v$active_session_history contains only 1 second samples, and … Continue reading Top 5 wait events from v$active_session_history
What is Oracle Data Redaction ? Oracle Data Redaction is meant to mask (redact) sensitive data returned from application queries. Oracle Data Redaction doesn’t make change to data on disk, the sensitive data is redacted on the fly before it is returned to the application. You can redact column data by using one of the following methods: Full … Continue reading OCP 12C – Oracle Data Redaction
Use Resource Manager for a CDB and a PDB Managing Resources between PDBs The Resource Manager uses Shares ans Utilization limit to manage resources allocated to PDBs. The more “Shares” you allocate to a PDB, the more resource it will have. Shares are allocated through DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE. One directive can only concern one PDB and you can’t … Continue reading OCP 12C – Resource Manager and Performance Enhancements
The post OCP 12C – Resource Manager and Performance Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).
Extended Character Data Type Columns In this release Oracle changed the maximum sixe of three data types In Oracle 12c if you set a VARCHAR2 to 4000 bytes or less it is stored inline, if you set it to more than 4000 bytes then it is transformed in extended character data type and stored out … Continue reading OCP 12C – SQL Enhancements
Oracle DataPump Enhancements Full Transportable Export and Import of Data In Oracle 12c you now have the possibility to create full transportable exports and imports. A full transportable export contains all objects and data needed to create a copy of the database. To create a fully transportable export of your database you need to specify … Continue reading OCP 12C – DataPump, SQL*Loader, External Tables Enhancements
The post OCP 12C – DataPump, SQL*Loader, External Tables Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).