밥돌이밥순이

	-----------------------------------------------------------------------------------
	--  계정에 C##  취소  
	--ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.
	-----------------------------------------------------------------------------------
	alter session set "_ORACLE_SCRIPT"=true;
	-----------------------------------------------------------------------------------
	--  사용자 계정 새성 
	-----------------------------------------------------------------------------------
	CREATE USER IF_DB IDENTIFIED BY oracle;
	-----------------------------------------------------------------------------------
	--  권한(DBA 권한 부여)
	-----------------------------------------------------------------------------------
	GRANT CONNECT, DBA, RESOURCE TO IF_DB; 
    select * from dba_users  where username ='IF_DB';
    -----------------------------------------------------------------------------------
	--  DB 패스워드 기간만료일 확인  UNLIMITED : 기간을 제한두깆 않음  
	-----------------------------------------------------------------------------------
    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
    SELECT * FROM SYS.dba_profiles 
    WHERE profile ='DEFAULT' AND resource_name ='PASSWORD_LIFE_TIME'; 
	-----------------------------------------------------------------------------------
	--  사용자 계정 삭제
	-----------------------------------------------------------------------------------
	select sid,serial#,username,status from v$session 
    where schemaname ='MEGAHUB';--session조회 
    alter system kill session '494,2536'; --session삭제 'SID,SERIAL#';
	alter session set "_ORACLE_SCRIPT"=true;
	drop user IF_DB cascade;

WHERE IN에 '1,2,3,4' ,로 구분 해서 조회 

SELECT TRIM(REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL)) AS C1 FROM DUAL
CONNECT BY INSTR('1,2,3', ',', 1, LEVEL - 1) > 0 ;

WHERE C1 IN (  
		 SELECT TRIM(REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL)) AS C1 FROM DUAL
		 CONNECT BY INSTR('1,2,3', ',', 1, LEVEL - 1) > 0 
		 )

DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE := 'SALESMAN';
    v_emp_refcur    SYS_REFCURSOR;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    emp_by_job(v_job, v_emp_refcur);
    LOOP
        FETCH v_emp_refcur INTO v_empno, v_ename;
        EXIT WHEN v_emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE v_emp_refcur;
END;