Oracle queries

-- output parameter
SET wrap off;
SET pagesize 0;
SET linesize 120;
column owner format a30;
 
-- show configuration
show parameters;
 
-- list users
SELECT username FROM all_users 
 
-- find constraint
SELECT *
FROM all_constraints
WHERE constraint_name = 'SYS_C00381400';
 
-- select a random row from table
SELECT column FROM 
   SELECT column FROM TABLE
   ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM = 1
 
-- update materialized view
BEGIN
   dbms_snapshot.refresh('SCHEMA.MATERIALIZED_VIEW_NAME'); 
END;
 
-- view scheduler jobs
SELECT * FROM user_scheduler_jobs
 
-- get lock on table (NOWAIT)
SELECT username,v$lock.sid,TRUNC(id1/POWER(2,16)) rbs,BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot,id2 seq,lmode,request
FROM v$lock, v$session
WHERE v$lock.TYPE = 'TX'
AND v$lock.sid = v$session.sid
 
-- set password
ALTER USER hr identified BY test
 
-- get next ID from sequence
SELECT FOO_SEQ1.NEXTVAL FROM dual;
 
-- subquery
WITH Bought AS (
SELECT *
FROM MY_TABLE
WHERE sku = '1234'
AND ICOUNT > 0
AND IPRICE > 0
AND TRUNC(IDATE, 'YEAR') = TO_DATE(' + year + ', 'YYYY')
AND ITAX NOT IN (5, 6)
)
SELECT PURCHASE.c - RETOURE.c
FROM
(SELECT SUM(menge) c FROM Bought WHERE AK IN ('K', 'U')) PURCHASE,
(SELECT SUM(menge) c FROM Bought WHERE AK IN ('R')) RETOURE
 
-- order alphanumeric
SELECT sku FROM MY_TABLE
ORDER BY regexp_replace(sku, '([[:digit:]])') NULLS FIRST,  TO_NUMBER(regexp_replace(sku, '([[:alpha:]]|[[:punct:]]|[[:blank:]])')) NULLS LAST
 
-- show database size by schema / user
SELECT owner, SUM(bytes)/1024/1024 mb FROM dba_segments GROUP BY owner ORDER BY 2 DESC;
 
-- get datapump directory name
SELECT * FROM all_directories;