Oracle command-line tools basic operations and SQL commands
1. Basic Concepts
1.1. Data type
Basic data Type (number,varchar2,date)
O Racel supports the following internal data types:
VARCHAR2 variable length string with a maximum of 2000 characters.
Number numeric.
Long to longer character data, up to 2G bytes.
Date type.
RAW binary data, up to 255 bytes in length.
Long RAW variable-length binary data with a maximum length of 2G bytes.
ROWID 26 binary string that represents the unique address of the table row.
Char fixed long character data, up to a maximum of 255.
2. Sql*plus
This is the most common and best-used SQL command execution tool provided by Oracle.
2.1. Database System Management
2.1.1. Login
C:> SQLPLUSW
C:> Sqlplus/nolog
Sql> Conn Username/[email protected]
For example, the system logon
2nd Page Total 9 page
Sql> Conn System/[email protected]
If you want to line some commands that only SYSDBA can execute, you must be logged on with SYSDBA privileges:
Sql> Conn Sys/[email protected] as Sysdba
2.1.2. Creating table Spaces
You must have create tablespace privilege users to create table spaces, such as system
and SYS users.
sql> Conn [email protected]
Please enter a password:
is connected.
sql> Create tablespace ts_test datafile '/data2/oradata/ciis/ts_test01.dbf ' size
10m;
The tablespace has been created.
2.1.3. Adding data files
Sql> alter tablespace ts_test add datafile '/data2/oradata/ciis/ts_test02.dbf ' size
10m;
Table space has changed.
2.1.4. viewing table space size
Sql> DESC Dba_data_files
is the name empty? Type
-------------------- -------- --------------
file_name VARCHAR2 (513)
FILE_ID number
Tablespace_name VARCHAR2 (30)
BYTES number
BLOCKS number
3rd Page Total 9 page
STATUS VARCHAR2 (9)
RELATIVE_FNO number
Autoextensible VARCHAR2 (3)
MaxBytes number
Maxblocks number
Increment_by number
User_bytes number
User_blocks number
Sql> SELECT tablespace_name,sum (BYTES)/1024/1024 M
2 from Dba_data_files
3 GROUP by Tablespace_name;
Tablespace_name M
-------------------- ----------
DEVELOP1 8000
DEVELOP2 14336
Developindex 4106
Drsys 20
EXAMPLE 145.625
INDX 25
ODM 20
SYSTEM 1024
TOOLS 10
Ts_ci_13 4094
Ts_ci_32 4094
Ts_ci_33 2047
Ts_ii_13 2047
Ts_ii_32 2047
ts_pi_1301 2047
4th Page Total 9 page
ts_pi_1302 2047
ts_pi_3201 2047
Ts_test 20
UNDOTBS1 5048
USERS 25
XDB 38.125
21 rows have been selected.
Sql> SELECT tablespace_name,sum (BYTES)/1024/1024 M
2 from Dba_data_files
3 WHERE tablespace_name= ' ts_test '
4 GROUP by Tablespace_name;
Tablespace_name M
-------------------- ----------
Ts_test 20
2.1.5. View free (remaining) Table space size
Sql> desc Dba_free_space
is the name empty? Type
---------------------- -------- ---------------
Tablespace_name VARCHAR2 (30)
FILE_ID number
BLOCK_ID number
BYTES number
BLOCKS number
RELATIVE_FNO number
5th Page Total 9 page
Sql> SELECT tablespace_name,sum (BYTES)/1024/1024 M
2 from Dba_free_space
3 WHERE tablespace_name= ' ts_test '
4 GROUP by Tablespace_name;
Tablespace_name M
-------------------- ----------
Ts_test 19.6875
2.1.6. Creating a new user
Sql> create user test identified by test default tablespace ts_test Temporary
Tablespace temp;
User has created
2.1.7. privileges to user roles
Sql> Grant Connect,resource to test;
Authorization is successful.
2.2. User Data Objects
2.2.1. viewing the current User table name
Sql> select * from tab;
Tname Tabtype Clusterid
------------------------------ ------- ----------
BONUS TABLE
CC TABLE
DEPT TABLE
6th Page Total 9 page
EMP TABLE
Emp_iot TABLE
Salgrade TABLE
6 rows have been selected.
2.2.2. Creating a data table
Sql> CREATE TABLE Book (
2 BookID Number (18),
3 BookName VARCHAR2 (+) not NULL,
4 author VARCHAR2 (40),
5 Price Number (6,2)
6);
The table is created.
2.2.3. Creating an Index
Sql> CREATE INDEX idx_book_bookid on book (bookname);
The index has been created.
2.2.4. Creating a PRIMARY KEY constraint
sql> ALTER TABLE book ADD CONSTRAINT pk_book_bookid PRIMARY
KEY (BookID);
The table has changed.
2.2.5. Show Table Structure
Sql> DESC Book
is the name empty? Type
------------------------------ -------- ----------------------
7th Page Total 9 page
BOOKID not NULL number (18)
BookName not NULL VARCHAR2 (80)
AUTHOR VARCHAR2 (40)
Price Number (6,2)
2.2.6. Viewing the index of a table
sql> Column index_name format A30
Sql> Select table_name, index_name from User_indexes;
TABLE_NAME Index_name
------------------------------ ------------------------
Book Idx_book_bookname
Book Pk_book_bookid
2.2.7. Viewing index columns
Sql> Select table_name, index_name, COLUMN_NAME, column_position
From User_ind_columns;
TABLE_NAME INDEX_NAME column_name Column_position
-------------- ------------------- -------------- ---------------
Book Pk_book_bookid BOOKID 1
Book Idx_book_bookname BookName 1
2.2.8. View data segment Space size
Data segments include tables, indexes, partitions, and so on.
Sql> desc user_segments
is the name empty? Type
-------------------- -------- ------------------------
8th Page Total 9 page
Segment_name VARCHAR2 (81)
Partition_name VARCHAR2 (30)
Segment_type VARCHAR2 (18)
Tablespace_name VARCHAR2 (30)
BYTES number
BLOCKS number
Extents number
Initial_extent number
Next_extent number
Min_extents number
Max_extents number
Pct_increase number
Freelists number
Freelist_groups number
Buffer_pool VARCHAR2 (7)
Sql> select Segment_name,segment_type,bytes from user_segments;
Segment_name Segment_type BYTES
------------------------------ --------------- ---------
Book TABLE 65536
Idx_book_bookname INDEX 65536
Pk_book_bookid INDEX 65536
9th Page Total 9 page
2.2.9. View table Space Size
Sql> Select Segment_name,segment_type,bytes from User_segments where
Segment_type= ' TABLE ';
Segment_name Segment_type BYTES
------------------------------ ---------------- --------
Book TABLE 65536
Oracle command-line tools basic operations and SQL commands
