명령 프롬프트 SQL에서의 데이터 작업

# XE DB 버전

show user
select name from v$database;

# public database link 생성

create public database link ora11g_link connect to system identified by oracle using 'ora11g';
/*
create public database link ora11g_link 
connect to system(ora11g 서버)
identified by oracle using 'ora11g';
*/

# link 생성 확인

select * from dba_db_links;

# public database link 삭제

drop public database link ORA11G_LINK;

# 디렉터리 생성 확인
SQL> select * from dba_directories;

OWNER
------------------------------------------------------------
DIRECTORY_NAME
------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS
ORACLECLRDIR
C:\oraclexe\app\oracle\product\11.2.0\server\bin\clr

SYS
DATA_PUMP_DIR
C:\oraclexe\app\oracle/admin/xe/dpdump/

OWNER
------------------------------------------------------------
DIRECTORY_NAME
------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------

SYS
XMLDIR
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml

SYS
ORACLE_OCM_CONFIG_DIR

OWNER
------------------------------------------------------------
DIRECTORY_NAME
------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
C:\ADE\aime_xe28\oracle/ccr/state

#) OS로 나오는 명령어
SQL> host
Microsoft Windows [Version 10.0.22631.3007]
(c) Microsoft Corporation. All rights reserved.

C:\Users\ITWILL>expdp system/oracle directory=data_pump_dir dumpfile=hr.dmp schemas=hr network_link=ora11g_link

# XE 버전 / hr session
C:\Users\ITWILL>sqlplus hr/hr

SQL> select * from tab;

SQL> select table_name, tablespace_name from user_tables;

# 테이블 삭제
SQL> drop table hr.job_history purge;

SQL> select count(*) from hr.job_history;
select count(*) from hr.job_history
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

# OS로 나오기
SQL> host
C:\Users\ITWILL>impdp system/oracle directory=data_pump_dir dumpfile=hr.dmp tables=hr.job_history remap_tablespace=example:users exclude=grant

# XE 버전으로 접속 
C:\Users\ITWILL>exit

SQL> show user
USER is "HR"
SQL> select count(*) from hr.job_history;

  COUNT(*)
----------
        10

# system 접속
SQL> conn / as sysdba
Connected.
SQL> select name from v$database;

NAME
------------------
XE

SQL> show user
USER is "SYS"
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
------------------------- --------------------------------------------------
USERS                     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
SYSAUX                    C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
UNDOTBS1                  C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
SYSTEM                    C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

#) user 생성 후 권한 부여
SQL> create user james identified by oracle default tablespace users quota unlimited on users;

User created.

SQL> grant create session to james;

Grant succeeded.

#) import 하기
SQL> host
C:\Users\ITWILL>impdp system/oracle directory=data_pump_dir dumpfile=hr.dmp remap_schema=hr:james remap_tablespace=example:users exclude=grant


#) oracle 접속
SQL> conn james/oracle
Connected.
SQL> show user
USER is "JAMES"
SQL> select * from tab;

TNAME                                    TABTYPE                    CLUSTERID
---------------------------------------- ------------------------- ----------
COUNTRIES                                TABLE
DEPARTMENTS                              TABLE
EMPLOYEES                                TABLE
EMP_20                                   TABLE
EMP_50_STMAN                             TABLE
EMP_DETAILS_VIEW                         VIEW
INSA_EMP                                 TABLE
JOBS                                     TABLE
JOB_HISTORY                              TABLE
LOCATIONS                                TABLE
REGIONS                                  TABLE

11 rows selected.