sqlplus related
set pagesize 0 (this sets infinite page size but avoids printing column names in output)
set pagesize 1000 (to avoid printing column names multiple times in output)
set heading off (to suppress printing column names in output)
set timing on (to print the elapsed time)
set heading off (to suppress printing column names in output)
set timing on (to print the elapsed time)
set wrap off (kind of setting wordwarp off)
set linesize xxx (similar to "set wrap off" just to fit each record in the result set in single line)
set trimout on (to match column width with the longest column data)
set trimspool on (to maintain similar output in spool file too)
to format the output of columns
column <column_name> format a7 (str column with length of 7 chars)
column <column_name> format a12 (str column with length of 12 chars)
column <column_name> format 999 (Numeric column with length of 3 digits)
column <column_name> format 99999 (Numeric column with length of 5 digits)
Execute SQL query from OS prompt itself in PuTTY
exit | sqlplus -S /@dbname <<< "<sql query>;"
Below is example for above syntax to check table count in OS prompt itself in PuTTY
exit | sqlplus -S /@dbname <<< "select count(*) from schemaname.tablename;"
Execute multiple SQL queries from OS prompt
Syntax: echo -e "cmd1; \n cmd2; \n query" | sqlplus -S /@dbname
Syntax: echo -e "cmd1; \n cmd2; \n query" | sqlplus -S /@dbname
E.g.: echo -e "set linesize 200; \n set pagesize 100; \n select count(*) from tbl;" | sqlplus -S /@dbname
To get the count of all tables in a schema
E.g.: echo -e "column tablename format a50; \n set linesize 200; \n set pagesize 100; \n select owner||'.'||table_name tablename, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||owner||'.'||table_name)),'/ROWSET/ROW/X')) count from all_tables where owner='schema/owner';" | sqlplus -S /@dbname
Execute multiple SQL queries from file at OS prompt and store output in file along with queries. Kind of verbose mode
Syntax:
Syntax:
Contents of <input_file>
--Begin of input_file
set echo on;
spool <input_file>.out
query1;
query2;
spool off;
exit;
--End of input_file
cat <input_file> | sqlplus /@dbname
set lines 200;
SELECT TO_CHAR (first_time, 'YYYY-MON-DD') DAY,
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '00', 1, 0)), '9999') "00",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '01', 1, 0)), '9999') "01",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '02', 1, 0)), '9999') "02",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '03', 1, 0)), '9999') "03",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '04', 1, 0)), '9999') "04",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '05', 1, 0)), '9999') "05",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '06', 1, 0)), '9999') "06",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '07', 1, 0)), '9999') "07",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '08', 1, 0)), '9999') "08",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '09', 1, 0)), '9999') "09",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '10', 1, 0)), '9999') "10",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '11', 1, 0)), '9999') "11",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '12', 1, 0)), '9999') "12",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '13', 1, 0)), '9999') "13",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '14', 1, 0)), '9999') "14",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '15', 1, 0)), '9999') "15",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '16', 1, 0)), '9999') "16",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '17', 1, 0)), '9999') "17",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '18', 1, 0)), '9999') "18",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '19', 1, 0)), '9999') "19",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '20', 1, 0)), '9999') "20",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '22', 1, 0)), '9999') "22",
TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '23', 1, 0)), '9999') "23"
FROM gv$log_history
WHERE first_time > SYSDATE - 7
GROUP BY TO_CHAR (first_time, 'YYYY-MON-DD')
ORDER BY TO_CHAR (first_time, 'YYYY-MON-DD') DESC;
List all the schema names from database
set pagesize 1000;
select username as schemaname from sys.all_users order by username;
To see the current schema name
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual;
To change the current schema name
ALTER SESSION SET CURRENT_SCHEMA = some_other_schema;
To change the date display format
alter session set NLS_DATE_FORMAT = 'yyyy-MM-dd-HH24.MI.SS';
To get the list of Orphan indexes
set pagesize 1000;
set linesize 1000;
column owner format a20
column object_name format a20
select owner, object_name, object_type, status from dba_objects where status != 'VALID';
To list the invalid objects indexes post DDL changes
SELECT
ROW_NUMBER() OVER (
ORDER BY NVL(s.table_bytes, 0) DESC,
i.table_owner,
i.table_name,
i.index_name
) AS row_num,
i.owner AS index_owner,
i.index_name,
i.table_owner,
i.table_name,
i.orphaned_entries,
i.status,
ROUND(NVL(s.table_bytes, 0) / 1024 / 1024, 2) AS table_mb,
TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS current_time
FROM dba_indexes i
LEFT JOIN (
SELECT
owner,
segment_name,
SUM(bytes) AS table_bytes
FROM dba_segments
WHERE segment_type IN (
'TABLE',
'TABLE PARTITION',
'TABLE SUBPARTITION',
'IOT - TOP'
)
GROUP BY owner, segment_name
) s
ON s.owner = i.table_owner
AND s.segment_name = i.table_name
WHERE i.orphaned_entries = 'YES'
ORDER BY NVL(s.table_bytes, 0) DESC,
i.table_owner,
i.table_name,
i.index_name;
To list DDL changes which were done in the last 5 hours - first 500 changes
SELECT rn,
owner,
object_name,
object_type,
last_ddl_time1
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY last_ddl_time DESC) AS rn,
owner,
object_name,
object_type,
TO_CHAR(last_ddl_time, 'DD/MON/YY HH24:MI:SS') AS last_ddl_time1
FROM dba_objects
WHERE last_ddl_time >= SYSDATE - (5/24) -- last 5 hours
AND last_ddl_time <= SYSDATE
AND owner <> 'SYS'
)
WHERE rn <= 500
ORDER BY rn;
To display configuration and current scheduling of scheduler job "PMO_DEFERRED_GIDX_MAINT_JOB" (this is a built-in Oracle Database scheduler job (owned by SYS) that automatically cleans up orphaned entries in deferred global indexes)
SELECT CON_ID ,
OWNER ,
JOB_NAME ,
JOB_TYPE ,
ENABLED ,
STATE ,
NEXT_RUN_DATE ,
REPEAT_INTERVAL ,
TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') AS CURRENT_TIME
FROM cdb_scheduler_jobs
WHERE job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB';
To stop the Oracle Scheduler job named "PMO_DEFERRED_GIDX_MAINT_JOB"
EXEC DBMS_SCHEDULER.STOP_JOB(
'SYS.PMO_DEFERRED_GIDX_MAINT_JOB',
force => TRUE
);
To schedule stop Oracle Scheduler job named "PMO_STOP_DEFERRED_GIDX_JOB"
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'SYS.PMO_STOP_DEFERRED_GIDX_JOB',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
l_running NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO l_running
FROM DBA_SCHEDULER_RUNNING_JOBS
WHERE OWNER = 'SYS'
AND JOB_NAME = 'PMO_DEFERRED_GIDX_MAINT_JOB';
IF l_running > 0 THEN
DBMS_SCHEDULER.STOP_JOB(
job_name => 'SYS.PMO_DEFERRED_GIDX_MAINT_JOB',
force => TRUE
);
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Ignore "job is no longer running" race condition.
IF SQLCODE != -27366 THEN
RAISE;
END IF;
END;
]',
start_date => TO_TIMESTAMP_TZ(
'2026-08-12 07:00:00 America/New_York',
'YYYY-MM-DD HH24:MI:SS TZR'
),
repeat_interval => 'FREQ=DAILY;BYHOUR=7;BYMINUTE=0;BYSECOND=0',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Stops SYS.PMO_DEFERRED_GIDX_MAINT_JOB if running at 7:00 AM ET daily'
);
END;
/
To check the Oracle Scheduler job named "PMO_STOP_DEFERRED_GIDX_JOB"
SELECT owner,
job_name,
enabled,
state,
start_date,
repeat_interval,
next_run_date
FROM dba_scheduler_jobs
WHERE owner = 'SYS'
AND job_name = 'PMO_STOP_DEFERRED_GIDX_JOB';
To display current partition meta data
SELECT
table_owner,
table_name,
partition_name,
partition_position,
tablespace_name,
high_value,
num_rows,
TO_CHAR(last_analyzed,'DD-MON-RR') AS last_analyzed
FROM
dba_tab_partitions
WHERE
table_owner = 'table_schema'
AND table_name = 'table_name'
ORDER BY
partition_position;
Commands related to Data Guard monitoring
select client_process, process, thread#, sequence#, status from v$managed_standby where client_process='LGWR' or process='MRP0';
#####
Oracle Recycle Bin Consumers and purge:
When you drop a table, Oracle doesn't immediately delete it—it renames it to a
BIN$... object and stores it in the recycle bin so it can be recovered later. These dropped objects continue to consume tablespace until they are purged.- List the consumers of the space
- Purge the consumers
- Repeat Step 1
Command to list Recycle Bin Space consumers:
SELECT rn,
owner,
original_name,
object_name, -- BIN$...
type,
ts_name,
droptime,
space_gb
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY space * ts.block_size DESC) AS rn,
owner,
original_name,
object_name,
type,
ts_name,
droptime,
ROUND(space * ts.block_size / POWER(1024, 3), 2) AS space_gb
FROM dba_recyclebin rb
JOIN dba_tablespaces ts
ON ts.tablespace_name = rb.ts_name
)
FETCH FIRST 50 ROWS ONLY;
purge a table to check
PURGE TABLE "'BIN$xxxxxxxxxxxxxxx'";
purge tablespace tbsp_name;
purge tablespace tbps_name USER username;
####
To list the invalid objects in the database
SELECT
owner,
object_name,
object_type,
status,
last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
To fix the invalid objects in the database listed in the above query
DECLARE
BEGIN
FOR cur IN (
SELECT object_name,
object_type,
owner
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE')
AND owner = 'schema_name'
)
LOOP
BEGIN
IF cur.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE
'ALTER PACKAGE ' || cur.owner || '."' || cur.object_name || '" COMPILE BODY';
ELSE
EXECUTE IMMEDIATE
'ALTER ' || cur.object_type || ' ' ||
cur.owner || '."' || cur.object_name || '" COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
How to get the DDL of a Store Procedure
set long 32000;
SELECT dbms_metadata.GET_DDL('PROCEDURE','sp_name','schema/owner') FROM DUAL;
If long is not set, output will get truncated.
Viewing and changing system variables
To view the current value of variables pagesize and long
show pagesize
show long
To set a value for variables pagesize and long
set pagesize 5000
set long 1000
How to execute select query and store output in a csv file in SQL plus
set markup csv on;
spool outputfile.csv
execute the select query
spool off;
SET SERVEROUTPUT ON
DECLARE
v_sql VARCHAR2(4000);
v_rowscn NUMBER;
BEGIN
FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'YOUR_SCHEMA_NAME') LOOP
BEGIN
v_sql := 'SELECT MAX(ORA_ROWSCN) FROM ' || t.table_name;
EXECUTE IMMEDIATE v_sql INTO v_rowscn;
DBMS_OUTPUT.PUT_LINE('Table: ' || t.table_name || ' | MAX(ORA_ROWSCN): ' || v_rowscn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error processing table: ' || t.table_name || ' - ' || SQLERRM);
END;
END LOOP;
END;
/
No comments:
Post a Comment