SQL Tips & Oracle Handy Snippets
An eclectic view of the world of Oracle — quick copy-paste answers to everyday DBA questions.
Contents
- #hidden-paramsShow Oracle hidden parameters
- #proxy-connectConnect as another user through SYSTEM (proxy)
- #spool-excelSpool to Excel (SQL*Plus markup)
- #negative-selectThoughts: “negative” SELECT
- #listaggGrouping with LISTAGG
- #ampersandsAmpersands in SQL*Plus input
- #datafile-infoQuick datafile info
- #datesHandling dates in Oracle
- #plsql-loopsPL/SQL loops: columns & row counts
- #joins-minus-intersectJOIN, MINUS, INTERSECT examples
- #pkg-funcPackage & function examples
- #create-table-procProcedure to create a table
- #srvctlsrvctl stop/start database
- #sys-privsSYS/role grants quick queries
- #rebuild-indexesRebuild unusable indexes (all)
- #creation-timeWhen was a tablespace/datafile created?
- #sysdba-authTesting SYS password vs. OS authentication
- #view-ddl-timesView creation & last DDL time
- #constraint-ddlDDL for a constraint (drop/recreate)
- #test-connectionTest connection (RAC scan script)
- #asm-quorumASM: put quorum disk online & verify
- #sqlplus-defineShow SQL*Plus variable value
- #dblinks-ddlDDL for DB LINKS (DBMS_METADATA)
- #proxy-quickProxy connect (quick)
- #backup-packageBackup a package from USER_SOURCE
- #drop-non-defaultDrop non-default users (19c & 11g)
- #proc-activitySee what a Unix process is doing
- #space-usageSpace usage (CDB/PDB & 11g)
- #gather-statsGather schema stats
Show Oracle hidden parameters
SELECT
ksppinm,
ksppstvl
FROM x$ksppi a, x$ksppsv b
WHERE a.indx = b.indx
AND ksppinm LIKE '_allow%';
-- Example output:
-- _allow_error_simulation FALSE
-- _allow_resetlogs_corruption FALSE
-- _allow_terminal_recovery_corruption FALSE
-- _allow_read_only_corruption FALSE
-- _allow_file_1_offline_error_1245 FALSE
-- ...etc
Connect as another user through SYSTEM (proxy)
ALTER USER DW_REP GRANT CONNECT THROUGH system;
-- Now login and proxy
-- SQL> CONNECT system[DW_REP]/password
-- USER is "DW_REP"
-- Revoke when finished:
ALTER USER DW_REP REVOKE CONNECT THROUGH system;
Spool to Excel (SQL*Plus markup)
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
SPOOL sample.xls;
SELECT * FROM emp;
SPOOL OFF;
Thoughts: “negative” SELECT
Example table TEST_TAB with columns col1..col5. To show all except col2 you’d write:
SELECT col1, col3, col4, col5
FROM test_tab;
It’d be neat if Oracle supported something like SELECT -col2 FROM test_tab; to exclude columns,
but that isn’t a feature today — explicit column lists are the way.
Grouping with LISTAGG
-- Sample data
-- CUSTOMER_ID TYPE_ID
-- 12345 1
-- 56789 2
-- 12345 2
-- 98765 3
-- 12345 3
SELECT
customer_id,
LISTAGG(type_id, ',') WITHIN GROUP (ORDER BY type_id) AS type_id
FROM customer
GROUP BY customer_id;
-- Result:
-- CUSTOMER_ID TYPE_ID
-- 12345 1,2,3
-- 56789 2
-- 98765 3
Ampersands in SQL*Plus input
Inserting text with & without prompts:
-- 1) Disable substitution
SET DEFINE OFF
-- 2) Change define character
SET DEFINE #
-- 3) Stop scanning entirely
SET SCAN OFF
-- 4) Escape ampersand for SQL*Plus input (ESCAPE sets the escape char)
SET ESCAPE \
INSERT INTO test_table VALUES('Salt \& Pepper');
-- 5) Use ASCII
INSERT INTO test_table VALUES('Salt '||CHR(38)||' Pepper');
-- Note: In HTML (Blogger), use & to render '&' as text inside code blocks.
Quick datafile info
SELECT
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 AS allocated_mb,
b.free_mb
FROM dba_data_files a
JOIN (
SELECT file_id, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space
GROUP BY file_id
) b ON a.file_id = b.file_id
ORDER BY a.tablespace_name;
Handling dates in Oracle
-- Session format
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY';
-- Or from UNIX:
-- export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
-- Query examples
SELECT * FROM datetest WHERE TO_DATE(startdate,'dd-mon-yyyy') < TO_DATE('21-FEB-2017');
INSERT INTO datetest (warrant, startdate, finishdate)
VALUES ('A12345','22-JUN-2015','22-DEC-2019');
COMMIT;
SELECT * FROM datetest WHERE TO_DATE(startdate,'dd-mon-yyyy') < TO_DATE('31-DEC-2015');
Procedure example selecting dates prior to a threshold:
-- Table sample:
-- WARRANT STARTDATE FINISHDATE
-- Y12345 22-JUN-15 22-DEC-19
-- A12345 22-DEC-17 24-DEC-17
-- X56789 10-JAN-16 11-FEB-16
-- Z12345 10-JAN-17 15-FEB-17
CREATE OR REPLACE PROCEDURE warrantlist IS
warrant_num VARCHAR2(10);
warrant_date DATE;
CURSOR d1 IS
SELECT DISTINCT warrant FROM datetest;
CURSOR d2 IS
SELECT MAX(startdate)
FROM datetest
WHERE warrant = warrant_num
AND startdate < TO_DATE('21-FEB-2016');
BEGIN
OPEN d1;
LOOP
FETCH d1 INTO warrant_num;
EXIT WHEN d1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Warrant : '|| warrant_num);
OPEN d2;
LOOP
FETCH d2 INTO warrant_date;
EXIT WHEN d2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Warrant date: '|| warrant_date);
END LOOP;
CLOSE d2;
END LOOP;
CLOSE d1;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END warrantlist;
/
PL/SQL loops: columns & row counts
Count columns per table for users ending with MAIN:
SET SERVEROUTPUT ON;
SET LINES 200;
DECLARE
v_app_user dba_users.username%TYPE;
v_object_name dba_objects.object_name%TYPE;
v_obj_name2 dba_objects.object_name%TYPE;
v_num NUMBER;
CURSOR a1 IS
SELECT username FROM dba_users WHERE username LIKE '%MAIN' ORDER BY 1;
CURSOR d1 IS
SELECT object_name FROM dba_objects
WHERE object_type = 'TABLE'
AND owner = v_app_user
ORDER BY 1;
CURSOR d2 IS
SELECT v_object_name, COUNT(a.table_name)
FROM dual, dba_tab_columns a
WHERE a.table_name = v_object_name
AND owner = v_app_user;
BEGIN
OPEN a1;
LOOP
FETCH a1 INTO v_app_user;
EXIT WHEN a1%NOTFOUND;
OPEN d1;
LOOP
FETCH d1 INTO v_object_name;
EXIT WHEN d1%NOTFOUND;
OPEN d2;
LOOP
FETCH d2 INTO v_obj_name2, v_num;
EXIT WHEN d2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RPAD(v_app_user,30) || RPAD(v_obj_name2,40) ||
' Columns : ' || RPAD(v_num,9));
END LOOP;
CLOSE d2;
END LOOP;
CLOSE d1;
END LOOP;
CLOSE a1;
END;
/
Variation: count rows in each table:
DECLARE
v_app_user dba_users.username%TYPE;
v_object_name dba_objects.object_name%TYPE;
vnum NUMBER;
CURSOR a1 IS
SELECT username FROM dba_users WHERE username LIKE '%MAIN' ORDER BY 1;
CURSOR d1 IS
SELECT object_name
FROM dba_objects
WHERE object_type = 'TABLE'
AND owner = v_app_user
ORDER BY 1;
BEGIN
OPEN a1;
LOOP
FETCH a1 INTO v_app_user;
EXIT WHEN a1%NOTFOUND;
OPEN d1;
LOOP
FETCH d1 INTO v_object_name;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_app_user ||'.'|| v_object_name INTO vnum;
DBMS_OUTPUT.PUT_LINE(RPAD(v_app_user,30)||' '||RPAD(v_object_name,30)||' Rows: '|| RPAD(vnum,9));
EXIT WHEN d1%NOTFOUND;
END LOOP;
CLOSE d1;
END LOOP;
CLOSE a1;
END;
/
Improved approach with one cursor:
DECLARE
vnum NUMBER;
CURSOR a1 IS
SELECT owner, table_name
FROM dba_tables
WHERE owner IN (SELECT username FROM dba_users WHERE username LIKE 'ENG%')
ORDER BY 2,1;
BEGIN
FOR a_row IN a1 LOOP
EXECUTE IMMEDIATE 'SELECT /*+ PARALLEL */ COUNT(*) FROM ' ||
a_row.owner ||'.'|| a_row.table_name
INTO vnum;
DBMS_OUTPUT.PUT_LINE(RPAD(a_row.owner,30)||' '||
RPAD(a_row.table_name,30)||' Rows: '|| RPAD(vnum,9));
END LOOP;
END;
/
Even better: use statistics (after DBMS_STATS):
-- Refresh stats first if needed:
-- EXEC DBMS_STATS.GATHER_SCHEMA_STATS('DWADM');
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_tables IS
SELECT owner, table_name, num_rows
FROM dba_tables
WHERE owner = 'DWADM'
ORDER BY table_name;
BEGIN
FOR t IN c_tables LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(t.owner, 30) || ' ' ||
RPAD(t.table_name, 30) || ' Rows: ' ||
NVL(t.num_rows, 0));
END LOOP;
END;
/
JOIN, MINUS, INTERSECT examples
-- Table A
-- COUNTRY_CODE COUNTRY
-- 1 England
-- 2 Scotland
-- 3 Wales
-- 4 Ireland
-- Table B
-- COUNTRY_CODE COUNTRY
-- 3 Wales
-- 4 Ireland
-- 5 France
-- 6 Italy
-- Outer join example
SELECT a.country, b.country
FROM a, b
WHERE a.country_code(+) = b.country_code;
-- MINUS
SELECT country FROM a
MINUS
SELECT country FROM b;
-- England, Scotland
-- INTERSECT
SELECT country FROM a
INTERSECT
SELECT country FROM b;
-- Ireland, Wales
-- INNER JOIN
SELECT a.country
FROM a
INNER JOIN b ON a.country_code = b.country_code;
-- Wales, Ireland
-- UNION ALL de-dup with DISTINCT/ORDER
SELECT DISTINCT country
FROM (
SELECT country FROM a
UNION ALL
SELECT country FROM b
) x
ORDER BY country;
-- England, France, Ireland, Italy, Scotland, Wales
Package & function examples
CREATE OR REPLACE PACKAGE test AS
FUNCTION getlengthWidth(len NUMBER, wid NUMBER) RETURN NUMBER;
PROCEDURE hello1;
PROCEDURE hello2;
END test;
/
CREATE OR REPLACE PACKAGE BODY test AS
FUNCTION getlengthWidth(len NUMBER, wid NUMBER) RETURN NUMBER AS
varea NUMBER;
BEGIN
varea := len * wid;
RETURN varea;
END getlengthWidth;
PROCEDURE hello1 IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello1');
END hello1;
PROCEDURE hello2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello2');
END hello2;
END test;
/
-- Source text:
SELECT text FROM dba_source WHERE name = 'HELLO1';
SELECT text FROM dba_source WHERE name = 'HELLO2';
-- Call
SELECT test.getlengthWidth(15,45) FROM dual; -- 675
SET SERVEROUTPUT ON;
EXEC test.hello1; -- Hello1
Standalone function:
CREATE OR REPLACE FUNCTION area
(len IN NUMBER, wid IN NUMBER)
RETURN NUMBER
AS
varea NUMBER;
BEGIN
varea := len * wid;
RETURN varea;
END;
/
SELECT area(10,30) AS area FROM dual; -- 300
Procedure to create a table
CREATE OR REPLACE PROCEDURE testtable IS
var1 VARCHAR2(4000);
BEGIN
var1 := 'CREATE TABLE testable (testnum NUMBER)';
EXECUTE IMMEDIATE var1;
DBMS_OUTPUT.PUT_LINE('Table testable created!!!');
END testtable;
/
srvctl stop/start database
srvctl stop database -d mbtpay
srvctl start database -d mbtpay
SYS/role grants quick queries
SELECT * FROM dba_sys_privs WHERE grantee = 'TEST';
-- Example:
-- GRANTEE PRIVILEGE ADM
-- TEST UNLIMITED TABLESPACE NO
SELECT * FROM dba_role_privs WHERE grantee = 'TEST';
-- Generate grants for roles like %MAIN%:
SELECT 'grant '|| role || ' to AUSER;'
FROM dba_roles
WHERE role LIKE '%MAIN%';
Rebuild unusable indexes (all)
SET SERVEROUTPUT ON
DECLARE
PROCEDURE ddl (str IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE str;
DBMS_OUTPUT.PUT_LINE(str);
END ddl;
BEGIN
FOR t IN (SELECT table_name FROM user_tables ORDER BY table_name) LOOP
FOR i IN (
SELECT index_name, partition_name, 'partition' AS ddl_type
FROM user_ind_partitions
WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = t.table_name)
AND status = 'UNUSABLE'
UNION ALL
SELECT index_name, subpartition_name, 'subpartition'
FROM user_ind_subpartitions
WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = t.table_name)
AND status = 'UNUSABLE'
UNION ALL
SELECT index_name, NULL, NULL
FROM user_indexes
WHERE table_name = t.table_name
AND status = 'UNUSABLE'
) LOOP
IF i.ddl_type IS NULL THEN
ddl('ALTER INDEX '||i.index_name||' REBUILD');
ELSE
ddl('ALTER INDEX '||i.index_name||' REBUILD '||i.ddl_type||' '||i.partition_name);
END IF;
END LOOP;
END LOOP;
END;
/
When was a tablespace/datafile created?
SELECT tablespace_name, name, creation_time
FROM v$datafile_header;
Testing SYS password vs. OS authentication
Local AS SYSDBA uses OS authentication, so any password “works”. To actually test the SYS password, use a network connection string:
-- Wrong password:
sqlplus sys/wrongpassword@oradb as sysdba
-- ORA-01017: invalid username/password; logon denied
-- Right password:
sqlplus sys/rightpassword@oradb1 as sysdba
-- Connected. SHOW USER; --> SYS
Background reading: https://www.thegeekdiary.com/why-can-i-login-as-sysdba-with-any-username-and-password/ Why can I login as SYSDBA with any username and password?
View creation & last DDL time (views)
SELECT o.object_name, o.created, o.last_ddl_time
FROM dba_objects o
WHERE o.object_name LIKE 'SERVICENOW%'
AND o.object_type = 'VIEW';
DDL for a constraint (drop/recreate)
-- Extract
SELECT DBMS_METADATA.get_ddl('CONSTRAINT', constraint_name, owner)
FROM dba_constraints
WHERE constraint_name = 'CC1_TXN_CHANNEL_MAP';
-- Drop and recreate (adding a value)
ALTER TABLE edw.txn_channel_map DROP CONSTRAINT cc1_txn_channel_map;
ALTER TABLE edw.txn_channel_map ADD CONSTRAINT cc1_txn_channel_map
CHECK (txn_source IN ('SalesOrder','SaleTxn','UseTxn','RefundOrder','PatronOrder'))
ENABLE;
Test connection (RAC scan script)
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
a=1
while [ $a -lt 20 ]; do
sqlplus -silent system/racattack@collabn-cluster-scan:1521/stockton.racattack << 'EOF'
set echo off
set heading off
set feedback off
SELECT host_name FROM gv\$instance
WHERE instance_number = userenv('instance');
-- SELECT UTL_INADDR.get_host_address FROM dual;
EXIT;
EOF
a=$(expr $a + 1)
sleep 1
done
ASM: put quorum disk online & verify
ALTER DISKGROUP NFPS_VOTE_DATA ONLINE QUORUM DISK NFPS_VOTE_DATA_B007;
-- Verify:
SET LINES 300
SET PAGES 66
COL asmdisk FORMAT a20
COL failgroup FORMAT a20
COL site_name FORMAT a10
COL path FORMAT a40
SELECT dg.group_number,
dg.name AS diskgroup,
d.name AS asmdisk,
d.total_mb,
d.state,
d.mount_status,
d.failgroup,
d.site_name,
d.header_status,
d.path
FROM v$asm_diskgroup dg
LEFT JOIN v$asm_disk d ON dg.group_number = d.group_number
ORDER BY 1,2,3;
Show SQL*Plus variable value
SET SERVEROUTPUT ON
SET LINES 200
DEFINE russo = 'SYSTEM';
SELECT * FROM dba_data_files WHERE tablespace_name = '&russo';
DEFINE russo
DDL for DB LINKS (DBMS_METADATA)
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0
SET LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);
END;
/
SELECT DBMS_METADATA.get_ddl('DB_LINK', db_link, owner)
FROM dba_db_links
WHERE owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'));
SET PAGESIZE 14 LINESIZE 1000 FEEDBACK ON VERIFY ON
Proxy connect (quick)
ALTER USER dw_rep GRANT CONNECT THROUGH system;
CONNECT system[DW_REP]/password
SHOW USER -- USER is "DW_REP"
ALTER USER dw_rep REVOKE CONNECT THROUGH system;
Backup a package from USER_SOURCE
SELECT
DECODE(type||'-'||TO_CHAR(line,'fm99999'),
'PACKAGE BODY-1', '/'||CHR(10), NULL) ||
DECODE(line, 1, 'create or replace ', '' ) ||
text AS text
FROM user_source
WHERE name = UPPER('frm_extract_sale_txn_payment');
Temporarily relax password verify & reset
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;
ALTER USER oggadmin IDENTIFIED BY oggadmin;
Note: Not for production — restore password verify afterwards.
Drop non-default users (19c & 11g)
-- 19c+: exclude Oracle-maintained accounts
SELECT 'drop user '||username||' cascade;'
FROM dba_users
WHERE username NOT IN (
SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
);
-- Pre-11g: non-Oracle users created after SYS
SELECT username, profile
FROM dba_users
WHERE TRUNC(created) > (
SELECT TRUNC(created) FROM dba_users WHERE username='SYS'
)
ORDER BY 1;
-- 11g workaround to exclude Oracle-maintained users
SELECT username FROM dba_users
WHERE username NOT IN (SELECT name FROM system.logstdby$skip_support)
ORDER BY 1;
-- 11g list of Oracle-maintained users
SELECT username FROM dba_users
WHERE username IN (SELECT name FROM system.logstdby$skip_support)
ORDER BY 1;
See what a Unix process is doing
SET LINES 200
COLUMN Client_User FORMAT a10
COLUMN DB_User FORMAT a10
COLUMN MACHINE FORMAT a30
SELECT s.status AS "Status",
s.type AS "Type",
s.username AS "DB_User",
s.osuser AS "Client_User",
s.server AS "Server",
s.machine AS "Machine",
s.module AS "Module",
s.logon_time AS "Connect Time",
s.process AS "Process",
p.spid,
p.pid,
s.sid,
s.audsid,
SYSDATE - (s.last_call_et / 86400) AS "Last_Call"
FROM v$session s
JOIN v$process p ON s.paddr = p.addr(+)
WHERE spid = &1;
Space usage (CDB/PDB & 11g)
All DBs (based on dba_tablespace_usage_metrics):
SELECT m.tablespace_name,
ROUND(MAX(m.tablespace_size*t.block_size/1024/1024),2) AS tol_metrics,
ROUND(SUM(d.maxbytes/1024/1024),2) AS max_size,
ROUND((SUM(d.bytes)*COUNT(DISTINCT d.file_id))/COUNT(d.file_id)/1024/1024,2) AS tol_alloc,
ROUND(MAX(m.used_space*t.block_size/1024/1024),2) AS used,
ROUND(MAX(m.used_space*t.block_size)*100/(SUM(d.bytes)*COUNT(DISTINCT d.file_id)/COUNT(d.file_id)),2) AS perc_alloc,
ROUND(MAX(m.used_percent),2) AS perc_max,
ROUND(((SUM(d.bytes)*COUNT(DISTINCT d.file_id))/COUNT(d.file_id)-MAX(m.used_space*t.block_size))/1024/1024,2) AS free_alloc,
ROUND(MAX((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) AS free_max
FROM dba_tablespace_usage_metrics m,
dba_tablespaces t,
dba_data_files d
WHERE m.tablespace_name = t.tablespace_name
AND d.tablespace_name = t.tablespace_name
GROUP BY m.tablespace_name
ORDER BY 7 DESC;
CDB/PDB variant:
SET LINES 200
COL pdb_name FORMAT a20
SET PAGES 66
BREAK ON pdb_name
SELECT m.tablespace_name,
n.name AS pdb_name,
ROUND(MAX(m.tablespace_size*t.block_size/1024/1024),2) AS tol_metrics,
ROUND(SUM(d.maxbytes/1024/1024),2) AS max_size,
ROUND((SUM(d.bytes)*COUNT(DISTINCT d.file_id))/COUNT(d.file_id)/1024/1024,2) AS tol_alloc,
ROUND(MAX(m.used_space*t.block_size/1024/1024),2) AS used,
ROUND(MAX(m.used_space*t.block_size)*100/(SUM(d.bytes)*COUNT(DISTINCT d.file_id)/COUNT(d.file_id)),2) AS perc_alloc,
ROUND(MAX(m.used_percent),2) AS perc_max,
ROUND(((SUM(d.bytes)*COUNT(DISTINCT d.file_id))/COUNT(d.file_id)-MAX(m.used_space*t.block_size))/1024/1024,2) AS free_alloc,
ROUND(MAX((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) AS free_max
FROM cdb_tablespace_usage_metrics m,
v$pdbs n,
cdb_tablespaces t,
cdb_data_files d
WHERE m.tablespace_name = t.tablespace_name
AND m.con_id = n.con_id
AND d.tablespace_name = t.tablespace_name
GROUP BY m.tablespace_name, n.name
ORDER BY 2,7 DESC;
11g‑style (no usage_metrics):
COLUMN "Tablespace" FORMAT a13
COLUMN "Used MB" FORMAT 99,999,999
COLUMN "Free MB" FORMAT 99,999,999
COLUMN "Total MB" FORMAT 99,999,999
SELECT
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
ROUND(100 * (fs.freespace / df.totalspace)) "Pct. Free"
FROM
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) freespace
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;
Gather schema stats
EXEC dbms_stats.gather_schema_stats('testschema', estimate_percent => 100, degree => 8);
No comments:
Post a Comment