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;
Commands related to Data Guard monitoring
select client_process, process, thread#, sequence#, status from v$managed_standby where client_process='LGWR' or process='MRP0';
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