oracle常用sql语句集合

﹏ヽ暗。殇╰゛Y 2022-08-01 13:35 199阅读 0赞

创建临时表空间:

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;