-------------------------------------------------------------------------------------------
Slide 42 - SQLNET.ORA demo

-------------------------------------------------------------------------------------------
Slide 76 - proxy cnx

CREATE USER mechid
IDENTIFIED BY "A1Ac9C81292FC1CF0b8A40#5F04C0A"
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 100M ON uwdata;

AUDIT CONNECT BY scott ON BEHALF OF mechid;

ALTER USER mechid GRANT CONNECT THROUGH scott;

SELECT * FROM sys.proxy_info$;

conn scott[MECHID]/tiger@pdbtest

sho user

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

SELECT sys_context('USERENV', 'CURRENT_USER') FROM dual;

SELECT sys_context('USERENV', 'PROXY_USER') FROM dual;

-------------------------------------------------------------------------------------------
Slide 77 - def pwds

SELECT d.con_id, d.username, u.account_status
FROM cdb_users_with_defpwd d, cdb_users u
WHERE d.username = u.username
AND u.account_status = 'OPEN'
ORDER BY 3,1, 2;

-------------------------------------------------------------------------------------------
Slide 91 - mem acc

CREATE TABLE credit_card (
ccno  VARCHAR2(19),
cname VARCHAR2(25));

INSERT /* memtest */ INTO credit_card
VALUES ('5123-4567-8901-2345', 'Dan Morgan');

SELECT sql_id, sql_fulltext
FROM v$sqlarea
WHERE sql_fulltext LIKE '%memtest%';

-------------------------------------------------------------------------------------------
Slide 92 - product user profile

INSERT INTO system.product_user_profile
(product, userid, attribute, scope, numeric_value, char_value, date_value, long_value)
VALUES
('SQL*Plus', 'SCOTT', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);

commit;

DELETE FROM system.product_user_profile WHERE userid = 'SCOTT';

commit;

-------------------------------------------------------------------------------------------
Slide 100 - write all code to file syst

CREATE TABLE uwclass.t (textcol CLOB);

DECLARE
 c CLOB;
 CURSOR scur IS
 SELECT text
 FROM dba_source
 WHERE rownum < 200001;
BEGIN
  EXECUTE IMMEDIATE 'truncate table uwclass.t';
  FOR srec IN scur LOOP
    c := c || srec.text;
  END LOOP;
  INSERT INTO uwclass.t VALUES (c);
  COMMIT;
END;
/

SELECT LENGTH(textcol) FROM uwclass.t;

set timing on
DECLARE
 buf CLOB;
BEGIN
  SELECT textcol
  INTO buf
  FROM uwclass.t
  WHERE rownum = 1;

  dbms_advisor.create_file(buf, 'CTEMP', 'testfile1.txt');
END;
/

-- open the file

-------------------------------------------------------------------------------------------
Slide 102 - external tables

CREATE OR REPLACE DIRECTORY bdump AS 'c:\app\oracle\diag\rdbms\orabase\orabase\trace\';

CREATE TABLE log_table (TEXT VARCHAR2(400))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY bdump
ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  NOBADFILE NODISCARDFILE NOLOGFILE
  FIELDS TERMINATED BY '0x0A'
  MISSING FIELD VALUES ARE NULL)
LOCATION ('alert_orabase.log'))
REJECT LIMIT unlimited;

SELECT * FROM log_table WHERE rownum < 1001;

-------------------------------------------------------------------------------------------
Slide 105 - UTL_INADDR

SELECT utl_inaddr.get_host_address('www.oracle.com') FROM dual;
SELECT utl_inaddr.get_host_address('www.umn.edu') FROM dual;

SELECT utl_inaddr.get_host_name('134.84.119.7') FROM dual;
SELECT utl_inaddr.get_host_name('134.84.119.8') FROM dual;
SELECT utl_inaddr.get_host_name('134.84.119.9') FROM dual;
SELECT utl_inaddr.get_host_name('134.84.119.10') FROM dual;

-------------------------------------------------------------------------------------------
Slide 108 - UTL_HTTP

DECLARE
req   utl_http.req;
resp  utl_http.resp;
value VARCHAR2(1024);
BEGIN
  req := utl_http.begin_request('http://www.morganslibrary.org');
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
resp := utl_http.get_response(req);
LOOP
utl_http.read_line(resp, value, TRUE);
dbms_output.put_line(value);
END LOOP;
utl_http.end_response(resp);
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(resp);
END;
/

-------------------------------------------------------------------------------------------
Slide 112 - Recyclebin

CREATE TABLE dropme (soc_sec_no VARCHAR2(11));

INSERT INTO dropme (soc_sec_no)
VALUES ('523-14-0963');
commit;

DROP TABLE dropme;

desc dropme

SELECT object_name, original_name, type, related, base_object
FROM user_recyclebin; 

SELECT * FROM "BIN$XQYTCXIjTw+aoyEM2bCFjw==$0";

FLASHBACK TABLE dropme TO BEFORE DROP;

desc dropme

SELECT * FROM dropme;

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