oracl基本sql语句

1.分页查询：

select rn, tt.* from  
  (select rownum rn,t.* from    
    (select * from tablename order by table_id) t)  tt
      where rn between 1 and 5;

2.创建oracle序列

CREATE SEQUENCE university_seq;
insert into university() values(university_seq.nextval,);
select university_seq.nextval from dual;
select university_seq.currval from dual;

3.修改oracle进程数

show parameter sessions
show parameter processes
alter system set processes =200 scope=spfile;
alter system set sessions=220 scope=spfile;
alter system set processes =900 scope=spfile;
alter system set sessions=990 scope=spfile;
sessions=processes*1.1

4.重启oracle服务
关掉oracle服务后，需要再次连接到oracle再进行启动

oracle监听命令：lsnrctl status，lsnrctl start和lsnrctl stop
1. 停应用
2. 停监听
3. shutdown immediate

shutdown immediate
conn /@instancename as sysdba
startup

5.连接到oracle
sqlplus /nolog
conn /@instancename as sysdba

6. Oracle查询用户权限和角色

grant/revoke dba,debug any procedure,debug connect session from userName;

6.1 oracle用户查看自己的权限和角色

     select * from user_tab_privs;
    select * from user_role_privs;

6.2 sys用户查看任一用户的权限和角色

     select * from dba_tab_privs;
    select * from dba_role_privs; 

6.3查看用户名与密码
select username,password from dba_users;

6.4 //修改用户口令 格式 alter user 用户名 identified by 新密码;
alter user system identified by manager;

6.5 创建用户并赋权

create user userName identified by userName ;
grant dba,resource,connect to userName ;
drop user userName cascade;

6.6创建表空间

create temporary tablespace temptps tempfile 'd:\temptps.dbf' size 500M autoextend on next 50M maxsize unlimited;

create tablespace tps datafile 'd:\tps.dbf' size 20000M autoextend on next 50M maxsize unlimited;

create tablespace tpsextend datafile 'd:\tpsextend.dbf' size 10000M autoextend on next 50M maxsize unlimited;

create user userName identified by userPwd DEFAULT TABLESPACE tps TEMPORARY TABLESPACE temptps quota unlimited on tps;

alter user userName quota unlimited on tpsextend;

7. 修改列
7.1 增加列
alter table tableName add (columnName varchar2(10));
7.2 重命名列
alter table tableName rename column old_columnName to new_columnName;
7.3 删除列
alter table tableName drop column columnName
7.4 删除约束
alter table tbl_roundup_code drop constraint constraintName;
7.5 添加主键
alter table tbl_code modify(primary key(code,code_type));
7.6 添加外键
ALTER TABLE tableName_A ADD CONSTRAINT CONSTRAINT_NAME FOREIGN KEY (colunmName) REFERENCES tableName_B (ID)

8. 查看所有约束

select a.owner 外键拥有者, a.table_name 外键表,a.constraint_name 约束名称,
 substr(c.column_name,1,127) 外键列, b.owner 主键拥有者, 
b.table_name 主键表, substr(d.column_name,1,127) 主键列 
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d 
    where a.r_constraint_name=b.constraint_name and a.constraint_type='R' 
    and b.constraint_type='P' and a.r_owner=b.owner and a.constraint_name=c.constraint_name 
    and b.constraint_name=d.constraint_name and a.owner=c.owner 
    and a.table_name=c.table_name and b.owner=d.owner and b.table_name=d.table_name