[oracle@localhost ~]$ set pagesize 1000
[oracle@localhost ~]$ set long 9
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 30 09:18:11 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS> startup
ORACLE instance started.
Total System Global Area 623546368 bytes
Fixed Size 1338308 bytes
Variable Size 469763132 bytes
Database Buffers 146800640 bytes
Redo Buffers 5644288 bytes
Database mounted.
Database opened.
SYS> conn hr/hr
Connected.
HR> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)
2 from dba_tablespaces;
from dba_tablespaces
*
ERROR at line 2:
ORA-00942: table or view does not exist
HR> conn / as sysdba
Connected.
SYS>
SYS> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)
2 from dba_tablespaces;
DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)
--------------------------------------------------------------------------------
CREATE TABLESPACE "SYSTEM" DATAFILE
'/u01/app/oracle/oradata/orcl/system01
CREATE TABLESPACE "SYSAUX" DATAFILE
'/u01/app/oracle/oradata/orcl/sysaux01
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'/u01/app/oracle/oradata/orcl/u
DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)
--------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/u01/app/oracle/oradata/orcl/
CREATE TABLESPACE "USERS" DATAFILE
'/u01/app/oracle/oradata/orcl/users01.d
CREATE TABLESPACE "NEW_USERS" DATAFILE
DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME)
--------------------------------------------------------------------------------
'/u01/app/oracle/product/11.2.0/db_
CREATE TABLESPACE "EMP_TBS1" DATAFILE
'/u01/app/oracle/oradata/orcl/emp_tb
CREATE TABLESPACE "EMP_TBS2" DATAFILE
'/u01/app/oracle/oradata/orcl/emp_tb
8 rows selected.
SYS> SYS>
SYS>
SYS>
SYS> select dbms_metadata.get_ddl('TABLE','DEPARTMENTS','HR') from dual;
DBMS_METADATA.GET_DDL('TABLE','DEPARTMENTS','HR')
--------------------------------------------------------------------------------
CREATE TABLE "HR"."DEPARTMENTS"
( "DEPARTMENT_ID" NUMBER(4,0),
"DEPARTM
SYS> !ls
afiedt.buf ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql
Desktop ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql
ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql
ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql
ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql
SYS> !mkdir directory
SYS> !ls
afiedt.buf ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql
Desktop ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql
directory ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql
ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql
ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql
SYS> !
[oracle@localhost ~]$ ls
afiedt.buf ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql
Desktop ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql
directory ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql
ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql
ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql
[oracle@localhost ~]$ rm -rf directory/
[oracle@localhost ~]$ ls
afiedt.buf ex_07_18.sql lab_04_03.sql lab_05_04.sql lab_08_03.sql lab2_01_04.sql m_05_01.sql m_05_05b.sql q_dbf.sql test_hjs_03.sql
Desktop ex_07_22.sql lab_05_01_2.sql lab_06_01.sql lab2_01_01.sql lab2_02_01.sql m_05_02.sql m_05_06.sql q_log.sql test_hjs_04.sql
ex_06_12.sql ex_07_m.sql lab_05_01.sql lab_07_02.sql lab2_01_02.sql lab2_02_02.sql m_05_03.sql m_07_02.sql q_tbs.sql test_hjs_05.sql
ex_06_14.sql job_pkg.plb lab_05_02_2.sql lab_08_01.sql lab2_01_03.sql lab2_02_03a.sql m_05_04.sql oradiag_oracle test_hjs_01.sql test_hjs_06.sql
ex_07_16.sql job_pkg.sql lab_05_02.sql lab_08_02.sql lab2_01_04b.sql labs m_05_05a.sql over_load.sql test_hjs_02.sql
[oracle@localhost ~]$ mkdir reports_dir
[oracle@localhost ~]$ exit
exit
SYS> create directory reports_dir as '/home/oracle/reports_dir';
Directory created.
SYS> !ls /home/oracle/reports_dir
SYS> desc utl_file
PROCEDURE FCLOSE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN/OUT
ID BINARY_INTEGER IN/OUT
DATATYPE BINARY_INTEGER IN/OUT
BYTE_MODE BOOLEAN IN/OUT
PROCEDURE FCLOSE_ALL
PROCEDURE FCOPY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SRC_LOCATION VARCHAR2 IN
SRC_FILENAME VARCHAR2 IN
DEST_LOCATION VARCHAR2 IN
DEST_FILENAME VARCHAR2 IN
START_LINE BINARY_INTEGER IN DEFAULT
END_LINE BINARY_INTEGER IN DEFAULT
PROCEDURE FFLUSH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
PROCEDURE FGETATTR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCATION VARCHAR2 IN
FILENAME VARCHAR2 IN
FEXISTS BOOLEAN OUT
FILE_LENGTH NUMBER OUT
BLOCK_SIZE BINARY_INTEGER OUT
FUNCTION FGETPOS RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
FUNCTION FOPEN RETURNS RECORD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER OUT
DATATYPE BINARY_INTEGER OUT
BYTE_MODE BOOLEAN OUT
LOCATION VARCHAR2 IN
FILENAME VARCHAR2 IN
OPEN_MODE VARCHAR2 IN
MAX_LINESIZE BINARY_INTEGER IN DEFAULT
FUNCTION FOPEN_NCHAR RETURNS RECORD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER OUT
DATATYPE BINARY_INTEGER OUT
BYTE_MODE BOOLEAN OUT
LOCATION VARCHAR2 IN
FILENAME VARCHAR2 IN
OPEN_MODE VARCHAR2 IN
MAX_LINESIZE BINARY_INTEGER IN DEFAULT
PROCEDURE FREMOVE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCATION VARCHAR2 IN
FILENAME VARCHAR2 IN
PROCEDURE FRENAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SRC_LOCATION VARCHAR2 IN
SRC_FILENAME VARCHAR2 IN
DEST_LOCATION VARCHAR2 IN
DEST_FILENAME VARCHAR2 IN
OVERWRITE BOOLEAN IN DEFAULT
PROCEDURE FSEEK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN/OUT
ID BINARY_INTEGER IN/OUT
DATATYPE BINARY_INTEGER IN/OUT
BYTE_MODE BOOLEAN IN/OUT
ABSOLUTE_OFFSET BINARY_INTEGER IN DEFAULT
RELATIVE_OFFSET BINARY_INTEGER IN DEFAULT
PROCEDURE GET_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER VARCHAR2 OUT
LEN BINARY_INTEGER IN DEFAULT
PROCEDURE GET_LINE_NCHAR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER NVARCHAR2 OUT
LEN BINARY_INTEGER IN DEFAULT
PROCEDURE GET_RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER RAW OUT
LEN BINARY_INTEGER IN DEFAULT
FUNCTION IS_OPEN RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
PROCEDURE NEW_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
LINES BINARY_INTEGER IN DEFAULT
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER VARCHAR2 IN
PROCEDURE PUTF
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
FORMAT VARCHAR2 IN
ARG1 VARCHAR2 IN DEFAULT
ARG2 VARCHAR2 IN DEFAULT
ARG3 VARCHAR2 IN DEFAULT
ARG4 VARCHAR2 IN DEFAULT
ARG5 VARCHAR2 IN DEFAULT
PROCEDURE PUTF_NCHAR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
FORMAT NVARCHAR2 IN
ARG1 NVARCHAR2 IN DEFAULT
ARG2 NVARCHAR2 IN DEFAULT
ARG3 NVARCHAR2 IN DEFAULT
ARG4 NVARCHAR2 IN DEFAULT
ARG5 NVARCHAR2 IN DEFAULT
PROCEDURE PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER VARCHAR2 IN
AUTOFLUSH BOOLEAN IN DEFAULT
PROCEDURE PUT_LINE_NCHAR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER NVARCHAR2 IN
PROCEDURE PUT_NCHAR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER NVARCHAR2 IN
PROCEDURE PUT_RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILE RECORD IN
ID BINARY_INTEGER IN
DATATYPE BINARY_INTEGER IN
BYTE_MODE BOOLEAN IN
BUFFER RAW IN
AUTOFLUSH BOOLEAN IN DEFAULT
SYS> CREATE OR REPLACE PROCEDURE create_user (
2 p_user_name VARCHAR2, p_
3 BEGIN
4 END;
5 /
Warning: Procedure created with compilation errors.
SYS> ED
Wrote file afiedt.buf
1 CREATE OR REPLACE PROCEDURE create_user (
2 p_user_name VARCHAR2) IS
3 BEGIN
4 execute immediate 'CREATE USER ' || p_user_name || ' IDENTIFIED BY ' || p_user_name);
5* END;
SYS> /
Warning: Procedure created with compilation errors.
SYS> show error
Errors for PROCEDURE CREATE_USER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/86 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
. ( * @ % & = - + ; < / > at in is mod remainder not rem
return returning <an exponent (**)> <> or != or ~= >= <= <>
and or like like2 like4 likec between into using || bulk
member submultiset
The symbol "(" was substituted for ")" to continue.
SYS> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PROCEDURE create_user (
2 p_user_name VARCHAR2) IS
3 BEGIN
4 execute immediate 'CREATE USER ' || p_user_name || ' IDENTIFIED BY ' || p_user_name;
5* END;
SYS> /
Procedure created.
SYS> exec create_user
BEGIN create_user; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CREATE_USER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SYS> exec create_user('hjs');
PL/SQL procedure successfully completed.
SYS> desc dba_users;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SYS> select username from dba_users;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
JSHONG
HR
U2
HJS
U1
OUTLN
USERNAME
------------------------------
WMSYS
APPQOSSYS
DIP
ORACLE_OCM
15 rows selected.
SYS> set timing on
SYS> alter system set cursor_sharing = EXACT;
System altered.
Elapsed: 00:00:00.04
SYS> create tablespace query_data
2 datafile '/u01/app/oracle/oradata/orcl/query_data01.dbf' size 100m
3 autoextend on next 10m maxsize 500m;
Tablespace created.
Elapsed: 00:00:03.68
SYS> connect hr/hr
Connected.
HR> create table big_table (c1 number, c2 varchar2(200)) tablespace query_data;
Table created.
Elapsed: 00:00:00.06
HR> declare
2 i number;
3 str varchar2(200);
4 begin
5 for i in 1..100000 loop
6 str := 'insert into big_table values ('||i||', RPAD('||i||', 100, ''*''))';
7 execute immediate str;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.39
HR> truncate table big_table;
Table truncated.
Elapsed: 00:00:00.46
HR> declare
2 i number;
3 str varchar2(200);
4 begin
5 str:= 'insert into big_table values (:x1, :x2)';
6 for i in 1..100000 loop
7 execute immediate str using i, RPAD(i, 100, '*');
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.43
HR> set timing off
HR>