티스토리 뷰

DataBase/Oracle

스크립트파일

디비자 2012. 10. 17. 11:47
반응형


oracle_관련_모음.xlsx



 범주 설명 파일명 스크립트 본문
 find view  fv select view_name from dba_views
where view_name like upper('%&KEY%')
 find constraint columns cons_col select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME
from DBA_CONS_COLUMNS
where owner='&OWNER'
 find constraint  cons select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME
from dba_constraints
where owner='&OWNER'
권한 보기 sPriv select * from session_privs
권한 찾기 sPrivs select * from session_privs
where privilege like upper('%&privilege%')
 flashback version query fvq  select versions_starttime,versions_endtime, loc
from dept versions between timestamp
get fvq 해서 수정하면서 사용할껏 minvalue and maxvalue
where deptno=40
order by versions_endtime
 rollup select empno,e.ename,e.job,e.mgr, r.sumsal
from emp e right outer join(
select deptno,empno,sum(sal) sumsal from emp group by rollup(deptno,empno)) r
using(empno)
order by r.deptno,empno
 cube 는 rollup 대신 적어주면 됨.  cube
 timezone 보는 스크립트 tzn select tzname,tz_offset(tzname) from v$timezone_names
where upper(tzname) like '%&TimeZoneName%'
트리구조 tree SELECT LPAD (ename, LENGTH(ename)+(level*2)-2,'_') AS org_chart
FROM emp
START with ename='KING'
CONNECT BY PRIOR empno=mgr
parameter 찾기 p col name FORM a20
col value FORM a60
select name, value
from V_$PARAMETER
where name like lower('%&KEY%')
/
 data format 변경 n  alter session set nls_date_format='yy-mm-dd hh24:mi:ss'
/
 DB 상태 확인 s select status from v$instance;
 테이블명으로 datafile 찾기 datafileByTable col fileName form a40;
select df.name fileName, df.file#, ts#, ts.name
from v$datafile df
     join v$tablespace ts using(ts#)
     join dba_tables dt on(dt.tablespace_name=ts.name)
where dt.owner='&Owner' and dt.table_name='&tableName'
테이블 사이즈 보는 스크립트 tableSize select owner,segment_name,blocks*
(select value
from V_$PARAMETER
where name='db_block_size')/1024 KB
from dba_segments
where owner='&Owner' and segment_name='&tableName'
/
 T/S로 datafile 리스트 보는 스크립트  dfList col tablespaceName form a10;
col datafileName form a80;
select ts.name tablespaceName,df.NAME datafileName
from v$datafile df join v$tablespace ts using(ts#)
where ts.name like('%&timespace_name%')
/
 T/S size 보는 스크립트  tsSize set verify OFF
col "size" form a50;
select
    'TableSpace &&tabkespace_name''s size is ' ||
    sum(maxbytes+decode(autoextensible, 'NO',bytes,0))/1024/1024 "MB"
from dba_data_files
where TABLESPACE_NAME=upper('&tablespace_name')
/
undifine tablespace_name;
createUser CREATE USER U1
        PROFILE DEFAULT
        IDENTIFIED BY "1"
        DEFAULT TABLESPACE EXAMPLE
        QUOTA 10 M ON EXAMPLE
        ACCOUNT UNLOCK
/
GRANT CONNECT TO U1
/
 Role  sample  roleTest  drop roles HR_CLERK;
 drop roles HR_MGR;
(Admin1 6-18P)  drop user david;
 drop user rachel;
 drop user jenny;
 create roles HR_CLERK not identified;
 grant select on hr.employees to HR_CLERK;
 grant update on hr.employees to HR_CLERK:
 create roles HR_MGR not identified;
 grant delete on hr.employees to HR_MGR;
 grant insert on hr.employees to HR_MGR;
 grant HR_CLERK to HR_MGR;
 create user jenny identified by "1";
 create user david identified by "1";
 create user rachel identified by "1";
 grant HR_CLERK to david;
 grant HR_CLERK to rachel;
 grant HR_MGR to jenny;
 find object  fo col object format a30
select owner||'.'||object_name object, object_type
from dba_objects
where owner like upper('%&Owner%')
        and object_name like upper('%&ObjectName%')
/
 find package  fp   select distinct object_name from user_procedures where object_name like '%&key%'
 tablespace 사용량 보기  tsUsage2  select name, "Used MB", "File Used MB" from v$tablespace join
(select tsn, sum(BYTES/1024/1024) "Used MB", sum(blocks) "B NUMBER"
        from TBS_SPACE_USAGE group by tsn) tu
on(TS#=TSN) join
        (select ts#,sum(bytes)/1024/1024 "File Used MB"
                from v$datafile group by ts#) df
on(tu.tsn=df.ts#)
/





 범주설명파일명스크립트 본문
 find view fvselect view_name from dba_views
where view_name like upper('%&KEY%')
 find constraint columnscons_colselect CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME
from DBA_CONS_COLUMNS
where owner='&OWNER'
 find constraint consselect CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME
from dba_constraints
where owner='&OWNER'
권한 보기sPrivselect * from session_privs
권한 찾기sPrivs

select * from session_privs

where privilege like upper('%&privilege%')

 flashback version query

get fvq 해서 수정하면서 사용할껏

fvq_dept

col versions_starttime form a22

col versions_endtime form a22

select versions_starttime,versions_endtime, dept.*

from dept versions between timestamp
minvalue and maxvalue
order by deptno,versions_endtime

/

 rollupselect empno,e.ename,e.job,e.mgr, r.sumsal
from emp e right outer join(
select deptno,empno,sum(sal) sumsal from emp group by rollup(deptno,empno)) r
using(empno)
order by r.deptno,empno
 cube 는 rollup 대신 적어주면 됨. cube
 timezone 보는 스크립트tznselect tzname,tz_offset(tzname) from v$timezone_names
where upper(tzname) like '%&TimeZoneName%'
트리구조treeSELECT LPAD (ename, LENGTH(ename)+(level*2)-2,'_') AS org_chart
FROM emp
START with ename='KING'
CONNECT BY PRIOR empno=mgr
parameter 찾기pcol name FORM a20
col value FORM a60
select name, value
from V_$PARAMETER
where name like lower('%&KEY%')
/
 data format 변경n alter session set nls_date_format='yy-mm-dd hh24:mi:ss'
/
 DB 상태 확인sselect status from v$instance;
 테이블명으로 datafile 찾기datafileByTablecol fileName form a40;
select df.name fileName, df.file#, ts#, ts.name
from v$datafile df
     join v$tablespace ts using(ts#)
     join dba_tables dt on(dt.tablespace_name=ts.name)
where dt.owner='&Owner' and dt.table_name='&tableName'
테이블 사이즈 보는 스크립트tableSizeselect owner,segment_name,blocks*
(select value
from V_$PARAMETER
where name='db_block_size')/1024 KB
from dba_segments
where owner='&Owner' and segment_name='&tableName'
/
 T/S로 datafile 리스트 보는 스크립트 dfListcol tablespaceName form a10;
col datafileName form a80;
select ts.name tablespaceName,df.NAME datafileName
from v$datafile df join v$tablespace ts using(ts#)
where ts.name like('%&timespace_name%')
/
 T/S size 보는 스크립트 tsSizeset verify OFF
col "size" form a50;
select
    'TableSpace &&tabkespace_name''s size is ' ||
    sum(maxbytes+decode(autoextensible, 'NO',bytes,0))/1024/1024 "MB"
from dba_data_files
where TABLESPACE_NAME=upper('&tablespace_name')
/
undifine tablespace_name;
createUser

CREATE USER U1

        PROFILE DEFAULT

        IDENTIFIED BY "1"

        DEFAULT TABLESPACE EXAMPLE

        QUOTA 10 M ON EXAMPLE

        ACCOUNT UNLOCK

/

GRANT CONNECT TO U1

/

 Role  sample

(Admin1 6-18P)

 roleTest drop roles HR_CLERK;
 drop roles HR_MGR;
 drop user david;
 drop user rachel;
 drop user jenny;
 create roles HR_CLERK not identified;
 grant select on hr.employees to HR_CLERK;
 grant update on hr.employees to HR_CLERK:
 create roles HR_MGR not identified;
 grant delete on hr.employees to HR_MGR;
 grant insert on hr.employees to HR_MGR;
 grant HR_CLERK to HR_MGR;
 create user jenny identified by "1";
 create user david identified by "1";
 create user rachel identified by "1";
 grant HR_CLERK to david;
 grant HR_CLERK to rachel;
 grant HR_MGR to jenny;
find object focol object format a30
select owner||'.'||object_name object, object_type
from dba_objects
where owner like upper('%&Owner%')
        and object_name like upper('%&ObjectName%')
/
 find package fp  select distinct object_name from user_procedures where object_name like '%&key%'
 tablespace 사용량 보기 tsUsage2 select name, "Used MB", "File Used MB" from v$tablespace join
(select tsn, sum(BYTES/1024/1024) "Used MB", sum(blocks) "B NUMBER"
        from TBS_SPACE_USAGE group by tsn) tu
on(TS#=TSN) join
        (select ts#,sum(bytes)/1024/1024 "File Used MB"
                from v$datafile group by ts#) df
on(tu.tsn=df.ts#)
/
 session 보기 session col machine FORM a20;
col username FORM a20;
select username,machine,sid,serial# from v$session
      where username not in ('SYS','SYSTEM','SYSMAN','DBSNMP','MDSYS','DMSYS','MGMT_VIEW')
/
 접속한 유저 죽이기 killAll begin
 for s in (select * from v$session
   where username not in ('SYS','SYSTEM','DBSNMP','MDSYS','DMSYS','MGMT_VIEW')) loop
  EXECUTE IMMEDIATE 'alter system kill session '''||s.sid||','||s.serial#||''' immediate';
 end loop;
END;
/
 database link 만들기 dbLink conn / as sysdba
grant create database link to scott;
conn scott/tiger
create database link lec
               connect to scott identified by tiger
               using 'LEC';
select * from emp@lec
/
 shared server로 parameter값 바꾸기 setSharedServer

alter system set DISPATCHERS='' scope=both;
alter system set DISPATCHERS="(PROTOCOL=TCP)(SERVICE=orcl)(dispatchers=3)"
 scope=both;
alter system set MAX_DISPATCHERS=3 scope=both;
alter system set shared_servers=10 scope=both;
alter system set MAX_shared_servers=20 scope=both;
alter system set circuits=300 scope=both;
alter system set shared_server_sessions=20 scope=both;


 snapshot 생성exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
baseline 생성, 확인

exec  dbms_workload_repository.create_baseline(63,64,'TEST_BASE');

 

select * from dba_hist_baseline;

 AWR 생성 주기 변경

begin

     DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440*7,60*1);

end;

 AWR 생성주기 변경 확인 select TO_CHAR(retention), TO_CHAR(snap_interval), most_recent_snap_time from sys.wrm$_wr_control
row 갯수 많은 거 찾기topRows

 select * from (

select owner,table_name,num_rows from dba_tables

where num_rows is not null order by num_rows desc)

where rownum <10

/

 Archive mode 끄고 켜기 archiveMode.sh
fra

select name, value

    from V_$PARAMETER

    where name like lower('db_recovery_file%')

/


select FILE_TYPE,PERCENT_SPACE_USED,NUMBER_OF_FILES

    from V_$FLASH_RECOVERY_AREA_USAGE

union all

select 'Total==>',sum(PERCENT_SPACE_USED),null

    from V_$FLASH_RECOVERY_AREA_USAGE

/

fra2

var s number;

begin

select value into :s

    from V_$PARAMETER

    where name like lower('db_recovery_file_dest_size');

end;

/


select FILE_TYPE,PERCENT_SPACE_USED/100*:s/1024/1024 "MB",NUMBER_OF_FILES

    from V_$FLASH_RECOVERY_AREA_USAGE

union all

select 'Total==>',sum(PERCENT_SPACE_USED/100*:s/1024/1024),null

    from V_$FLASH_RECOVERY_AREA_USAGE

/

에러난 파일 찾아주는errFile

select name, error

from v$datafile

join v$recover_file

using (file#)

/

 Flashback version query fvqcol versions_starttime form a22
col versions_endtime form a22
select versions_starttime,versions_endtime,emp.*
from scott.emp versions between timestamp
minvalue and maxvalue
where not(versions_endtime is null and versions_starttime is null)
order by empno,versions_endtime
/
 문자 변환. iconv -f eucKR -t utf8 hangul.txt > hangul_u.txt

var s number;

begin

select value into :s

    from V_$PARAMETER

    where name like lower('db_recovery_file_dest_size');

end;

/


select FILE_TYPE,PERCENT_SPACE_USED/100*:s/1024/1024 "MB",NUMBER_OF_FILES

    from V_$FLASH_RECOVERY_AREA_USAGE

union all

select 'Total==>',sum(PERCENT_SPACE_USED/100*:s/1024/1024),null

    from V_$FLASH_RECOVERY_AREA_USAGE

/


반응형
저작자표시

'DataBase > Oracle' 카테고리의 다른 글

Admin 1. 03장 Creating an Oracle Instance  (0) 2012.10.18
PL/SQL 예제 리스트  (0) 2012.10.17
oracle user 생성  (0) 2012.10.17
oracle service 등록  (0) 2012.10.17
오라클 스크립트 oracle.sh  (0) 2012.10.17