Oracle queries

-- show tables
SELECT table_name FROM all_tables;
 
-- 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;
 
-- show table size
SELECT segment_name, bytes/1024/1024 MB FROM user_segments WHERE segment_type='TABLE';
 
-- get datapump directory name
SELECT * FROM all_directories;
 
-- resize column
ALTER TABLE FOO.BAR1 MODIFY "COLL1" VARCHAR2(100);
 
-- delete user with all objects
DROP USER foo cascade;
 
-- show users
SELECT * FROM dba_users;
 
-- get export / import directory
SELECT * FROM dba_directories WHERE DIRECTORY_NAME LIKE 'DATA_PUMP_DIR';
 
-- get invalid objects
SELECT 'ALTER ' || object_type || ' ' || owner || '.' || object_name || ' COMPILE;'
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
 
-- get scheduler jobs
SELECT owner, job_name, comments FROM dba_scheduler_jobs;
 
-- get characterset
SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
 
-- change charset
CONNECT sys/pass1234 AS sysdba;
shutdown IMMEDIATE;
startup restrict;
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
shutdown IMMEDIATE;
startup;
 
-- show parameter
show parameter;
show parameter processes
 
-- set parameter
ALTER system SET open_cursors=1024 scope=both;
ALTER SYSTEM SET PROCESSES=250 SCOPE=SPFILE;
 
-- allow to access application express from remote
exec DBMS_XDB.setListenerLocalAccess (l_access => FALSE);
 
--  returning string in uppercase alpha-numeric characters
SELECT DBMS_RANDOM.string('x', 32) FROM dual;