oracle常用sql语句集合 ﹏ヽ暗。殇╰゛Y 2022-08-01 13:35 202阅读 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;
还没有评论,来说两句吧...