Notes on Oracle DB XE

SELECT username, privilege FROM USER_SYS_PRIVS;
SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS;
select user from dual;
select sys_context( 'userenv', 'current_schema' ) from dual; 
select * from all_users;

-- drop user
drop user soademo_01 cascade;

-- Create user in XE
create user soademo_01 identified by "soademo_01"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
grant dba, resource, connect to soademo_01;
ALTER USER soademo_01 DEFAULT ROLE "CONNECT",
                                  DBA,
                                  "RESOURCE";
								  
connect soademo_01/soademo_01; -- doesnt work in sql gui jdbc


-- northwind in oracle
-- http://blog.i-m-code.com/2013/03/28/northwind-for-oracle/
-- https://gist.github.com/segilbert/5095533

-- access to HR sample schema in XE
ALTER USER HR ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY HR; -- to pass the expired account
CONNECT HR/HR -- doesnt work in sql gui jdbc

#http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#05_05
#How do I connect as SYSDBA or SYSOPER?
#Properties props = new Properties(); props.put("user", "scott"); props.put("password", "tiger"); props.put("internal_logon", "sysoper");

#see http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html
#see all the variables related to language in oracle client jdbc
SELECT * FROM V$NLS_PARAMETERS
SELECT * FROM NLS_SESSION_PARAMETERS;
SELECT USERENV ('language') FROM DUAL;

#modify params of session because to_date('08-DEC-48','DD-MON-RR') was giving me errors
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American' -- <- here without semicolon!

#sqldeveloper change idiom of the gui
#http://stackoverflow.com/questions/7768313/how-can-i-change-the-language-to-english-in-oracle-sql-developer
#http://stackoverflow.com/questions/2333994/how-to-avoid-variable-substitution-in-oracle-sql-developer-with-trinidad-toba

#Shows the current schema
select sys_context( 'userenv', 'current_schema' ) from dual; 

#List all the tables accessibles for the user.
SELECT * FROM dba_tables where table_name like '%CATALOGO%';

#List all the synonyms accessible for the user.
select * from all_synonyms where table_name like 'T%'