一、 常用Query
# 查看表名称
select table_name, tablespace_name from user_tables;
二、 sqlplus的使用
2.1 简介
2.2 用法
# 连接数据库
sqlplus username/password@hostname/dbname
# 执行sql(sql文件末尾要加exit)
sqlplus username/password@hostname/dbname @schema.sql
# 执行简易的sql命令
sql="
drop table t1;
drop table t2;
exit
"
echo "${sql}" | sqlplus username/password@hostname/dbname
三、sqlldr的使用
3.1 简介
3.2 用法
# 需要首先准备item.ctl文件，示例如下
# table name一定要大写
options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=10)
load data
infile '/home/katsura/data/item.csv'
append into table "ITEM"
fields terminated by ';'
TRAILING NULLCOLS
(
item_sk ,
current_price ,
wholesale_cost ,
brand_id ,
class_id ,
category_id ,
manufact_id
)
sqlldr username/password@hostname/dbname control=item.ctl direct=true parallel=true
四、Explain用法
4.1 简介
4.2 用法
-- explain sql命令
explain plan set statement_ID='plan1' for select * from item;
--- 控制输出格式
column Rows format 99999999
column Plan format a50
-- 从plan_table中查询plan
SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'plan1'
ORDER BY id;
