2022.10.27 수정
해당 게시글로 방문자가 많고, 내용 길어서 요약한다.
사전 준비는 오라클 21cXE 설치, notepad++ 설치 이다.
오라클 설치위치는 D:/Oracle21XE 이고, 스키마 압축 해제 위치는 D:/db-sample-schemas-21.1 이다.
데이터베이스 명(sid)는 study로 지정했다.
1. 스키마 다운로드 : https://github.com/oracle/db-sample-schemas/releases/latest
2. 스키마 압축 풀고, notepad++로 찾아바꾸기
2-1. notepad++로 찾아바꾸기 : 파일 > 작업 영역으로 폴더 열기 > 왼쪽 작업 영역 폴더 우클릭 > 파일에서 찾기
변경전: __SUB__CWD__
변경후 : D:/db-sample-schemas-21.1
3. sql plus 접속 :
cmd 관리자 권한 실행
> 스키마 샘플 폴더 위치로 이동 :
예시 : C:\Users\user10>D:
D:\>cd db-sample-schemas-21.1
D:\db-sample-schemas-21.1>
> sqlplus system/asdf@localhost/study:1521
> 스크립트 사용을 위한 권한 부여: ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
> 스크립트 실행 @mksample asdf asdf asdf asdf asdf asdf asdf asdf EXAMPLE TEMP d:/log/ localhost/study:1521
mksample 명령어를 실행하되 비번은 asdf 로 통일, 로그 위치와 db 접근 주소
진행 중 문제 : ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다
대응 : mksample.sql, hr_main.sql, bi_main.sql, co_main.sql, ix_main.sql, sh_main.sql, qs_main.sql, oe_main.sql, pm_main.sql 의 첫 컷넥(CONNECT) 이후에 ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE; 를 붙여 넣어 저장하고
mksample.sql을 실행시켜 권한을 모두 획득하게 했다.
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE; 는 dba_users의 oracle_maintained 부울 값을 Y로 바꿔주는 명령이기 때문에 아래와 같이 확인할 수도 있고 변경할 수도 있다.
그럼 쿼리 연습 힘내세요.
근데 하다가 또 빡치는게.. 나 웹개발 하려고 전직했는데 SM 맡아서 맨날 쿼리짜고, 프로시저 짜고, 스케줄 잡 만들어서 프로시저로 배치돌리고 .. 빨리 다시 SI 업무 하고 싶다..SQLD 이력서에 쓰지 말껄...ㅠㅠ
select
username,
oracle_maintained,
account_status
from
dba_users
where
account_status = 'OPEN'
order by
oracle_maintained,
username;
---추가---샘플스키마 실행 결과
테이블이 분석되었습니다.
All named objects and stati
OWNER OBJECT_TYPE OBJECT_NAME SUBOBJECT_NAME STATUS
------ -------------------- ------------------------------ ---------------- --------
BI SYNONYM CHANNELS VALID
BI SYNONYM COSTS VALID
BI SYNONYM COUNTRIES VALID
BI SYNONYM CUSTOMERS VALID
BI SYNONYM PRODUCTS VALID
BI SYNONYM PROMOTIONS VALID
BI SYNONYM SALES VALID
BI SYNONYM TIMES VALID
HR INDEX COUNTRY_C_ID_PK VALID
HR INDEX DEPT_ID_PK VALID
HR INDEX DEPT_LOCATION_IX VALID
HR INDEX EMP_DEPARTMENT_IX VALID
HR INDEX EMP_EMAIL_UK VALID
HR INDEX EMP_EMP_ID_PK VALID
HR INDEX EMP_JOB_IX VALID
HR INDEX EMP_MANAGER_IX VALID
HR INDEX EMP_NAME_IX VALID
HR INDEX JHIST_DEPARTMENT_IX VALID
HR INDEX JHIST_EMPLOYEE_IX VALID
HR INDEX JHIST_EMP_ID_ST_DATE_PK VALID
HR INDEX JHIST_JOB_IX VALID
HR INDEX JOB_ID_PK VALID
HR INDEX LOC_CITY_IX VALID
HR INDEX LOC_COUNTRY_IX VALID
HR INDEX LOC_ID_PK VALID
HR INDEX LOC_STATE_PROVINCE_IX VALID
HR INDEX REG_ID_PK VALID
HR PROCEDURE ADD_JOB_HISTORY VALID
HR PROCEDURE SECURE_DML VALID
HR SEQUENCE DEPARTMENTS_SEQ VALID
HR SEQUENCE EMPLOYEES_SEQ VALID
HR SEQUENCE LOCATIONS_SEQ VALID
HR TABLE COUNTRIES VALID
HR TABLE DEPARTMENTS VALID
HR TABLE EMPLOYEES VALID
HR TABLE JOBS VALID
HR TABLE JOB_HISTORY VALID
HR TABLE LOCATIONS VALID
HR TABLE REGIONS VALID
HR TRIGGER SECURE_EMPLOYEES VALID
HR TRIGGER UPDATE_JOB_HISTORY VALID
HR VIEW EMP_DETAILS_VIEW VALID
IX EVALUATION CONTEXT AQ$_ORDERS_QUEUETABLE_V VALID
IX EVALUATION CONTEXT AQ$_STREAMS_QUEUE_TABLE_V VALID
IX INDEX AQ$_STREAMS_QUEUE_TABLE_Y VALID
IX QUEUE AQ$_ORDERS_QUEUETABLE_E VALID
IX QUEUE AQ$_STREAMS_QUEUE_TABLE_E VALID
IX QUEUE ORDERS_QUEUE VALID
IX QUEUE STREAMS_QUEUE VALID
IX RULE SET ORDERS_QUEUE_N VALID
IX RULE SET ORDERS_QUEUE_R VALID
IX RULE SET STREAMS_QUEUE_N VALID
IX RULE SET STREAMS_QUEUE_R VALID
IX SEQUENCE AQ$_ORDERS_QUEUETABLE_N VALID
IX SEQUENCE AQ$_STREAMS_QUEUE_TABLE_N VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_G VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_H VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_I VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_L VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_S VALID
IX TABLE AQ$_ORDERS_QUEUETABLE_T VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_C VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_G VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_H VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_I VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_L VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_S VALID
IX TABLE AQ$_STREAMS_QUEUE_TABLE_T VALID
IX TABLE ORDERS_QUEUETABLE VALID
IX TABLE STREAMS_QUEUE_TABLE VALID
IX TYPE ORDER_EVENT_TYP VALID
IX VIEW AQ$ORDERS_QUEUETABLE VALID
IX VIEW AQ$ORDERS_QUEUETABLE_R VALID
IX VIEW AQ$ORDERS_QUEUETABLE_S VALID
IX VIEW AQ$STREAMS_QUEUE_TABLE VALID
IX VIEW AQ$STREAMS_QUEUE_TABLE_R VALID
IX VIEW AQ$STREAMS_QUEUE_TABLE_S VALID
IX VIEW AQ$_ORDERS_QUEUETABLE_F VALID
IX VIEW AQ$_STREAMS_QUEUE_TABLE_F VALID
OE FUNCTION GET_PHONE_NUMBER_F VALID
OE INDEX ACTION_TABLE_MEMBERS VALID
OE INDEX CUSTOMERS_PK VALID
OE INDEX CUST_ACCOUNT_MANAGER_IX VALID
OE INDEX CUST_EMAIL_IX VALID
OE INDEX CUST_LNAME_IX VALID
OE INDEX CUST_UPPER_NAME_IX VALID
OE INDEX INVENTORY_IX VALID
OE INDEX INV_PRODUCT_IX VALID
OE INDEX ITEM_ORDER_IX VALID
OE INDEX ITEM_PRODUCT_IX VALID
OE INDEX LINEITEM_TABLE_MEMBERS VALID
OE INDEX ORDER_ITEMS_PK VALID
OE INDEX ORDER_ITEMS_UK VALID
OE INDEX ORDER_PK VALID
OE INDEX ORD_CUSTOMER_IX VALID
OE INDEX ORD_ORDER_DATE_IX VALID
OE INDEX ORD_SALES_REP_IX VALID
OE INDEX PRD_DESC_PK VALID
OE INDEX PRODUCT_INFORMATION_PK VALID
OE INDEX PROD_NAME_IX VALID
OE INDEX PROD_SUPPLIER_IX VALID
OE INDEX PROMO_ID_PK VALID
OE INDEX WAREHOUSES_PK VALID
OE INDEX WHS_LOCATION_IX VALID
OE LOB EXTRADATA46_L VALID
OE LOB NAMESPACES47_L VALID
OE SEQUENCE ORDERS_SEQ VALID
OE SYNONYM COUNTRIES VALID
OE SYNONYM DEPARTMENTS VALID
OE SYNONYM EMPLOYEES VALID
OE SYNONYM JOBS VALID
OE SYNONYM JOB_HISTORY VALID
OE SYNONYM LOCATIONS VALID
OE TABLE ACTION_TABLE VALID
OE TABLE CATEGORIES_TAB VALID
OE TABLE CUSTOMERS VALID
OE TABLE INVENTORIES VALID
OE TABLE LINEITEM_TABLE VALID
OE TABLE ORDERS VALID
OE TABLE ORDER_ITEMS VALID
OE TABLE PRODUCT_DESCRIPTIONS VALID
OE TABLE PRODUCT_INFORMATION VALID
OE TABLE PRODUCT_REF_LIST_NESTEDTAB VALID
OE TABLE PROMOTIONS VALID
OE TABLE PURCHASEORDER VALID
OE TABLE SUBCATEGORY_REF_LIST_NESTEDTAB VALID
OE TABLE WAREHOUSES VALID
OE TRIGGER INSERT_ORD_LINE VALID
OE TRIGGER ORDERS_ITEMS_TRG VALID
OE TRIGGER ORDERS_TRG VALID
OE TRIGGER PURCHASEORDER$xd VALID
OE TYPE ACTIONS_T VALID
OE TYPE ACTION_T VALID
OE TYPE ACTION_V VALID
OE TYPE CATALOG_TYP $VSN_1 VALID
OE TYPE CATALOG_TYP VALID
OE TYPE CATEGORY_TYP $VSN_1 VALID
OE TYPE CATEGORY_TYP VALID
OE TYPE COMPOSITE_CATEGORY_TYP $VSN_1 VALID
OE TYPE COMPOSITE_CATEGORY_TYP VALID
OE TYPE CORPORATE_CUSTOMER_TYP VALID
OE TYPE CUSTOMER_TYP VALID
OE TYPE CUST_ADDRESS_TYP VALID
OE TYPE INVENTORY_LIST_TYP VALID
OE TYPE INVENTORY_TYP VALID
OE TYPE LEAF_CATEGORY_TYP $VSN_1 VALID
OE TYPE LEAF_CATEGORY_TYP VALID
OE TYPE LINEITEMS_T VALID
OE TYPE LINEITEM_T VALID
OE TYPE LINEITEM_V VALID
OE TYPE ORDER_ITEM_LIST_TYP VALID
OE TYPE ORDER_ITEM_TYP VALID
OE TYPE ORDER_LIST_TYP VALID
OE TYPE ORDER_TYP VALID
OE TYPE PART_T VALID
OE TYPE PHONE_LIST_TYP VALID
OE TYPE PRODUCT_INFORMATION_TYP VALID
OE TYPE PRODUCT_REF_LIST_TYP VALID
OE TYPE PURCHASEORDER_T VALID
OE TYPE REJECTION_T VALID
OE TYPE SHIPPING_INSTRUCTIONS_T VALID
OE TYPE SUBCATEGORY_REF_LIST_TYP VALID
OE TYPE WAREHOUSE_TYP VALID
OE TYPE BODY CATALOG_TYP VALID
OE TYPE BODY COMPOSITE_CATEGORY_TYP VALID
OE TYPE BODY LEAF_CATEGORY_TYP VALID
OE VIEW ACCOUNT_MANAGERS VALID
OE VIEW BOMBAY_INVENTORY VALID
OE VIEW CUSTOMERS_VIEW VALID
OE VIEW OC_CORPORATE_CUSTOMERS VALID
OE VIEW OC_CUSTOMERS VALID
OE VIEW OC_INVENTORIES VALID
OE VIEW OC_ORDERS VALID
OE VIEW OC_PRODUCT_INFORMATION VALID
OE VIEW ORDERS_VIEW VALID
OE VIEW PRODUCTS VALID
OE VIEW PRODUCT_PRICES VALID
OE VIEW SYDNEY_INVENTORY VALID
OE VIEW TORONTO_INVENTORY VALID
PM INDEX PRINTMEDIA_PK VALID
PM TABLE PRINT_MEDIA VALID
PM TABLE TEXTDOCS_NESTEDTAB VALID
PM TYPE ADHEADER_TYP VALID
PM TYPE TEXTDOC_TAB VALID
PM TYPE TEXTDOC_TYP VALID
SH DIMENSION CHANNELS_DIM VALID
SH DIMENSION CUSTOMERS_DIM VALID
SH DIMENSION PRODUCTS_DIM VALID
SH DIMENSION PROMOTIONS_DIM VALID
SH DIMENSION TIMES_DIM VALID
SH INDEX CHANNELS_PK VALID
SH INDEX COSTS_PROD_BIX VALID
SH INDEX COSTS_TIME_BIX VALID
SH INDEX COUNTRIES_PK VALID
SH INDEX CUSTOMERS_GENDER_BIX VALID
SH INDEX CUSTOMERS_MARITAL_BIX VALID
SH INDEX CUSTOMERS_PK VALID
SH INDEX CUSTOMERS_YOB_BIX VALID
SH INDEX DR$SUP_TEXT_IDX$KD VALID
SH INDEX DR$SUP_TEXT_IDX$KR VALID
SH INDEX DR$SUP_TEXT_IDX$X VALID
SH INDEX FW_PSC_S_MV_CHAN_BIX VALID
SH INDEX FW_PSC_S_MV_PROMO_BIX VALID
SH INDEX FW_PSC_S_MV_SUBCAT_BIX VALID
SH INDEX FW_PSC_S_MV_WD_BIX VALID
SH INDEX PRODUCTS_PK VALID
SH INDEX PRODUCTS_PROD_CAT_IX VALID
SH INDEX PRODUCTS_PROD_STATUS_BIX VALID
SH INDEX PRODUCTS_PROD_SUBCAT_IX VALID
SH INDEX PROMO_PK VALID
SH INDEX SALES_CHANNEL_BIX VALID
SH INDEX SALES_CUST_BIX VALID
SH INDEX SALES_PROD_BIX VALID
SH INDEX SALES_PROMO_BIX VALID
SH INDEX SALES_TIME_BIX VALID
SH INDEX SUP_TEXT_IDX VALID
SH INDEX TIMES_PK VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_1995 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_1996 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_H1_1997 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_H2_1997 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q1_1998 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q1_1999 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q1_2000 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q1_2001 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q1_2002 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q1_2003 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q2_1998 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q2_1999 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q2_2000 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q2_2001 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q2_2002 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q2_2003 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q3_1998 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q3_1999 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q3_2000 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q3_2001 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q3_2002 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q3_2003 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q4_1998 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q4_1999 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q4_2000 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q4_2001 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q4_2002 VALID
SH INDEX PARTITION COSTS_PROD_BIX COSTS_Q4_2003 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_1995 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_1996 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_H1_1997 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_H2_1997 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q1_1998 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q1_1999 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q1_2000 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q1_2001 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q1_2002 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q1_2003 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q2_1998 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q2_1999 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q2_2000 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q2_2001 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q2_2002 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q2_2003 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q3_1998 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q3_1999 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q3_2000 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q3_2001 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q3_2002 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q3_2003 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q4_1998 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q4_1999 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q4_2000 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q4_2001 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q4_2002 VALID
SH INDEX PARTITION COSTS_TIME_BIX COSTS_Q4_2003 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_1995 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_1996 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_H1_1997 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_H2_1997 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q1_1998 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q1_1999 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q1_2000 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q1_2001 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q1_2002 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q1_2003 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q2_1998 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q2_1999 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q2_2000 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q2_2001 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q2_2002 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q2_2003 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q3_1998 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q3_1999 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q3_2000 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q3_2001 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q3_2002 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q3_2003 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q4_1998 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q4_1999 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q4_2000 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q4_2001 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q4_2002 VALID
SH INDEX PARTITION SALES_CHANNEL_BIX SALES_Q4_2003 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_1995 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_1996 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_H1_1997 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_H2_1997 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q1_1998 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q1_1999 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q1_2000 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q1_2001 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q1_2002 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q1_2003 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q2_1998 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q2_1999 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q2_2000 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q2_2001 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q2_2002 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q2_2003 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q3_1998 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q3_1999 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q3_2000 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q3_2001 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q3_2002 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q3_2003 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q4_1998 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q4_1999 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q4_2000 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q4_2001 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q4_2002 VALID
SH INDEX PARTITION SALES_CUST_BIX SALES_Q4_2003 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_1995 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_1996 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_H1_1997 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_H2_1997 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q1_1998 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q1_1999 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q1_2000 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q1_2001 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q1_2002 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q1_2003 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q2_1998 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q2_1999 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q2_2000 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q2_2001 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q2_2002 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q2_2003 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q3_1998 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q3_1999 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q3_2000 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q3_2001 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q3_2002 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q3_2003 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q4_1998 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q4_1999 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q4_2000 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q4_2001 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q4_2002 VALID
SH INDEX PARTITION SALES_PROD_BIX SALES_Q4_2003 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_1995 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_1996 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_H1_1997 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_H2_1997 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q1_1998 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q1_1999 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q1_2000 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q1_2001 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q1_2002 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q1_2003 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q2_1998 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q2_1999 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q2_2000 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q2_2001 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q2_2002 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q2_2003 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q3_1998 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q3_1999 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q3_2000 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q3_2001 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q3_2002 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q3_2003 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q4_1998 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q4_1999 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q4_2000 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q4_2001 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q4_2002 VALID
SH INDEX PARTITION SALES_PROMO_BIX SALES_Q4_2003 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_1995 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_1996 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_H1_1997 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_H2_1997 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q1_1998 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q1_1999 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q1_2000 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q1_2001 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q1_2002 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q1_2003 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q2_1998 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q2_1999 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q2_2000 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q2_2001 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q2_2002 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q2_2003 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q3_1998 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q3_1999 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q3_2000 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q3_2001 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q3_2002 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q3_2003 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q4_1998 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q4_1999 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q4_2000 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q4_2001 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q4_2002 VALID
SH INDEX PARTITION SALES_TIME_BIX SALES_Q4_2003 VALID
SH MATERIALIZED VIEW CAL_MONTH_SALES_MV VALID
SH MATERIALIZED VIEW FWEEK_PSCAT_SALES_MV VALID
SH TABLE CAL_MONTH_SALES_MV VALID
SH TABLE CHANNELS VALID
SH TABLE COSTS VALID
SH TABLE COUNTRIES VALID
SH TABLE CUSTOMERS VALID
SH TABLE DR$SUP_TEXT_IDX$I VALID
SH TABLE DR$SUP_TEXT_IDX$K VALID
SH TABLE DR$SUP_TEXT_IDX$N VALID
SH TABLE DR$SUP_TEXT_IDX$U VALID
SH TABLE FWEEK_PSCAT_SALES_MV VALID
SH TABLE PRODUCTS VALID
SH TABLE PROMOTIONS VALID
SH TABLE SALES VALID
SH TABLE SALES_TRANSACTIONS_EXT VALID
SH TABLE SUPPLEMENTARY_DEMOGRAPHICS VALID
SH TABLE TIMES VALID
SH TABLE PARTITION COSTS COSTS_1995 VALID
SH TABLE PARTITION COSTS COSTS_1996 VALID
SH TABLE PARTITION COSTS COSTS_H1_1997 VALID
SH TABLE PARTITION COSTS COSTS_H2_1997 VALID
SH TABLE PARTITION COSTS COSTS_Q1_1998 VALID
SH TABLE PARTITION COSTS COSTS_Q1_1999 VALID
SH TABLE PARTITION COSTS COSTS_Q1_2000 VALID
SH TABLE PARTITION COSTS COSTS_Q1_2001 VALID
SH TABLE PARTITION COSTS COSTS_Q1_2002 VALID
SH TABLE PARTITION COSTS COSTS_Q1_2003 VALID
SH TABLE PARTITION COSTS COSTS_Q2_1998 VALID
SH TABLE PARTITION COSTS COSTS_Q2_1999 VALID
SH TABLE PARTITION COSTS COSTS_Q2_2000 VALID
SH TABLE PARTITION COSTS COSTS_Q2_2001 VALID
SH TABLE PARTITION COSTS COSTS_Q2_2002 VALID
SH TABLE PARTITION COSTS COSTS_Q2_2003 VALID
SH TABLE PARTITION COSTS COSTS_Q3_1998 VALID
SH TABLE PARTITION COSTS COSTS_Q3_1999 VALID
SH TABLE PARTITION COSTS COSTS_Q3_2000 VALID
SH TABLE PARTITION COSTS COSTS_Q3_2001 VALID
SH TABLE PARTITION COSTS COSTS_Q3_2002 VALID
SH TABLE PARTITION COSTS COSTS_Q3_2003 VALID
SH TABLE PARTITION COSTS COSTS_Q4_1998 VALID
SH TABLE PARTITION COSTS COSTS_Q4_1999 VALID
SH TABLE PARTITION COSTS COSTS_Q4_2000 VALID
SH TABLE PARTITION COSTS COSTS_Q4_2001 VALID
SH TABLE PARTITION COSTS COSTS_Q4_2002 VALID
SH TABLE PARTITION COSTS COSTS_Q4_2003 VALID
SH TABLE PARTITION SALES SALES_1995 VALID
SH TABLE PARTITION SALES SALES_1996 VALID
SH TABLE PARTITION SALES SALES_H1_1997 VALID
SH TABLE PARTITION SALES SALES_H2_1997 VALID
SH TABLE PARTITION SALES SALES_Q1_1998 VALID
SH TABLE PARTITION SALES SALES_Q1_1999 VALID
SH TABLE PARTITION SALES SALES_Q1_2000 VALID
SH TABLE PARTITION SALES SALES_Q1_2001 VALID
SH TABLE PARTITION SALES SALES_Q1_2002 VALID
SH TABLE PARTITION SALES SALES_Q1_2003 VALID
SH TABLE PARTITION SALES SALES_Q2_1998 VALID
SH TABLE PARTITION SALES SALES_Q2_1999 VALID
SH TABLE PARTITION SALES SALES_Q2_2000 VALID
SH TABLE PARTITION SALES SALES_Q2_2001 VALID
SH TABLE PARTITION SALES SALES_Q2_2002 VALID
SH TABLE PARTITION SALES SALES_Q2_2003 VALID
SH TABLE PARTITION SALES SALES_Q3_1998 VALID
SH TABLE PARTITION SALES SALES_Q3_1999 VALID
SH TABLE PARTITION SALES SALES_Q3_2000 VALID
SH TABLE PARTITION SALES SALES_Q3_2001 VALID
SH TABLE PARTITION SALES SALES_Q3_2002 VALID
SH TABLE PARTITION SALES SALES_Q3_2003 VALID
SH TABLE PARTITION SALES SALES_Q4_1998 VALID
SH TABLE PARTITION SALES SALES_Q4_1999 VALID
SH TABLE PARTITION SALES SALES_Q4_2000 VALID
SH TABLE PARTITION SALES SALES_Q4_2001 VALID
SH TABLE PARTITION SALES SALES_Q4_2002 VALID
SH TABLE PARTITION SALES SALES_Q4_2003 VALID
SH VIEW PROFITS VALID
488 행이 선택되었습니다.
Data types used
OWNER DATA_TYPE DATA_TYPE_OWNER DATA_T COUNT(*)
------ ----------------------------------- ---------------- ------ ----------
PM ADHEADER_TYP PM 1
IX ANYDATA SYS 7
IX AQ$_SIG_PROP SYS 4
PM BFILE 1
PM BLOB 2
SH BLOB 1
HR CHAR 3
IX CHAR 2
OE CHAR 2
SH CHAR 5
IX CLOB 2
PM CLOB 2
OE CUSTOMER_TYP OE REF 1
OE CUST_ADDRESS_TYP OE 3
HR DATE 3
IX DATE 6
OE DATE 2
SH DATE 19
OE INTERVAL YEAR(2) TO MONTH 3
OE INVENTORY_LIST_TYP OE 1
PM NCLOB 1
HR NUMBER 21
IX NUMBER 107
OE NUMBER 80
PM NUMBER 2
SH NUMBER 100
OE NVARCHAR2 7
IX ORDER_EVENT_TYP IX 3
OE ORDER_ITEM_LIST_TYP OE 1
OE ORDER_LIST_TYP OE 2
OE PHONE_LIST_TYP OE 3
IX RAW 32
IX ROWID 6
SH ROWID 2
OE SDO_GEOMETRY MDSYS 2
PM TEXTDOC_TAB PM 1
IX TIMESTAMP(6) 25
OE TIMESTAMP(6) WITH LOCAL TIME ZONE 1
IX TIMESTAMP(6) WITH TIME ZONE 8
IX TIMESTAMP(9) 2
HR VARCHAR2 24
IX VARCHAR2 128
OE VARCHAR2 53
SH VARCHAR2 47
OE WAREHOUSE_TYP OE 1
OE XMLTYPE SYS 2
46 행이 선택되었습니다.
XML tables
OWNER TABLE_NAME SCHEMA_OWNER STORAGE_TYPE
------ ------------------------------ ---------------- --------------------
OE PURCHASEORDER OE OBJECT-RELATIONAL
1개의 행이 선택되었습니다.
All objects named 'SYS%' (LOBs etc)
OWNER OBJECT_TYPE STATUS COUNT(*)
------ -------------------- -------- ----------
IX INDEX VALID 16
OE INDEX VALID 24
PM INDEX VALID 9
SH INDEX VALID 3
IX LOB VALID 3
OE LOB VALID 13
PM LOB VALID 7
SH LOB VALID 1
IX TABLE VALID 2
OE TYPE VALID 5
10 행이 선택되었습니다.
All constraints
OWNER CONSTRAINT_TYPE STATUS VALIDATED GENERATED COUNT(*)
------ -------------------- -------- ---------------- ---------------- ----------
IX Check or Not Null ENABLED VALIDATED GENERATED NAME 4
OE Check or Not Null ENABLED VALIDATED GENERATED NAME 2
SH Check or Not Null ENABLED VALIDATED GENERATED NAME 121
HR Check or Not Null ENABLED VALIDATED USER NAME 15
OE Check or Not Null ENABLED VALIDATED USER NAME 13
SH Foreign key DISABLED NOT VALIDATED USER NAME 2
OE Foreign key ENABLED NOT VALIDATED USER NAME 2
SH Foreign key ENABLED NOT VALIDATED USER NAME 8
HR Foreign key ENABLED VALIDATED USER NAME 10
OE Foreign key ENABLED VALIDATED USER NAME 7
PM Foreign key ENABLED VALIDATED USER NAME 1
SH Primary key DISABLED NOT VALIDATED USER NAME 1
SH Primary key ENABLED NOT VALIDATED USER NAME 6
IX Primary key ENABLED VALIDATED GENERATED NAME 13
OE Primary key ENABLED VALIDATED GENERATED NAME 3
SH Primary key ENABLED VALIDATED GENERATED NAME 2
HR Primary key ENABLED VALIDATED USER NAME 7
OE Primary key ENABLED VALIDATED USER NAME 8
PM Primary key ENABLED VALIDATED USER NAME 1
HR Read only view ENABLED NOT VALIDATED GENERATED NAME 1
IX Read only view ENABLED NOT VALIDATED GENERATED NAME 8
OE Unique key ENABLED VALIDATED GENERATED NAME 6
PM Unique key ENABLED VALIDATED GENERATED NAME 1
HR Unique key ENABLED VALIDATED USER NAME 1
24 행이 선택되었습니다.
All dimensions
OWNER DIMENSION_NAME IN COMPILE_STATE
------ -------------------- -- --------------------------
SH CHANNELS_DIM N VALID
SH CUSTOMERS_DIM N VALID
SH PRODUCTS_DIM N VALID
SH PROMOTIONS_DIM N VALID
SH TIMES_DIM N VALID
5 행이 선택되었습니다.
All granted roles
GRANTED_ROLE GRANTEE
------------------------- -------
AQ_ADMINISTRATOR_ROLE IX
AQ_USER_ROLE IX
CONNECT IX
CONNECT PM
RESOURCE BI
RESOURCE HR
RESOURCE IX
RESOURCE OE
RESOURCE PM
RESOURCE SH
SELECT_CATALOG_ROLE IX
SELECT_CATALOG_ROLE SH
XDBADMIN OE
13 행이 선택되었습니다.
All granted system privileges
PRIVILEGE GRANTEE
------------------------- -------
ALTER SESSION BI
ALTER SESSION HR
ALTER SESSION IX
ALTER SESSION OE
ALTER SESSION SH
CREATE CLUSTER BI
CREATE CLUSTER IX
CREATE CLUSTER SH
CREATE DATABASE LINK BI
CREATE DATABASE LINK HR
CREATE DATABASE LINK IX
CREATE DATABASE LINK OE
CREATE DATABASE LINK SH
CREATE DIMENSION SH
CREATE INDEXTYPE IX
CREATE MATERIALIZED VIEW OE
CREATE MATERIALIZED VIEW SH
CREATE OPERATOR IX
CREATE PROCEDURE IX
CREATE RULE IX
CREATE RULE SET IX
CREATE SEQUENCE BI
CREATE SEQUENCE HR
CREATE SEQUENCE IX
CREATE SEQUENCE SH
CREATE SESSION BI
CREATE SESSION HR
CREATE SESSION IX
CREATE SESSION OE
CREATE SESSION SH
CREATE SYNONYM BI
CREATE SYNONYM HR
CREATE SYNONYM IX
CREATE SYNONYM OE
CREATE SYNONYM SH
CREATE TABLE BI
CREATE TABLE IX
CREATE TABLE SH
CREATE TRIGGER IX
CREATE TYPE IX
CREATE VIEW BI
CREATE VIEW HR
CREATE VIEW IX
CREATE VIEW OE
CREATE VIEW SH
QUERY REWRITE OE
QUERY REWRITE SH
SELECT ANY DICTIONARY IX
UNLIMITED TABLESPACE BI
UNLIMITED TABLESPACE HR
UNLIMITED TABLESPACE IX
UNLIMITED TABLESPACE OE
UNLIMITED TABLESPACE PM
UNLIMITED TABLESPACE SH
54 행이 선택되었습니다.
All granted object privileges
OWNER TABLE_NAME PRIVILEGE GRANTEE
------ ------------------------------ ------------------------- -------
HR COUNTRIES REFERENCES OE
HR COUNTRIES SELECT OE
HR DEPARTMENTS SELECT OE
HR EMPLOYEES REFERENCES OE
HR EMPLOYEES SELECT OE
HR JOBS SELECT OE
HR JOB_HISTORY SELECT OE
HR LOCATIONS REFERENCES OE
HR LOCATIONS SELECT OE
OE BOMBAY_INVENTORY SELECT BI
OE CUSTOMERS SELECT BI
OE CUSTOMERS SELECT PM
OE INVENTORIES SELECT BI
OE INVENTORIES SELECT PM
OE ORDERS SELECT BI
OE ORDERS SELECT PM
OE ORDER_ITEMS SELECT BI
OE ORDER_ITEMS SELECT PM
OE PRODUCTS SELECT BI
OE PRODUCT_DESCRIPTIONS SELECT BI
OE PRODUCT_DESCRIPTIONS SELECT PM
OE PRODUCT_INFORMATION REFERENCES PM
OE PRODUCT_INFORMATION SELECT BI
OE PRODUCT_INFORMATION SELECT PM
OE PRODUCT_PRICES SELECT BI
OE PROMOTIONS SELECT BI
OE SYDNEY_INVENTORY SELECT BI
OE TORONTO_INVENTORY SELECT BI
OE WAREHOUSES SELECT BI
OE WAREHOUSES SELECT PM
SH CAL_MONTH_SALES_MV SELECT BI
SH CHANNELS SELECT BI
SH COSTS SELECT BI
SH COUNTRIES SELECT BI
SH CUSTOMERS SELECT BI
SH FWEEK_PSCAT_SALES_MV SELECT BI
SH PRODUCTS SELECT BI
SH PROMOTIONS SELECT BI
SH SALES SELECT BI
SH TIMES SELECT BI
SYS AQ$_UNFLUSHED_DEQUEUES SELECT IX
SYS DATA_FILE_DIR READ SH
SYS DBMS_APPLY_ADM EXECUTE IX
SYS DBMS_AQ EXECUTE IX
SYS DBMS_AQADM EXECUTE IX
SYS DBMS_AQ_BQVIEW EXECUTE IX
SYS DBMS_CAPTURE_ADM EXECUTE IX
SYS DBMS_FLASHBACK EXECUTE IX
SYS DBMS_PROPAGATION_ADM EXECUTE IX
SYS DBMS_STATS EXECUTE HR
SYS DBMS_STATS EXECUTE IX
SYS DBMS_STATS EXECUTE OE
SYS DBMS_STATS EXECUTE PM
SYS DBMS_STATS EXECUTE SH
SYS DBMS_STREAMS_ADM EXECUTE IX
SYS LOG_FILE_DIR READ SH
SYS LOG_FILE_DIR WRITE SH
SYS MEDIA_DIR READ PM
SYS QT74230_BUFFER SELECT IX
SYS QT74257_BUFFER SELECT IX
SYS SS_OE_XMLDIR READ OE
SYS SS_OE_XMLDIR WRITE OE
SYS SUBDIR READ OE
SYS SUBDIR WRITE OE
64 행이 선택되었습니다.
Space usage
OWNER SEGMENT_TYPE SUM(BYTES)
------ -------------------- ----------
HR INDEX 1245184
HR TABLE 393216
HR 1638400
IX INDEX 917504
IX TABLE 524288
IX LOBINDEX 196608
IX LOBSEGMENT 393216
IX 2031616
OE INDEX 3080192
OE TABLE 3932160
OE LOBINDEX 983040
OE LOBSEGMENT 1966080
OE NESTED TABLE 589824
OE 10551296
PM INDEX 196608
PM TABLE 65536
PM LOBINDEX 458752
PM LOBSEGMENT 5308416
PM NESTED TABLE 65536
PM 6094848
SH INDEX 2424832
SH TABLE 19791872
SH LOBINDEX 65536
SH LOBSEGMENT 131072
SH INDEX PARTITION 9043968
SH TABLE PARTITION 134217728
SH 165675008
185991168
28 행이 선택되었습니다.
Table cardinality relational and object tables
OWNER TABLE_NAME NUM_ROWS
------ ------------------------------ ----------
HR COUNTRIES 25
HR DEPARTMENTS 27
HR EMPLOYEES 107
HR JOBS 19
HR JOB_HISTORY 10
HR LOCATIONS 23
HR REGIONS 4
IX AQ$_ORDERS_QUEUETABLE_G 0
IX AQ$_ORDERS_QUEUETABLE_H 2
IX AQ$_ORDERS_QUEUETABLE_I 2
IX AQ$_ORDERS_QUEUETABLE_L 2
IX AQ$_ORDERS_QUEUETABLE_S 4
IX AQ$_ORDERS_QUEUETABLE_T 0
IX AQ$_STREAMS_QUEUE_TABLE_C 0
IX AQ$_STREAMS_QUEUE_TABLE_G 0
IX AQ$_STREAMS_QUEUE_TABLE_H 0
IX AQ$_STREAMS_QUEUE_TABLE_I 0
IX AQ$_STREAMS_QUEUE_TABLE_L 0
IX AQ$_STREAMS_QUEUE_TABLE_S 1
IX AQ$_STREAMS_QUEUE_TABLE_T 0
IX ORDERS_QUEUETABLE
IX STREAMS_QUEUE_TABLE
IX SYS_IOT_OVER_74244 0
IX SYS_IOT_OVER_74273 0
OE ACTION_TABLE 132
OE CATEGORIES_TAB 22
OE CUSTOMERS 319
OE INVENTORIES 1112
OE LINEITEM_TABLE 2232
OE ORDERS 105
OE ORDER_ITEMS 665
OE PRODUCT_DESCRIPTIONS 8640
OE PRODUCT_INFORMATION 288
OE PRODUCT_REF_LIST_NESTEDTAB 288
OE PROMOTIONS 2
OE PURCHASEORDER 132
OE SUBCATEGORY_REF_LIST_NESTEDTAB 21
OE WAREHOUSES 9
PM PRINT_MEDIA 4
PM TEXTDOCS_NESTEDTAB 12
SH CAL_MONTH_SALES_MV 48
SH CHANNELS 5
SH COSTS 0
SH COUNTRIES 23
SH CUSTOMERS 55500
SH DR$SUP_TEXT_IDX$I
SH DR$SUP_TEXT_IDX$K
SH DR$SUP_TEXT_IDX$N
SH DR$SUP_TEXT_IDX$U
SH FWEEK_PSCAT_SALES_MV 11266
SH PRODUCTS 72
SH PROMOTIONS 503
SH SALES 918843
SH SALES_TRANSACTIONS_EXT
SH SUPPLEMENTARY_DEMOGRAPHICS 4500
SH TIMES 1826
56 행이 선택되었습니다.
Index cardinality (without LOB indexes)
OWNER INDEX_NAME DISTINCT_KEYS NUM_ROWS
------ ------------------------- ------------- ----------
HR COUNTRY_C_ID_PK 25 25
HR DEPT_ID_PK 27 27
HR DEPT_LOCATION_IX 7 27
HR EMP_DEPARTMENT_IX 11 106
HR EMP_EMAIL_UK 107 107
HR EMP_EMP_ID_PK 107 107
HR EMP_JOB_IX 19 107
HR EMP_MANAGER_IX 18 106
HR EMP_NAME_IX 107 107
HR JHIST_DEPARTMENT_IX 6 10
HR JHIST_EMPLOYEE_IX 7 10
HR JHIST_EMP_ID_ST_DATE_PK 10 10
HR JHIST_JOB_IX 8 10
HR JOB_ID_PK 19 19
HR LOC_CITY_IX 23 23
HR LOC_COUNTRY_IX 14 23
HR LOC_ID_PK 23 23
HR LOC_STATE_PROVINCE_IX 17 17
HR REG_ID_PK 4 4
IX AQ$_STREAMS_QUEUE_TABLE_Y 0 0
OE ACTION_TABLE_MEMBERS 132 132
OE CUSTOMERS_PK 319 319
OE CUST_ACCOUNT_MANAGER_IX 4 319
OE CUST_EMAIL_IX 319 319
OE CUST_LNAME_IX 176 319
OE CUST_UPPER_NAME_IX 319 319
OE INVENTORY_IX 1112 1112
OE INV_PRODUCT_IX 208 1112
OE ITEM_ORDER_IX 105 665
OE ITEM_PRODUCT_IX 185 665
OE LINEITEM_TABLE_MEMBERS 132 132
OE ORDER_ITEMS_PK 665 665
OE ORDER_ITEMS_UK 665 665
OE ORDER_PK 105 105
OE ORD_CUSTOMER_IX 47 105
OE ORD_ORDER_DATE_IX 105 105
OE ORD_SALES_REP_IX 9 70
OE PRD_DESC_PK 8640 8640
OE PRODUCT_INFORMATION_PK 288 288
OE PROD_NAME_IX 3727 8640
OE PROD_SUPPLIER_IX 62 288
OE PROMO_ID_PK 2 2
OE WAREHOUSES_PK 9 9
OE WHS_LOCATION_IX 9 9
PM PRINTMEDIA_PK 4 4
SH CHANNELS_PK 5 5
SH COSTS_PROD_BIX 0 0
SH COSTS_TIME_BIX 0 0
SH COUNTRIES_PK 23 23
SH CUSTOMERS_GENDER_BIX 2 5
SH CUSTOMERS_MARITAL_BIX 11 18
SH CUSTOMERS_PK 55500 55500
SH CUSTOMERS_YOB_BIX 75 75
SH DR$SUP_TEXT_IDX$KD 0 0
SH DR$SUP_TEXT_IDX$KR 0 0
SH DR$SUP_TEXT_IDX$X 0 0
SH FW_PSC_S_MV_CHAN_BIX 4 4
SH FW_PSC_S_MV_PROMO_BIX 4 4
SH FW_PSC_S_MV_SUBCAT_BIX 21 21
SH FW_PSC_S_MV_WD_BIX 210 210
SH PRODUCTS_PK 72 72
SH PRODUCTS_PROD_CAT_IX 5 72
SH PRODUCTS_PROD_STATUS_BIX 1 1
SH PRODUCTS_PROD_SUBCAT_IX 21 72
SH PROMO_PK 503 503
SH SALES_CHANNEL_BIX 4 92
SH SALES_CUST_BIX 7059 35808
SH SALES_PROD_BIX 72 1074
SH SALES_PROMO_BIX 4 54
SH SALES_TIME_BIX 1460 1460
SH SUP_TEXT_IDX
SH TIMES_PK 1826 1826
72 행이 선택되었습니다.
SQL>
여기서부터 수정 전
=======================================================
oracle 18xe로 연습하고 있다.
Oracle SQL을 처음 접한게 2013년도 10c 였다.
뇌를 자극하는 오라클 프로그래밍 SQL & PL/SQL 이었다.
DB 버전업에 따라 내용이 많이 바껴서 샘플 스키마 설치부터 찾아보게되었다.
OS 환경 : windows 10
0. 샘플 스키마 다운로드
Database Sample Schemas
docs.oracle.com
hr은 oracle database 설치 시 내장되어 있지만, 나머지는 별도 git에서 다운받아 사용한다.
1. 샘플 스키마의 압축 해제 위치
C:\app\사용자명\product\18.0.0\dbhomeXE\demo\db-sample-schemas-12.2.0.1
C:/app/사용자명/product/18.0.0/dbhomeXE/demo/schema/db-sample-schemas-12.2.0.1
1.1. 샘플 스키마 설치 위치(__SUB__CWD__) 변경
1) C:\app\son10\product\18.0.0\dbhomeXE\demo\schema\db-sample-schemas-12.2.0.1을
C:\app\son10\product\18.0.0\dbhomeXE\demo\schema 에 붙여 넣기
2) C:\app\son10\product\18.0.0\dbhomeXE\demo\schema를 notepad++ 로 열기 (폴더 드래그하면 됨.)
폴더를 notepad++ 화면으로 드래그 > 최상위 폴더 우클릭 > 파일에서 찾기
3) __SUB__CWD__/ 를 : C:/app/son10/product/18.0.0/dbhomeXE/demo/schema/로 찾아 바꾸기함.
2. sqlplus 로 db 접속
Microsoft Windows [Version 10.0.19042.1165]
(c) Microsoft Corporation. All rights reserved.
C:\Users\son10>sqlplus
SQL*Plus: Release 18.0.0.0.0 - Production on 월 8월 16 00:41:26 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
사용자명 입력: sys as sysdba
비밀번호 입력: oracle
다음에 접속됨:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>
2.1. ORACLE SCRIPT 수행되도록 변경 (12? 버전 이후부터 변경되었다고함..cdb에서 pdb로 바뀌면서..)
SQL> alter session set "_ORACLE_SCRIPT"=true;
2.2. db 이름 확인
SQL> select name, con_id, dbid, con_uid from v$containers order by con_id;
NAME
CON_ID DBID CON_UID
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
---------------------- ---------- ---------- ----------
CDB$ROOT
1 2950367133 1
PDB$SEED
2 3504786223 3504786223
XEPDB1
3 2852623957 2852623957
3. 샘플 스키마 생성 sql 실행
SQL> @?/demo/schema/mksample.sql
specify password for SYSTEM as parameter 1:
1의 값을 입력하십시오: oracle
specify password for SYS as parameter 2:
2의 값을 입력하십시오: oracle
specify password for HR as parameter 3:
3의 값을 입력하십시오: hr
specify password for OE as parameter 4:
4의 값을 입력하십시오: oe
specify password for PM as parameter 5:
5의 값을 입력하십시오: pm
specify password for IX as parameter 6:
6의 값을 입력하십시오: ix
specify password for SH as parameter 7:
7의 값을 입력하십시오: sh
specify password for BI as parameter 8:
8의 값을 입력하십시오: bi
specify default tablespace as parameter 9:
9의 값을 입력하십시오: example
specify temporary tablespace as parameter 10:
10의 값을 입력하십시오: temp
specify log file directory (including trailing delimiter) as parameter 11:
11의 값을 입력하십시오: C:/app/son10/product/18.0.0/dbhomeXE/demo/schema/log
specify connect string as parameter 12:
12의 값을 입력하십시오: localhost:1521/xepdb1
4. 샘플 스키마 다이어그램
3 Schema Diagrams
Examine the diagrams of the sample database schemas.
3.1 Sample Schema Diagrams
Figure 3-1 illustrates sample schemas HR and OE and their relationship. The scripts and table descriptions for these schemas are in section "HR Schema" and "OE Schema", respectively.
Figure 3-2 illustrates schema PM. The scripts and table description for schema PM are at "PM Schema".
Figure 3-3 illustrates schema SH. The scripts and table description for schema SH are in section "SH Schema".
This edition of the book does not illustrate schema IX, but its scripts and table description are in section "IX Schema".
Figure 3-1 Sample Schemas HR and OE
Description of "Figure 3-1 Sample Schemas HR and OE"
Figure 3-2 Sample Schemas OE and PM
Description of "Figure 3-2 Sample Schemas OE and PM"
Figure 3-3 Sample Schema SH
Description of "Figure 3-3 Sample Schema SH"
5. 샘플 테이블 확인
SQL> desc orders;
이름 널? 유형
----------------------------------------------- -------- --------------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZON
E
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)
SQL>
참고. 1.1 수행 안하면 아래와 같은 에러가 발생함
SQL> @?/demo/schema/db-sample-schemas-19.2/mksample.sql
specify password for SYSTEM as parameter 1:
specify password for SYS as parameter 2:
specify password for HR as parameter 3:
specify password for OE as parameter 4:
specify password for PM as parameter 5:
specify password for IX as parameter 6:
specify password for SH as parameter 7:
specify password for BI as parameter 8:
specify default tablespace as parameter 9:
specify temporary tablespace as parameter 10:
10의 값을 입력하십시오: temp
specify log file directory (including trailing delimiter) as parameter 11:
11의 값을 입력하십시오: $ORACLE_HOME/demo/schema/log/
specify connect string as parameter 12:
12의 값을 입력하십시오: localhost:1521/pdb
Sample Schemas are being created ...
명령 구문이 올바르지 않습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0640: 연결되지 않았습니다.
SP2-0640: 연결되지 않았습니다.
SP2-0640: 연결되지 않았습니다.
SP2-0640: 연결되지 않았습니다.
SP2-0640: 연결되지 않았습니다.
SP2-0640: 연결되지 않았습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0310: 파일 "__SUB__CWD__/human_resources/hr_main.sql"을 열 수 없습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0310: 파일 "__SUB__CWD__/order_entry/oe_main.sql"을 열 수 없습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0310: 파일 "__SUB__CWD__/product_media/pm_main.sql"을 열 수 없습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0310: 파일 "__SUB__CWD__/info_exchange/ix_main.sql"을 열 수 없습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0310: 파일 "__SUB__CWD__/sales_history/sh_main.sql"을 열 수 없습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
SP2-0310: 파일 "__SUB__CWD__/bus_intelligence/bi_main.sql"을 열 수 없습니다.
ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함
현재 스풀되지 않고 있습니다.
SP2-0310: 파일 "__SUB__CWD__/mkverify.sql"을 열 수 없습니다.
소감.
윈도우에 perl 설치해서 파일 내용(__SUB__CWD__)을 바꿔 보려했지만, 명령어 실행이 원하는 대로 되지 않았다(옵션이 다름..ㅠ)
쓰던 notepad++ 사용해서 해결하는게 맘편했다.
cmd창 로깅안되서 뭔가 실행 안되는게 있었을꺼 같은데
하다가 안되면 해당 테이블만 수동 추가하는것도 나쁘지 않을것 같다.
이 샘플 스키마 구축하려고 몇시간을 날린건지.. 화가 났지만, 해결해서 참는다.
소감2. 뭔가 잘안됨
C:\Users\son10>sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on 월 8월 16 01:50:50 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
다음에 접속됨:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> alter session set "_ORACLE_SCRIPT"=true;
세션이 변경되었습니다.
SQL> create
2 pluggable database pdb
3 admin user sysdba
4 identified by oracle
5 file_name_convert=('\pdbseed\','\pdb\');
플러그인할 수 있는 데이터베이스가 생성되었습니다.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
------------------------------------------
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\SYSAUX01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\UNDOTBS01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\PDBSEED\SYSTEM01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\PDBSEED\SYSAUX01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\PDBSEED\UNDOTBS01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\XEPDB1\SYSTEM01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\XEPDB1\SYSAUX01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\XEPDB1\UNDOTBS01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\XEPDB1\USERS01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\PDB\SYSTEM01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\PDB\SYSAUX01.DBF
C:\APP\SON10\PRODUCT\18.0.0\ORADATA\XE\PDB\UNDOTBS01.DBF
14 행이 선택되었습니다.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XEPDB1 READ WRITE NO
5 PDB MOUNTED
SQL> alter pluggable database pdb open;
플러그인할 수 있는 데이터베이스가 변경되었습니다.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XEPDB1 READ WRITE NO
5 PDB READ WRITE NO
SQL> exit
C:\Users\son10>sqlplus
SQL*Plus: Release 18.0.0.0.0 - Production on 월 8월 16 02:09:55 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
사용자명 입력: scott
비밀번호 입력: tiger
마지막 성공한 로그인 시간: 일 8월 15 2021 23:27:17 +09:00
다음에 접속됨:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
@?/demo/schema/mksample oracle oracle hr oe pm ix sh bi EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb
'학습 > DataBase' 카테고리의 다른 글
| Windows 시작 시 PostgreSQL 자동 실행 (0) | 2024.09.30 |
|---|---|
| MySQL 5.0과 MySQL 8.x에서 CHARSET과 COLLATE 차이 (0) | 2024.06.24 |
| MySQL max_allowed_packet 변수 조정하기 : feat. 사진 업로드 실패 (0) | 2024.06.24 |
| [DB] AWS MySQL 연결 실패 (0) | 2023.12.27 |
| oracle 19c 설치 실패 및 대응 (0) | 2022.10.27 |
