Oracle常用命令

Oracle使用及语法

 

--创建表空间

CREATE TABLESPACE TEST DATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\TEST.DBF' 

SIZE 10M AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO;

 

--创建用户、密码、指定表空间

CREATE USER GGZ IDENTIFIEDBY GGZ DEFAULT TABLESPACE TEST;

 

--查看所有表空间

SELECT * FROM DBA_TABLESPACES;

 

--查看所有用户

SELECT * FROM ALL_USERS;

SELECT * FROM DBA_USERS;

 

--创建用户权限

GRANT 权限管理

常用系统权限：CONNECT(基本的连接)，RESOURCE(程序开发)，DBA(数据库管理)。

常用数据对象权限：ALL ON数据对象名，SELECT ON 数据对象名，UPDATE ON 数据对象名，DELETE ON 数据对象名，INSERT ON 数据对象名，ALTER ON 数据对象名。

GRANT CONNECT, RESOURCE TO 用户名;

GRANT SELECT ON 表名 TO 用户名;

GRANT SELECT, INSERT, DELETE ON 表名 TO 用户名1, 用户名2;

GRANT CONNECT, RESOURCE TO GGZ;

 

--查看当前用户的缺省表空间

SELECT USERNAME, DEFAULT_TABLESPACE FROM USER_USERS;

--查看表空间对应的数据文件
SELECT
 T1.NAME,
 T2.NAME
  FROM V$TABLESPACE T1,
       V$DATAFILE T2
 WHERE
 T1.TS# = T2.TS#
 ORDER BY T1.NAME;

--查看当前用户的角色、系统权限、数据对象权限

--系统权限：针对Oracle系统操作而言，例如：登陆create session，创建表create table等；

--对象权限：是针对对象操作而言，例如：查询select，更新update等；

--角色权限：角色其实是特定系统权限和对象权限的组合，是一组权限的组合，便于对用户授权，Oracle内置了一些角色，如经常使用的connnet，reseource和dba等；

SELECT * FROM USER_ROLE_PRIVS;

SELECT * FROM USER_SYS_PRIVS;

SELECT * FROM USER_TAB_PRIVS;

 

--查看用户下所有的表

SELECT * FROM USER_TABLES;

--处理死锁

SELECT B.OWNER,
       B.OBJECT_NAME,
       S.USERNAME,
       L.OBJECT_ID,
       L.SESSION_ID,
       L.LOCKED_MODE,
       S.SERIAL#,
       L.ORACLE_USERNAME,
       L.OS_USER_NAME,
       L.PROCESS
  FROM V$LOCKED_OBJECT L, V$SESSION S, DBA_OBJECTS B
 WHERE L.SESSION_ID = S.SID
   AND B.OBJECT_ID = L.OBJECT_ID;

ALTER SYSTEM KILL SESSION '254,36701';

--取消正在执行的存储过程

select b.sid, b.SERIAL#
  from SYS.V_$ACCESS a, SYS.V_$session b
 where a.type = 'PROCEDURE'
   and (a.OBJECT like upper('%存储过程名%') or a.OBJECT like lower('%存储过程名%'))
   and a.sid = b.sid
   and b.status = 'ACTIVE';

alter system kill session 'sid,SERIAL#';

 

--创建自增表例子

CREATE TABLE EXAMPLE(

ID NUMBER(10) NOT NULL PRIMARY KEY,

NAME VARCHAR(16),

PHONE VARCHAR(16)

);

 

CREATE SEQUENCE EXAMPLE_SEQUENCE

INCREMENT BY 1     --每次加几个

START WITH 1          --从1开始计数

NOMAXVALUE                  --不设置最大值

NOCYCLE                  --一直累加，不循环

NOCACHE                 --不建缓冲区

 

CREATE TRIGGER "EXAMPLE_TRIGGER" BEFORE

INSERT ON EXAMPLE FOREACH ROW WHEN (NEW.ID IS NULL)

BEGIN

SELECT EXAMPLE_SEQUENCE.NEXTVALINTO : NEW.ID FROM DUAL;

END;

 

INSERT INTO EXAMPLE (NAME,PHONE) VALUES ('SEAN','13795296721');

INSERT INTO EXAMPLE (NAME,PHONE) VALUES ('LOTUS','15920862071');

 

--创建组合索引

ALTER TABLE T_DSACTIVITY ADD CONSTRAINT UQ_DSACTIVITYUNIQUE (ACTIVITYID, PASSPORTID)

 

--创建全文索引

create index IDX_RSCHDOC_11 on v_rschdoc_search(title)indextype is CTXSYS.CONTEXT;

DROP INDEX IDX_RSCHDOC_11

 

--创建外键例子

CREATE SEQUENCE MYSEQUENCE

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOCYCLE

NOCACHE

 

CREATE TABLE T_TEST (

T_ID NUMBER PRIMARY KEY

)

 

CREATE TABLE T_PERSON (

       P_ID NUMBERUNIQUE NOT NULL,

       P_NAMEVARCHAR2(20) NOT NULL,

       P_SEXVARCHAR2(2),

       CONSTRAINTSFK_TEST FOREIGN KEY(P_ID) REFERENCES T_TEST(T_ID),

       CONSTRAINTSCK_CHECK CHECK (P_SEX IN ('女','男',' '))

)

 

INSERT INTO T_TEST VALUES (MYSEQUENCE.NEXTVAL);

INSERT INTO T_PERSON VALUES (1, '李逍遥', '男');

INSERT INTO T_PERSON VALUES (2, '林月如', '女');

INSERT INTO T_PERSON VALUES (3, '赵灵儿', '女');

COMMIT;

 

--最高效的删除重复记录方法 (使用ROWID)

DELETE FROM EMP E

 WHERE E.ROWID> (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

 

--查看数据库所有表空间大小

SELECT DBF.TABLESPACE_NAME,

      DBF.TOTALSPACE "总量(M)",

      DBF.TOTALBLOCKS "总块数",

      DFS.FREESPACE "剩余总量(M)",

      DFS.FREEBLOCKS "剩余块数",

      (DFS.FREESPACE / DBF.TOTALSPACE) * 100 "空闲比例"

  FROM (SELECT T.TABLESPACE_NAME,

              SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,

              SUM(T.BLOCKS) TOTALBLOCKS

          FROM DBA_DATA_FILES T

         GROUP BY T.TABLESPACE_NAME) DBF,

       (SELECT TT.TABLESPACE_NAME,

              SUM(TT.BYTES) / 1024 / 1024 FREESPACE,

              SUM(TT.BLOCKS) FREEBLOCKS

          FROM DBA_FREE_SPACE TT

         GROUP BY TT.TABLESPACE_NAME) DFS

 WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME);

 

SELECT D.TABLESPACE_NAME,

       SPACE "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       SPACE -NVL(FREE_SPACE, 0) "USED_SPACE(M)",

       ROUND((1- NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",

      FREE_SPACE "FREE_SPACE(M)"

  FROM (SELECT TABLESPACE_NAME,

              ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

              SUM(BLOCKS) BLOCKS

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

              ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

          FROM DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL --临时表空间

SELECT D.TABLESPACE_NAME,

       SPACE "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

      USED_SPACE "USED_SPACE(M)",

      ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

      NVL(FREE_SPACE, 0) "FREE_SPACE(M)"

  FROM (SELECT TABLESPACE_NAME,

              ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

              SUM(BLOCKS) BLOCKS

          FROM DBA_TEMP_FILES

         GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

              ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,

              ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

          FROM V$TEMP_SPACE_HEADER

         GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

 

--查看当前用户每个表占用空间的大小：

SELECT SEGMENT_NAME AS "名称", SUM(BYTES) / 1024 / 1024 AS "使用大小(M)"

  FROM USER_EXTENTS

 GROUP BY SEGMENT_NAME

 ORDER BY 2 DESC

--查看当前用户每个表占用空间情况和对应索引占用情况：

SELECT O.TABLE_NAME 表名,
       O.NUM_ROWS 行数,
       O.SPACEREAL 实际使用空间,
       O.SPACEOCCUPY 实际占用空间,
       O.SPACEREAL / O.SPACEOCCUPY 使用率,
       P.INDEX_NAME 索引,
       P.SPACEOCCUPY 索引占用空间
  FROM (SELECT T.TABLE_NAME,
               T.NUM_ROWS,
               T.NUM_ROWS * T.AVG_ROW_LEN / 1024 / 1024 SPACEREAL,
               K.SPACEOCCUPY
          FROM USER_TABLES T,
               (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 AS SPACEOCCUPY
                  FROM USER_EXTENTS N
                 GROUP BY SEGMENT_NAME) K
         WHERE T.TABLE_NAME = K.SEGMENT_NAME) O,
       (SELECT T.INDEX_NAME, T.TABLE_NAME, K.SPACEOCCUPY
          FROM USER_INDEXES T,
               (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 AS SPACEOCCUPY
                  FROM USER_EXTENTS M
                 GROUP BY SEGMENT_NAME) K
         WHERE T.INDEX_NAME = K.SEGMENT_NAME) P
 WHERE O.TABLE_NAME = P.TABLE_NAME
 ORDER BY 4 DESC

 

--logsegment段过大，查看这些lobsegment属于哪些表的哪些字段

SELECT A.TABLE_NAME,

      A.COLUMN_NAME,

      B.SEGMENT_NAME,

       B.SEGMENT_TYPE,

      B.TABLESPACE_NAME,

       B.BYTES /1024 / 1024,

       B.BLOCKS,

       B.EXTENTS

  FROM USER_LOBSA, USER_SEGMENTS B

WHERE A.SEGMENT_NAME = B.SEGMENT_NAME

ORDER BY B.BYTES DESC;

 

--缩小表的大小：

alter table TSMS_SEND_ARCHIVE enable row movement;

alter table TSMS_SEND_ARCHIVE shrink space;

alter table TSMS_SEND_ARCHIVE disable row movement;

 

--查看表空间里面有哪些表：

SELECT * FROM USER_TABLES T WHERE T.TABLESPACE_NAME ='MESSAGE_TABLE';

SELECT * FROM USER_INDEXES T WHERE T.TABLESPACE_NAME ='MESSAGE_INDEX';

--扩展表空间大小

以sysdba角色登录数据库

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct10 15:53:45 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and RealApplication Testing options

 

## 执行以下的语句检查表空间属性：主要查看是否为BIG类型

SQL> select * from v$tablespace;

TS#              NAME                  INC       BIG       FLA ENC

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

0                   SYSTEM              YES       NO       YES

1                  UNDOTBS1         YES       NO       YES

2                  SYSAUX              YES       NO       YES

4                  USERS                YES       NO       YES

3                  TEMP                  NO         NO       YES

6                  UBSSDIC             YES       NO       YES

## 执行以下语句查看表空间对应的数据文件：

SQL> select ts#, name from v$datafile;

TS#       NAME

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

0    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\SYSTEM01.DBF

1    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\UNDOTBS01.DBF

2    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\SYSAUX01.DBF

4    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\USERS01.DBF

6    D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC.DBF

 

## 对大文件类型（BIG字段的值为YES）的表空间，只能扩大原有的数据文件

## 对非大文件类型（BIG字段的值为NO）的表空间，例如UBSSDIC，有两种方式扩大表空间：

1、增加新的数据文件

SQL> alter tablespace UBSSDIC add datafile'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC_02.DBF' size 5 M;

Tablespace altered.

增加后查看数据文件的增加情况：

SQL> select ts#, name from v$datafile;

TS#       NAME

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

0    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\SYSTEM01.DBF

1    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\UNDOTBS01.DBF

2    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\SYSAUX01.DBF

4    D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\USERS01.DBF

6    D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC.DBF

6    D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC_02.DBF

 

2、扩大原有的数据文件

SQL> alter database datafile'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC_02.DBF' resize 10 M;

Database altered.

扩大D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC_02.DBF到10M后查看：

SQL> select ts#, round(bytes/1024/1024,2),name from v$datafile order by ts#;

TS#       ROUND(BYTES/1024/1024)   NAME

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

0     480              D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\SYSTEM01.DBF

1     25                D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\UNDOTBS01.DBF

2     260              D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\SYSAUX01.DBF

4     5                  D:\ORACLE\PRODUCT\10.2.0\ORADATA\UBSSDIC\USERS01.DBF

6     10                D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC.DBF

6     10                D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UBSSDIC_02.DBF