oracle常用命令

--登录：
sys/ken@orcl as sysdba

1、建立表空间、授予权限

/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace user_temp
tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf‘
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

/*第2步：创建数据表空间 */
create tablespace user_data
logging
datafile ‘D:\oracle\oradata\Oracle9i\user_data.dbf‘
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

/*第3步：创建用户并指定表空间 */
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;

/*第4步：给用户授予权限 */
grant connect,resource,dba to username

-------------------------------------------------------------------------------------------------------------

2、树关联

start with
connect by prior
说明：
a、prior：是单一操作符，放在列名的前面，等号左右均可； 放在父 ID 就是 寻找 祖先节点 ，放到本身 ID就是寻找 子节点 ；
b、connect_by_root：是单一操作符，返回当前层的最顶层节点；
c、connect_by_isleaf：是伪列，判断当前层是否为叶子节点，1代表是，0代表否；
d、level：是伪列，显示当前节点层所处的层数；
e、SYS_CONNECT_BY_PATH：是函数，显示当前层的详细路径。

-------------------------------------------------------------------------------------------------------------

3、查询表名及表注释
SELECT tb.table_name,cl.comments
FROM user_tab_comments cl,user_tables tb
WHERE 1=1
AND tb.table_name like ‘F_STA_PRJCOST_%‘
AND cl.table_name = tb.table_name

-------------------------------------------------------------------------------------------------------------

4、当前用户拥有的表 

select table_name from user_tables; //当前用户拥有的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner=‘用户名

-------------------------------------------------------------------------------------------------------------

5、字段，注释
SELECT t.TABLE_NAME,c.COLUMN_NAME,cc.comments
FROM USER_TABLES t,user_tab_columns c,user_col_comments cc
WHERE t.TABLE_NAME LIKE ‘F_PRJCOST_%‘
AND t.TABLE_NAME = c.TABLE_NAME
AND c.COLUMN_NAME = cc.column_name
AND cc.comments LIKE ‘施工单位%‘
;

-------------------------------------------------------------------------------------------------------------

6、修改列名
SELECT
t.TABLE_NAME,c.comments,
‘ALTER TABLE ‘|| t.TABLE_NAME ||‘ RENAME COLUMN projpart_id TO project_level_id ;‘
FROM USER_TABLES t,USER_TAB_COMMENTS c
WHERE t.TABLE_NAME LIKE ‘F_PRJCOST_%‘
AND t.TABLE_NAME = c.table_name
;
alter table table_name rename column col_old to col_new
增加列名
ALTER TABLE table_name
ADD column_name datatype
删除列名
ALTER TABLE table_name
DROP COLUMN column_name;
修改列长度：
ALTER TABLE "BIDWSTAGING_USR"."F_STA_PRJCOST_SUMMARY" MODIFY ("PROJECT_NAME" varchar2(512));

SELECT cl.comments,tb.table_name,
REPLACE(tb.table_name,‘F_PRJCOST‘,‘F_PRJCT‘),
‘CREATE TABLE ‘|| REPLACE(tb.table_name,‘F_PRJCOST‘,‘F_PRJCT‘)||‘ AS SELECT * FROM ‘||tb.table_name||‘ ;‘,--建表
‘DROP TABLE ‘|| REPLACE(tb.table_name,‘F_PRJCOST‘,‘F_PRJCT‘)||‘ ;‘,--删表
‘COMMENT ON COLUMN ‘||tb.table_name||‘.PROJECT_LEVEL_ID IS ‘‘项目结构ID‘‘;‘--修改注释
FROM user_tab_comments cl,user_tables tb
WHERE 1=1
AND tb.table_name like ‘F_PRJCT_%‘
AND cl.table_name = tb.table_name;
添加表注释，列注释：
COMMENT ON TABLE D_ORG_N IS ‘组织维表‘;
COMMENT ON COLUMN F_PRJCOST_ADJ_PRICE_SETTLE.PROJECT_LEVEL_ID IS ‘项目结构ID‘;

-------------------------------------------------------------------------------------------------------------

7、授权
SELECT tb.table_name,cl.comments,
‘GRANT SELECT ,INSERT,DELETE,UPDATE,ALTER ON ‘|| tb.table_name ||‘ TO BIDWSTAGING_USR;‘
FROM user_tab_comments cl,user_tables tb
WHERE 1=1
AND tb.table_name like ‘F_PRJCOST_%‘
AND cl.table_name = tb.table_name
;

-------------------------------------------------------------------------------------------------------------
8、同义词
SELECT tb.table_name,cl.comments,
‘CREATE OR REPLACE SYNONYM ‘|| tb.table_name ||‘ FOR BIDW_USR.‘||tb.table_name ||‘;‘
FROM user_tab_comments cl,user_tables tb
WHERE 1=1
AND tb.table_name like ‘F_PRJCOST_%‘
AND cl.table_name = tb.table_name

-------------------------------------------------------------------------------------------------------------

9、锁定用户的方法：
SQL> alter user test account lock;
--Oracle用户解锁的方法：
SQL> alter user test account unlock;
-------------------------------------------------------------------------------------------------------------
10、不同用户的truncate 权限
grant drop any table to bidwstaging_usr;
grant select any dictionary to bidwstaging_usr;

-------------------------------------------------------------------------------------------------------------

11、新建分区

CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)