Oracle CRUD

各种常用oralce语句

查看ORACLE版本

SQL> SELECT * FROM V$VERSION;

查看本地数据库名：

SQL> select name from v$database;

查看表空间

SQL> SELECT * FROM V$tablespace;

设置输出的行宽

SQL> set linesize 100;

设置输出的分页大小

SQL> set pagesize 20000;

查看用户的某个对象的类型：

SELECT object_name, object_type FROM user_objects WHERE object_name = '对象名'

查看/设置本机字符集(导入乱码时可通过查看本机字符集和数据源字符集是否相同，来确定是否是因为字符集的原因导致乱码的发生；再通过设置本机字符集和数据源字符集相同，来解决乱码问题)：

---查看字符集
select * from nls_database_parameters where parameter in('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

---设置机器字符集
SHUTDOWN   IMMEDIATE;       
STARTUP   MOUNT;   
ALTER   SYSTEM   ENABLE   RESTRICTED   SESSION;   
ALTER   SYSTEM   SET   JOB_QUEUE_PROCESSES=0;   
ALTER   DATABASE   OPEN;   
alter database character set INTERNAL_USE ZHS16GBK;  --这个是字体集名称
SHUTDOWN   IMMEDIATE;         
STARTUP;

给用户解锁:

SQL>alter user scott account unlock;

取得ORACLE当前连接的登陆用户名

SQL>select user from dual;
SQL>show user;

查看所有的用户

SQL> SELECT USERNAME FROM DBA_USERS;

查看数据库内的所有角色  

selec * from dba_roles;

查看所有角色所拥有的系统级权限：

select * from role_sys_privs

查看所有角色所拥有的表级权限：

select * from role_tab_privs;

查询 Oracle 当前登录用户权限

SQL> select * from user_sys_privs;

查询 Oracle 当前登录用户角色

select * from user_role_privs;

查看系统中所有被授予的角色（角色也可能授予给角色,这里包括授予给用户和角色的）：

select * from dba_role_privs;

查看系统中所有被授予的系统权限（包括授予给用户和角色的）：

select * from dba_sys_privs;

查看系统中所有被授予的表级权限（包括授予给用户和角色的）：

select * from dba_tab_privs;

查看数据库当前所有session：

SELECT * FROM v$session

kill session(sid,serial#均为v$session中字段)：

alter system kill session 'sid,serial#';

查询数据库中当前哪些对象被锁定(通过OBJECT_ID字段查看all_objects视图得到锁定对象的具体信息；通过SESSION_ID字段查看v$session视图得到锁定该对象的session的具体信息)：

select * from V$LOCKED_OBJECT

查看数据库中哪些session在等待中：

SELECT * FROM v$session_wait;

解锁数据库中被锁定的对象：

select OBJECT_ID,SESSION_ID,os_user_name from v$locked_object:
SELECT * from all_objects where object_id = <OBJECT_ID>; (查看锁定的哪个Object)
select SID,SERIAL# from v$session where sid = <SESSION_ID>; (查看被哪个session锁定)
alter system kill session '<SID>,<SERIAL#>'; (结束该session,则锁解之)

查询该用户拥有哪些数据库对象,对象包括表、视图、存储过程、触发器、包、索引、序列等。

SQL> select object_name from user_objects;

查询当前用户拥有哪些表

SQL> select table_name from user_tables;

查询当前用户拥有哪些视图

SQL> select view_name from user_views;

查询当前用户拥有哪些触发器

SQL> select trigger_name from user_triggers;

查询当前用户拥有哪些序列

SQL> select sequence_name from user_sequences;

查看已定义的触发器、存储过程等的内容

SQL> select * from user_source;
SQL> select text from user_source;

删除主键：
1 对没有名字的主键约束：

alter table usertest drop primary key;

2 对有名字的主键约束可以这样：
  a 查出主键约束的名字

SELECT * from user_cons_columns where table_name='usertest';

  b 取消主键约束(假设主键constraint的名字叫USERTEST_PK)

alter table usertest drop constraint USERTEST_PK;

创建主键

ALTER TABLE students
  ADD CONSTRAINT pk_students 
  PRIMARY KEY (student_id) USING INDEX ;

创建Unique约束

ALTER TABLE students
  ADD CONSTRAINT uk_students_license 
  UNIQUE (state, license_no) USING INDEX ;

创建Check约束

ALTER TABLE students ADD CONSTRAINT ck_students_st_lic
   CHECK ((state IS NULL AND license_no IS NULL) OR
          (state IS NOT NULL AND license_no is NOT NULL));

创建外键约束

ALTER TABLE students
  ADD CONSTRAINT fk_students_state
  FOREIGN KEY (state) REFERENCES state_lookup (state);

查看对象的DDL定义：

SELECT DBMS_METADATA.get_ddl('对象类型','对象名称') FROM DUAL;

对象类型为TABLE/TRIGGER/VIEW/PACHAGE/PROCEDURE等用户ojbect；对象名称为须查看其DDL定义的表/触发器/视图/包/存储过程等某对象的名字；注意get_ddl方法的两个参数必须都为大写，否则会报ORA-31603或ORA-31600。
例子：

---查看存储过程定义：
SELECT dbms_metadata.get_ddl('PROCEDURE','LOAD2160PO') FROM dual;
---查看包及包中存储过程定义：
SELECT DBMS_METADATA.get_ddl('PACKAGE','PLODEMAND') FROM DUAL;

修改表名：

ALTER TABLE <TABLE_NAME> RENAME TO  <NEW_TABLE_NAME>

修改列名：

ALTER TABLE <TABLE_NAME> RENAME COLUMN <COL_NAME> TO <NEW_COL_NAME>

注意：rename只能修改自己schema下面的表