Core Database Operations

Full Database Export

exp system/admin@TARGET_SID file=/backup/db_full.dmp full=y consistent=y

Full Database Import

imp system/admin@TARGET_SID file=/import/full_dump.dmp full=y commit=y ignore=y

Provision Tablespaces

CREATE TABLESPACE APP_LOGS 
  DATAFILE '/u02/oradata/PROD/log01.dbf' SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED 
  ONLINE PERMANENT 
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M 
  SEGMENT SPACE MANAGEMENT MANUAL;

User Creation and Privilege Assignment

DROP USER reporting_service CASCADE;
CREATE USER reporting_service IDENTIFIED BY R3p0rt!ng_SecUrE
  DEFAULT TABLESPACE APP_LOGS 
  TEMPORARY TABLESPACE TEMP 
  QUOTA UNLIMITED ON APP_LOGS;

GRANT CONNECT, RESOURCE, DBA TO reporting_service WITH ADMIN OPTION;
ALTER USER reporting_service DEFAULT ROLE ALL;

BEGIN
  dbms_java.grant_permission('REPORTING_SERVICE', 'SYS:java.net.SocketPermission', '192.168.1.50:8080', 'connect, resolve');
END;
/

Inspect Password Policy Constraints

SELECT profile, limit 
FROM dba_profiles 
WHERE resource_type = 'PASSWORD' 
  AND resource_name = 'PASSWORD_LIFE_TIME';

Update Authentication Credentials

ALTER USER reporting_service IDENTIFIED BY N3w_P@ssw0rd_Changed;

Verify Account Lifecycle Status

SELECT username, profile, account_status, expiry_date, lock_date 
FROM dba_users 
WHERE username IN ('REPORTING_SERVICE', 'SYSTEM', 'SYS');

Adjust Password Expriation Policies

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Runtime Diagnostics and Monitoring

Active Session Metadata

SELECT username, sid, serial#, machine, program, status, last_call_et
FROM v$session
WHERE audsid = USERENV('SESSIONID');

Connection Distribution Analysis

SELECT 
  s.machine AS client_host, 
  SUBSTR(s.program, 1, 30) AS application_tool, 
  COUNT(*) AS concurrent_sessions
FROM v$process p
INNER JOIN v$session s ON p.addr = s.paddr
WHERE s.type != 'BACKGROUND'
  AND s.username IS NOT NULL
GROUP BY s.machine, SUBSTR(s.program, 1, 30)
ORDER BY concurrent_sessions DESC;

Storage Layout Inspection

SELECT 
  tablespace_name, 
  file_id, 
  SUBSTR(file_name, 1, 60) AS datafile_path, 
  ROUND(bytes / 1048576, 2) AS capacity_gb
FROM dba_data_files
ORDER BY tablespace_name, file_id;

Space Utilization Metrics

WITH allocated AS (
  SELECT tablespace_name, SUM(bytes) / 1048576 AS total_mb
  FROM dba_data_files
  GROUP BY tablespace_name
),
free_space AS (
  SELECT tablespace_name, SUM(bytes) / 1048576 AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
)
SELECT 
  a.tablespace_name,
  a.total_mb AS provisioned_mb,
  ROUND(a.total_mb - f.free_mb, 2) AS consumed_mb,
  f.free_mb AS remaining_mb,
  ROUND(((a.total_mb - f.free_mb) / a.total_mb) * 100, 2) AS occupancy_percent
FROM allocated a
JOIN free_space f ON a.tablespace_name = f.tablespace_name
ORDER BY occupancy_percent DESC;