DB Notes

Oracle expdp Export Job Script Oracle expdp Export Job Script This bash script runs an Oracle Data Pump export (expdp) job with anonymized parameters (except the expdp binary path). Simply modify the variables in the script as needed. #!/bin/bash # This script runs an Oracle Data Pump export (expdp) job. # Modify the variables below to change the export settings. # Define variables for the export job ORACLE_CONN="'/ as sysdba'" # Oracle connection string EXPDP_BIN="/u01/app/oracle/product/19/dbhome_1/bin/expdp" # Oracle expdp binary (unchanged) DIRECTORY="EXP_DIR" # Oracle directory object name DUMPFILE="DUMPFILE.dmp" # Dump file name LOGFILE="LOGFILE.log" # Log file name SCHEMAS="SCHEMA1,SCHEMA2" # Comma-separated list of schemas to export PARALLELITY="1" # Parallel export degree # Log the job details ec...

DBA Toolkit: SQL Server Health Check Queries This guide provides a suite of T‑SQL queries that give you a quick snapshot of your SQL Server’s health. The toolkit covers: Database Health Status Top 5 Resource‑Intensive Queries (by CPU time, in seconds) Top 5 Largest Objects (Tables) by Size Backup Status – including full, differential (incremental), and transaction log backups Database Size Summary Memory (RAM) Usage 1. Database Health Status This query returns basic state and configuration info for each database: SELECT name, state_desc, -- e.g. ONLINE, OFFLINE, RECOVERING, SUSPECT, etc. recovery_model_desc, -- SIMPLE, FULL, or BULK_LOGGED user_access_desc, -- MULTI_USER, SINGLE_USER, RESTRICTED_USER is_read_only, is_auto_close_on, is_auto_shrink_on FROM sys.databases; 2. Top 5 Resource‑Intensive Querie...

Handling Expired Oracle Users When an Oracle database user account expires, it needs to be unlocked and assigned to a profile that ensures password expiration does not occur. Follow these steps to handle expired Oracle users. Step 1: Create a Profile with Unlimited Password Lifetime To prevent password expiration, create a profile with unlimited password lifetime: CREATE PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; Step 2: Assign the Profile to the User Assign the newly created profile to the affected user: ALTER USER APP_USER PROFILE APP_PROFILE; Step 3: Reset Password for Expired Users If a user is already expired, their password must be reset: ALTER USER APP_USER IDENTIFIED BY same_password; Step 4: Retrieve the Existing Password (If Necessary) ...

How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g When you accidentally create a datafile in the wrong folder, you can move it to the intended location. Unlike a regular file move, this process in Oracle 11g involves both operating system commands and an update to Oracle’s control file. Follow these steps carefully. Important: Backup: Before you begin, ensure you have a current backup of your database or at least the affected tablespace. Privileges: You must have the appropriate administrative privileges to perform these operations. Environment: The steps differ slightly based on whether you’re dealing with user tablespaces or system-critical tablespaces. Step 1: Identify the Datafile Start by confirming the file name and its current location. Connect to SQL*Plus or your preferred Oracle clie...