[oracle xe 18c] sample schema 조회하기

vagrant 로 oracle 설치를 했는데, sample schema 를 조회해려는 시도를 기록한다. 우선 vagrant up 으로 최초설치될때 sys, system pdaadmin 의 password 를 확인해야한다. 

> vagrant up
    ....(중략)....
    oracle18c-xe-vagrant: Version 18.4.0.0.0
    oracle18c-xe-vagrant: INSTALLER: Global EM Express port enabled
    oracle18c-xe-vagrant: oracle-xe-18c.service is not a native service, redirecting to /sbin/chkconfig.
    oracle18c-xe-vagrant: Executing /sbin/chkconfig oracle-xe-18c on
    oracle18c-xe-vagrant: INSTALLER: Created and enabled oracle-xe-18c systemd's service
    oracle18c-xe-vagrant: INSTALLER: setPassword.sh file setup
    oracle18c-xe-vagrant: INSTALLER: Running user-defined post-setup scripts
    oracle18c-xe-vagrant: INSTALLER: Done running user-defined post-setup scripts
    oracle18c-xe-vagrant: ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: 4IFwe1nCV8E=1
    oracle18c-xe-vagrant: INSTALLER: Installation complete, database ready to use!

 

vagrant up 끝나면 바로 접속해서  select 해보자. 

> vagrant ssh
getting Proxy Configuration from Host...

Welcome to Oracle Linux Server release 7.7 (GNU/Linux 4.14.35-1902.5.2.2.el7uek.x86_64)

The Oracle Linux End-User License Agreement can be viewed here:

    * /usr/share/eula/eula.en_US

For additional packages, updates, documentation and community help, see:

    * http://yum.oracle.com/

[vagrant@localhost ~]$ sudo su - oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> select pdb_id , pdb_name from cdb_pdbs;

    PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
	 3
XEPDB1

	 2
PDB$SEED

SQL> connect sys/4IFwe1nCV8E=1@localhost:1521/xepdb1 as sysdba
Connected.

SQL> connect sys/4IFwe1nCV8E=1@xepdb1 as sysdba -- 이렇게도 가능.
Connected.

SQL> show con_name;

CON_NAME
------------------------------
XEPDB1

SQL> select username from cdb_users where con_id = 3 and username = 'HR';

USERNAME
--------------------------------------------------------------------------------
HR

SQL> select TABLE_NAME from all_tables where owner = 'HR';

TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES

7 rows selected.

SQL> select * from HR.jobs;

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President				    20080      40000
AD_VP	   Administration Vice President	    15000      30000
AD_ASST    Administration Assistant		     3000	6000
FI_MGR	   Finance Manager			     8200      16000
FI_ACCOUNT Accountant				     4200	9000
AC_MGR	   Accounting Manager			     8200      16000
AC_ACCOUNT Public Accountant			     4200	9000
SA_MAN	   Sales Manager			    10000      20080
SA_REP	   Sales Representative 		     6000      12008
PU_MAN	   Purchasing Manager			     8000      15000
PU_CLERK   Purchasing Clerk			     2500	5500

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN	   Stock Manager			     5500	8500
ST_CLERK   Stock Clerk				     2008	5000
SH_CLERK   Shipping Clerk			     2500	5500
IT_PROG    Programmer				     4000      10000
MK_MAN	   Marketing Manager			     9000      15000
MK_REP	   Marketing Representative		     4000	9000
HR_REP	   Human Resources Representative	     4000	9000
PR_REP	   Public Relations Representative	     4500      10500

19 rows selected.

 

한번에 connect 하기.

> sqlplus sys/4IFwe1nCV8E=1@xepdb1 as sysdba
SQL> set linesize 500
SQL> set pagesize 100
SQL> select * from hr.locations;

LOCATION_ID STREET_ADDRESS			     POSTAL_CODE  CITY				 STATE_PROVINCE 	   CO
----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
       1000 1297 Via Cola di Rie		     00989	  Roma							   IT
       1100 93091 Calle della Testa		     10934	  Venice						   IT
       1200 2017 Shinjuku-ku			     1689	  Tokyo 			 Tokyo Prefecture	   JP
       1300 9450 Kamiya-cho			     6823	  Hiroshima						   JP
       1400 2014 Jabberwocky Rd 		     26192	  Southlake			 Texas			   US
       1500 2011 Interiors Blvd 		     99236	  South San Francisco		 California		   US
       1600 2007 Zagora St			     50090	  South Brunswick		 New Jersey		   US
       1700 2004 Charade Rd			     98199	  Seattle			 Washington		   US
       1800 147 Spadina Ave			     M5V 2L7	  Toronto			 Ontario		   CA
       1900 6092 Boxwood St			     YSW 9T2	  Whitehorse			 Yukon			   CA
       2000 40-5-12 Laogianggen 		     190518	  Beijing						   CN
       2100 1298 Vileparle (E)			     490231	  Bombay			 Maharashtra		   IN
       2200 12-98 Victoria Street		     2901	  Sydney			 New South Wales	   AU
       2300 198 Clementi North			     540198	  Singapore						   SG
       2400 8204 Arthur St					  London						   UK
       2500 Magdalen Centre, The Oxford Science Park OX9 9ZB	  Oxford			 Oxford 		   UK
       2600 9702 Chester Road			     09629850293  Stretford			 Manchester		   UK
       2700 Schwanthalerstr. 7031		     80925	  Munich			 Bavaria		   DE
       2800 Rua Frei Caneca 1360		     01307-002	  Sao Paulo			 Sao Paulo		   BR
       2900 20 Rue des Corps-Saints		     1730	  Geneva			 Geneve 		   CH
       3000 Murtenstrasse 921			     3095	  Bern				 BE			   CH
       3100 Pieter Breughelstraat 837		     3029SK	  Utrecht			 Utrecht		   NL
       3200 Mariano Escobedo 9991		     11932	  Mexico City			 Distrito Federal,	   MX

23 rows selected.

 

 

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/2day_dba/12cr1db_ch3emexp/12cr1db_ch3emexp.html#section2

Getting Started with Oracle Enterprise Manager Express

select dbid, name from v$database;

select username from sys.all_users;

select name  FROM v$active_services ;

select name, con_id from v$pdbs;

 

SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2910524248 XE

SQL> select instance_name, status, con_id from v$instance;

INSTANCE_NAME	 STATUS 	  CON_ID
---------------- ------------ ----------
XE		 OPEN		       0

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME
--------------------------------------------------------------------------------
    CON_ID	 DBID	 CON_UID GUID
---------- ---------- ---------- --------------------------------
CDB$ROOT
	 1 2910524248	       1 787D0540569D64C1E0530CAAE80AA107

PDB$SEED
	 2  331383107  331383107 9BDAD79928010482E053020011AC6399

XEPDB1
	 3 2267173948 2267173948 9BDAF841F79B0A42E053020011AC6C04
     

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME 	OPEN_MODE
---------- ------- ----------
	 2 PDB$SEED		   READ ONLY
	 3 XEPDB1		   READ WRITE
     

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
     
     
SQL> alter session set container=xepdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

-- container 설정바꾸면 조회가됨.
SQL> select * from hr.jobs;

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President				    20080      40000
AD_VP	   Administration Vice President	    15000      30000
AD_ASST    Administration Assistant		     3000	6000
FI_MGR	   Finance Manager			     8200      16000
FI_ACCOUNT Accountant				     4200	9000
AC_MGR	   Accounting Manager			     8200      16000
AC_ACCOUNT Public Accountant			     4200	9000
SA_MAN	   Sales Manager			    10000      20080
SA_REP	   Sales Representative 		     6000      12008
PU_MAN	   Purchasing Manager			     8000      15000
PU_CLERK   Purchasing Clerk			     2500	5500

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN	   Stock Manager			     5500	8500
ST_CLERK   Stock Clerk				     2008	5000
SH_CLERK   Shipping Clerk			     2500	5500
IT_PROG    Programmer				     4000      10000
MK_MAN	   Marketing Manager			     9000      15000
MK_REP	   Marketing Representative		     4000	9000
HR_REP	   Human Resources Representative	     4000	9000
PR_REP	   Public Relations Representative	     4500      10500

19 rows selected.