'쿼리'에 해당되는 글 4건

[테이블 생성]

table name : calendar

calendar_seq(int) 
date(varchar)
day(varchar)

[yyyymmdd, 요일의 데이터 삽입]
(날짜에 대한 데이터 삽입 공간을 위해서 의미없는 조인과 함께 공간은 만든 후, between 을 통한 날짜를 넣는다.)

INSERT INTO calendar

(calendar_seq, date, day) 

select rnum as calendar_seq,

       date_format((date_add('20050101', interval rnum - 1 day)),'%Y%m%d') as date, 

       dayname(date_format((date_add('20050101', interval rnum - 1 day)),'%Y%m%d')) as day

  from (

        select @rnum := @rnum + 1 as rnum

          from (

                select @rnum := 0 

               ) a, 아무개 table , 아무개 table, 아무개 table  
       ) c

 where date_format((date_add('20050101', interval rnum - 1 day)),'%Y%m%d') between '20100101' and '20151231';

[select 달력 뷰] 

             select max(if(a.day = 'sunday', date_format(a.date, '%d'), '')) as 일, 

                      max(if(a.day = 'monday', date_format(a.date, '%d'), '')) as 월,

                      max(if(a.day = 'tuesday', date_format(a.date, '%d'), '')) as 화,

                      max(if(a.day = 'wednesday', date_format(a.date, '%d'), '')) as 수,

                      max(if(a.day = 'thursday', date_format(a.date, '%d'), '')) as 목,

                      max(if(a.day = 'friday', date_format(a.date, '%d'), '')) as 금,

                      max(if(a.day = 'saturday', date_format(a.date, '%d'), '')) as 토

               from calendar a

             where a.date like '201103%'

              group by WEEK(a.date,0)

#  실무에 적용 해본바는 없고 -효용성에 대해서도 잘 모르지만-
   회사에서 내준 숙제(?)이기에 만들어진 SQL 야매 달력뷰

begin 
         FOR cur IN (SELECT OBJECT_NAME, OBJECT_TYPE
           FROM sys.USER_objects 
           WHERE  status = 'INVALID' ) LOOP 
          BEGIN
         if cur.OBJECT_TYPE = 'PACKAGE BODY' then 
          EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' ||  cur.OBJECT_NAME || '" compile body'; 
         else 
          EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.OBJECT_NAME || '" compile'; 
         end if; 
EXCEPTION
   WHEN OTHERS THEN NULL; 
END;
         end loop; 
         end;
         

SELECT
  TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24') "NEXT_TIME"
, COUNT(DISTINCT NAME) "TOTAL_ARC_COUNT"
, ROUND(SUM(DISTINCT BLOCKS*BLOCK_SIZE)/1024/1024/1024,2) "TOTAL_SIZE_GB"
, SUM(CASE WHEN INST_ID = 1 AND THREAD# = 1 THEN 1 ELSE 0 END) "RAC1"
, SUM(CASE WHEN INST_ID = 2 AND THREAD# = 2 THEN 1 ELSE 0 END) "RAC2"
, SUM(CASE WHEN INST_ID = 3 AND THREAD# = 3 THEN 1 ELSE 0 END) "RAC3"
, SUM(CASE WHEN INST_ID = 4 AND THREAD# = 4 THEN 1 ELSE 0 END) "RAC4"
FROM GV$ARCHIVED_LOG
WHERE NEXT_TIME >= TRUNC(SYSDATE) -10
GROUP BY TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24')
ORDER BY 1;



select * 
  from GV$ARCHIVED_LOG;
  
  
  select * from v$recovery_file_dest;
  
  select * from V$ARCHIVED_LOG;
  
  select * from v$archive;
  
  select * from v$log;
  
  select * from v$archive_dest;
  
  select * from v$archive_processes;
 
 select dest_name, name, sequence#, b.status
  from v$archive_dest a, v$archived_log b
  where a.dest_id = b.dest_id;

--뷰 상태 리스트
SELECT OBJECT_NAME
     , STATUS
     , B.COMMENTS
  FROM (SELECT A.*, 'NWISDBA' AS OWNER
          FROM USER_OBJECTS A
         WHERE OBJECT_TYPE = 'VIEW'
           AND STATUS = 'INVALID'
        ) A
     , ALL_TAB_COMMENTS B  
 WHERE A.OWNER = B.OWNER(+)  
   AND A.OBJECT_NAME = B.TABLE_NAME(+)  
 ORDER BY OBJECT_NAME;
-- WHERE OBJECT_NAME LIKE 'TB_ATTEND%' AND OBJECT_TYPE='TABLE'; 


oracle 각종 조회 쿼리
1. 사용자와 롤에 부여된 시스템 권한을 조회
SELECT * FROM USER_SYS_PRIVS; 

2. 현재 세션에서 사용자와 롤에 부여된 시스템 권한을 조회.
SELECT * FROM SESSION_PRIVS; 

3.. 내가 다른 사용자에게 부여한 객체 권한을 조회.
SELECT * FROM USER_TAB_PRIVS_MADE; 

4. 나에게 부여된 객체 권한, 객체 이름을 조회.
SELECT * FROM USER_TAB_PRIVS_RECD; 

5. 내가 다른 사용자에게 부여한 칼럼에 대한 객체 권한과 칼럼 이름을 조회.
SELECT * FROM USER_COL_PRIVS_MADE; 

6. 나에게 부여된 칼럼에 대한 객체 권한과 칼럼 이름을 조회.
SELECT * FROM USER_COL_PRIVS_RECD;

7. 롤에 부여한 시스템 권한 조회
SELECT * FORM ROLE_SYS_PRIVS; 

8. 롤에 부여한 객체 권한 조회
SELECT * FROM ROLE_TAB_PRIVS; 

9. 사용자가 부여받은 롤 조회
SELECT * FROM USER_ROLE_PRIVS; 

10. USER_데이터 딕셔너리 뷰 조회
SELECT TABLE_NAME FROM USER_TABLES; 

11. ALL_데이터 딕셔너리 뷰의 조회
SELECT OWNER, TABLE_NAME FROM ALL_TABLES; 

12. DBA_딕셔너리 뷰의 조회
SELECT OWNER, TABLE_NAME FROM DBA_TABLES; 

13. USER_TABLE 조회
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;
SELECT TABLE_NAME, TABLE_SPACE, MIN_EXTENTS, MAX_EXTENTS 
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'ADD%'; //이런식을 테이블 이름을 넣고

 

14.USER_OBJECTS 조회
SELECT OBJECT_NAEM, OBJECT_TYPE, CREATED
FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE 'TB_ATTEND%' AND OBJECT_TYPE='TABLE'; 

15. USER_CATALOG 조회
SELECT * FROM USER_CATALOG; 

16. USER_CONSTRAINTS 조회
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('TB_CLASS', 'TB_SUGANG');   

17. 인덱스 정보 조회
SELECT INDEX_NAME, UNIQUENESS 
FROM USER_INDEXES
WHERE TABLE_NAME='TBSTUDENT';   

18. 뷰의 확인
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS; 

19. redolog 조회
select group#,members, bytes/10241024||’M’ from v$log;      

20. redolog 위치 조회
 select member from v$logfile;    

21. 컨트롤 파일 파라미터 조회
show parameter control_files;
select * from v$controlfile; 

22. 동적으로 변경 가능한 파라미터 조회
SELECT
v.name,
v.value,
DECODE(ISSES_MODIFIABLE, 'IMMEDIATE', 'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') AS SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO', 'DEFERRED', 'NO','YES') AS SYS_MODIFIABLE,
v.description
FROM V$PARAMETER v WHERE name not like 'nls%'
ORDER BY 1; 

23. 딕셔너리 뷰의 조회
SELECT * FROM DICT_COLUMNS WHERE TABLE_NAME LIKE '%INDEX%';
SELECT * FROM dict WHERE table_name LIKE UPPER('%&데이타사전%');

24. 세그먼트 조회
select segment_name, tablespace_name, segment_ID, FILE_ID from dba_rollback_segs; 

25. 블럭 조회
SHOW PARAMETER BLOCK; 

26. shared_pool_size 조회
SHOW PARAMETER SHARED 

27. 인스턴스 조회
select instance_name, host_name from v$instance; 

28. 버퍼 조회
show parameter buffer; 

29. 데이타 파일 조회
SELECT NAME,BYTES FROM V$DATAFILE;

30. DBA 데이타 파일 조회
SELECT FILE_NAME, TABLESPACE_NAME  FROM DBA_DATA_FILES; 

31. SORT 영역 크기 조회
SHOW PARAMETER SORT_AREA_SIZE; 

32. 아카이브 확인
archive log list;
SELECT * FROM V$DATABASE; (시스템이 아카이브인지 확인할 수 있다) 

33. 인덱스 블럭에 delete 된 entry 조회
 > analyze index 'index_name' validate structure 로 분석 후에
 > select  LF_ROWS,
            DEL_LF_ROWS,
            DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED, 
    from index_stats 
    where NAME= :p_index; 

34. 딕셔너리캐시 히트율 조회
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" 
from v$rowcache;  

35. 버퍼캐시 히트율 조회
select round(((1-(sum(decode(name, 'physical reads', value,0))/ 
(sum(decode(name, 'db block gets', value,0))+ 
(sum(decode(name, 'consistent gets', value, 0))))))*100),2) 
|| '%' "Buffer Cache Hit Ratio" 
from v$sysstat;

 36. 라이브러리캐시히트율 조회
select namespace, sum(gets) gets, sum(gethits) gethits,
       round(sum(gethits)/sum(decode(gets,0,0.0001,gets)),3)
       "Get Hit Ratio",
       sum(pins) pins, sum(pinhits) pinhits, 
       round(sum(pinhits)/sum(decode(pins,0,0.0001,pins)),3)
       "Pin Hit Ratio",
       sum(invalidations) invalidations
  from v$librarycache
group by namespace;    

37. 설치된 옵션 조회
SELECT * FROM V$OPTION;

38. Tablespace 장애 확인
select h.tablespace_name, d.name, h.error
from v$datafile d, v$datafile_header h

where d.file#=h.file#;

참조**********

구분 테이블명 설명 
오브젝트 USER_OBJECTS (OBJ) 모든 오브젝트에 대한 정보를 지원 오즈젝트 유형, 작성시간, 오브젝트에 사용된 최종 DDL 명령, alter, grant 및 revoke 등 
테이블 USER_TABLES (TABS) 테이블에 대한 정보 
열 USER_TAB_COLUMNS (COLS) 컬럼에 대한 정보 
뷰 USER_VIEWS 뷰에 대한 정보 
동의어 USER_SYNONYMS (SYN) 
시퀀스 USER_SEQUENCES (SEQ) 
제약조건 USER_CONSTARINTS   
제약조건열 USER_CONS_COLUMNS 제약 조건을 가진 열에 대한 정보 
제약조건의 예외사항 EXCEPTIONS 제약조건을 활성화시 에러사항에 대한 정보 
테이블 주석 USER_TAB_COMMENTS 테이블/뷰에 대한 주석 
열 주석 USER_COL_COMMENTS 열에 대한 주석 
인덱스 USER_INDEXES (IND) ( 인덱스에 관한 정보) 
인덱스 열 USER_IND_COLUMNS 인덱스열에 대한 정보 
클러스터 USER_CLUSTERS (CLU) 클러스터와 관련된 정보 
데이터베이스 링크 USER_DB_LINKS 링크에 관련된 정보 
스냅샷 USER_SNAPSHOTS   
스냅샷 로그 USER_SNAPSHOT_LOGS   
트리거 USER_TRIGGERS   
프로시저, 함수 및 패키지 USER_SOURCE   
코드 오류 USER_ERRORS   
테이블스페이스 USER_TABLESPACES   
영역 할당량 USER_TS_QUOTAS 테이블스레이스 단위로 사용자가 이용할 수 있는 영역의 최대크기와 할당된 영역의 크기 파악에 대한 정보 
세그먼트와 익스텐트 USER_SEGMENTS 
USER_EXTENTS   
여유 영역 USER_FREE_SPACE 현재 여유로 표시된 영역이 얼마인지에 대한 정보 
사용자 USER_USERS   
자원 제한량 USER_RESOURCE_LIMITS   
테이블 권한 USER_TAB_PRIVS   
열 권한 USER_COL_PRIVS   
시스템 권한  USER_SYS_PRIVS