oracle常用sql语句集合

创建临时表空间:

create temporary tablespace ppatemp
tempfile 'D:\oracleDB\oradata\ppatb\ppa_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 

创建表空间:

create tablespace ppa
logging  
datafile 'D:\oracleDB\oradata\ppatb\ppa.dbf' 
size 100m  
autoextend on  
next 50m maxsize 20480m  
extent management local;

创建用户指定表空间:

create user admin identified by admin
default tablespace ppa
temporary tablespace ppatemp;

为用户授权:

GRANT 
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE，
    CREATE SEQUENCE 
　　TO admin;

修改用户表空间:

alter user admin default tablespace ppa quota unlimited on ppa;

创建表和索引并指定自增长列:

CREATE TABLE CUST_MAT(
  CM_NO CHAR(50),
  CM_NAME CHAR(200),
  CM_PART CHAR(50),
  CM_SMNAME CHAR(100),
  CM_MEM VARCHAR(200),
  CM_ALS VARCHAR(200),
  CM_MODEL VARCHAR(500),
  CM_MODELEN VARCHAR(800),
  CM_LEVEL SMALLINT,
  CM_TYPE CHAR(10),
  CM_SOUR VARCHAR(200),
  CM_GRWT DECIMAL(18,5),
  CM_NET DECIMAL(18，5),
  CM_PR DECIMAL(18，5),
  CM_UNIT CHAR(10),
  CM_DRAWNO INT,
  CM_BRAND CHAR(200),
  PRIMARY KEY(CM_NO, CM_PART),
  FOREIGN KEY(CM_NO) REFERENCES ORG_MSTR(ORG_NO)
);
CREATE SEQUENCE CMPO_NO_SEQUENCE INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;//自增长列
CREATE INDEX CM_NO_INDEX ON CUST_MAT(CM_NO);//索引
CREATE INDEX CM_PART_INDEX ON CUST_MAT(CM_PART);
CREATE INDEX CM_SMNAME_INDEX ON CUST_MAT(CM_SMNAME);
CREATE INDEX CM_MODEL_INDEX ON CUST_MAT(CM_MODEL);
CREATE INDEX CM_PR_INDEX ON CUST_MAT(CM_PR);
CREATE INDEX CM_DRAWNO_INDEX ON CUST_MAT(CM_DRAWNO);

oracle分页(注意三层关系，否则容易出bug)

select um1.* from (select um.*,rownum as rn from (select * from user_mstr where 1 =1 and user_name like '%%' order by id desc) um where rownum <=18) um1 where um1.rn >=2

select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数


修改最大连接数:
alter system set processes = 300 scope = spfile;


删除用户:
drop user common cascade;


删除表空间:
alter tablespace xx offline;
drop tablespace xxx including contents and datafiles;

授权:

grant ALL on shangyon.A to admin;

加字段注释:

comment on column ANAPR_LIST.anapr_date
  is '网上收集时间';

oracle实现类似mysql的主键自增长:

假设有表A，其主键为ID，首先建立递增序列SEQ_A:

create sequence SEQ_A

increment by 1

start with 1

minvalue 1 nomaxvalue

nocylce

然后建立触发器，当有数据插入表A 时，使用序列为其去的递增的主键值

create trigger TRG_A before insert on A
for each row
begin
select SEQ_A.nextval into :new.ID from dual;
end;

至此，创建完成！

备份语句参考:（直接在服务器cmd运行）
exp system/system@orcl  file=d:\ppabackup.dmp full=y
恢复语句参考：
先删除本地表空间(如果有)
再创建表空间和用户(tablespace.txt)
imp system/system@orcl file=d:\ppabackup.dmp full=y

-----------------------------修改用户密码

alter user kingdee account unlock;


alter user kingdee identified by "shangyon";

----------------------------------------完全授权

DECLARE   
      p_grantee       VARCHAR2   (30)   :=   'shangyon';   
BEGIN   
      FOR   rec   IN   (SELECT   object_name,   object_type   
                                        ,   DECODE   (object_type   
                                                        ,   'TABLE ',   'ALL '   
                                                        ,   'VIEW ',   'SELECT '   
                                                        ,   'EXECUTE '   
                                                          )   grant_type   
                              FROM       user_objects)   
      LOOP   
            BEGIN   
                  EXECUTE   IMMEDIATE   'grant   all   on   '   ||   rec.object_name   ||   '   to   '   ||   p_grantee;   
            EXCEPTION   
                  WHEN   OTHERS   
                  THEN   
                        dbms_output.put_line   (SUBSTR   (SQLERRM,   1,   240));   
            END;   
      END   LOOP;   
END;  

----------------------------------------------------dblink

创建公开数据库链接
CREATE PUBLIC DATABASE LINK “数据库链接别名”
CONNECT TO “登陆用户” IDENTIFIED BY “登陆密码”
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = IP)(PORT = 1521)))(CONNECT_DATA =(SID = SID)))';

------查询远程数据库:

SELECT * FROM 远程表名@数据库链接别名

查询数据库当前进程的连接数：select count(*) from v$process;
查看数据库当前会话的连接数：select count(*) from v$session;
查看数据库的并发连接数：select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况：select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数：select value from v$parameter where name = 'processes';
或者：show parameter processes;
修改数据库允许的最大连接数：alter system set processes = 300 scope = spfile;