-- 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;