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 sessionsshow parameter processesalter 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再进行启动
shutdown immediateconn [email protected] as sysdbastartup
5.连接到oracle
sqlplus /nolog
conn [email protected] 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