Oracle SQL

oracle.sql

-- connect as system
C:\>sqlplus / as sysdba

-- reset system password
ALTER USER system IDENTIFIED BY *password*;

-- unlock user
ALTER USER username ACCOUNT UNLOCK IDENTIFIED BY *password*;

-- create user/schema
CREATE USER username IDENTIFIED BY *password*;

-- grant privileges
GRANT ALL PRIVILEGES TO username;
GRANT CONNECT, RESOURCE TO username;

-- drop user
DROP USER username CASCADE;

-- change orace xe http port
--> http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html
SELECT dbms_xdb.gethttpport AS "http-port" FROM dual;

BEGIN
dbms_xdb.sethttpport('88');
END;

-- analyze table
ANALYZE TABLE table_name COMPUTE STATISTICS;

SELECT 'ANALYZE TABLE ' || TABLE_NAME || ' COMPUTE STATISTICS;' AS COMMAND
  FROM ALL_ALL_TABLES T
 WHERE T.OWNER = 'username'  
 ORDER BY TABLE_NAME

-- manage recycle bin
--> http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm
SELECT * FROM recyclebin;
SHOW recyclebin;

-- purge table
PURGE TABLE BIN$jsleilx392mk2=293$0;
PURGE RECYCLEBIN;

-- restore table
FLASHBACK TABLE table_name TO BEFORE DROP;

-- create database link
CREATE PUBLIC DATABASE LINK
    DBL_NAME
CONNECT TO
    remote_username
IDENTIFIED BY
    *remote_password*
USING 'tns_service_name';

-- select where condition with regular expression 
SELECT column_name
  FROM table_name
 WHERE NOT REGEXP_LIKE (column_name, '^[0-9\-](.*)');

-- session manage (connect as system privileges)

SELECT A.SID
     , A.SERIAL#
     , A.STATUS
     , A.USERNAME
     , A.OSUSER
     , A.MACHINE
     , A.PROGRAM
     , TO_CHAR(A.LOGON_TIME, 'YYYY-MM-DD HH24:mm:ss') AS LOGON_TIME
     , B.FIRST_LOAD_TIME
     , B.SQL_TEXT
  FROM V$SESSION A
     , V$SQLAREA B
 WHERE A.SQL_ADDRESS = B.ADDRESS;

 ------------------------------------------------------------------------------

SELECT S.SID
     , S.SERIAL#
     , S.INST_ID
     , S.STATUS
     , S.USERNAME
     , S.OSUSER
     , S.MACHINE
     , S.PROGRAM
     , 'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# ||
	   ','|| S.INST_ID || ''';' AS ALLTER_SESSION
   --, S.*
  FROM GV$SESSION S
 WHERE S.USERNAME = 'username';