SELECT * FROM v$instance; --查看数据库实例名 show parameter instance_name;
select instance from v$thread;

-- show parameter是oracle的命令，不是标准SQL语句
-- 可以在sqlplus或者pl/sql dev的命令窗口执行
-- show parameter aaaa;等价于SELECT * FROM v$parameter WHERE name like '%aaaa%';
SELECT * FROM v$parameter WHERE name like '%name%'; --等价于show parameter name;
select * from v$parameter where name like '%db_domain%'; --查询数据库域名


select username from all_users where username like '%SCOTT%';
drop user SCOTT cascade;
commit;

-- ERROR at line 1:
-- ORA-01940: cannot drop a user that is currently connected

select 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';' as KILLER from v$session where username='SCOTT';
-- KILLER
-- ALTER SYSTEM KILL SESSION '363,35';
-- ALTER SYSTEM KILL SESSION '364,51';
commit;

select * from dba_roles where role like '%CONNECT%';
drop role CONNECT;
commit;

select * from dba_tablespaces where tablespace_name like 'EXAMPLE';
drop tablespace EXAMPLE including contents and datafiles cascade constraints ;
-- including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数。
-- including datafiles 删除表空间中的数据文件。
-- cascade constraints 同时删除 tablespace 中表的外键参照。


-- 如何创建dblink和视图
-- http://docs.oracle.com/database/121/SQLRF/statements_5006.htm#i2061505
-- 如果需要创建全局 DBLink，则需要先确定用户有创建 dblink 的权限：
select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

-- 如果没有，则需要使用 sysdba 角色给用户赋权：
grant create public database link to dbusername;

-- 如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。
create /* public */ database link dblink1
connect to dbusername identified by dbpassword
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

-- 创建dblink后，就可以直接在dblink上创建视图
create or replace view cptp as (select SJDH from dbusername.cptp@dblink1); drop view cptp;


-- 锁表查询SQL
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;

-- 解除锁表
alter system kill session 'sid, serial#';


-- 备份某个表
create table new_table as select * from old_table;


-- 查看数据库是否在rac环境的集群中的
show parameter cluster_database;
select * from v$parameter where name = 'cluster_database';


-- 列操作
-- 增加和修改列不需要加关键字COLUMN
-- 删除单列的话，一定要加COLUMN，删除多列的时候，不能加COLUMN关键字

-- 增加一列
alter table emp4 add test varchar2(10);
-- 修改一列
alter table emp4 modify test varchar2(20);
-- 删除一列
alter table emp4 drop column test;
-- 增加多列
alter table emp4 add (test varchar2(10),test2 number);
-- 修改多列
alter table emp4 modify (test varchar2(20),test2 varchar2(20));
-- 删除多列
alter table emp4 drop (test,test2);


-- Windows下以管理员身份启动数据库
net start oracleserviceorcl -- 后面的orcl是你安装的数据库实例名
net start oracleoradb11g_home1tnslistener --非必须

-- linux下以sysdba用户登录，然后启动数据库
sqlplus / as sysdba
startup

-- sqlplus登陆方式
sqlplus / as sysdba --以操作系统权限认证的oracle sys管理员登陆

sqlplus /nolog
conn / as sysdba --以操作系统权限认证的oracle sys管理员登陆


sqlplus sys/password@orcl as sysdba --以sys用户登陆必须使用as sysdba

sqlplus /nolog --不在cmd或者teminal当中暴露密码的登陆方式
conn sys/password as sysdba


sqlplus --不显露密码的方式登陆
Enter user-name：sys
Enter password：password as sysdba --以sys用户登陆的话 必须要加上as sysdba子句

sqlplus scott/tiger@orcl --非管理员用户登陆


desc v$database; --查询v$database数据库的表结构



--在sqlplus中执行sql脚本，下面两种方式都可以
START file_name
@file_name


--判断表是否存在，如果存在则删除
declare
      num   number;
begin
      select count(1) into num from all_tables where TABLE_NAME = 'EMP' and OWNER='SCOTT';
      if   num=1   then
          execute immediate 'drop table EMP';
      end   if;
end;
/
--创建表
CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
可以将上述存储过程加载到每一个create table前面。

--ORACLE 判断序列是否存在,如果存在就删除

declare
 V_NUM number;

BEGIN
  ----多次删除时，每次都将v_num设置成为0
    V_NUM := 0;
    ----判断序列 seq_name_1 是否存在（区分大小写）
    select count(0) into V_NUM from user_sequences where sequence_name = 'SEQ_BUSINESS_PROCESS_INDEX_ID';
    ----如果存在立即删除
    if V_NUM > 0 then
    execute immediate 'DROP SEQUENCE  SEQ_BUSINESS_PROCESS_INDEX_ID';
    end if;
END;


-- 设置sqlplus模式显示总行数
show pagesize; --查看当前的pagesize
set pagesize 300;

-- 设置sqlplus模式显示行宽度
show linesize; --查看当前的linesize
set linesize 300;

-- 修改安装目录glogin.sql文件才能保证之前的设置永久生效
set pagesize 300;
set linesize 300;




-- 删除表对象
select 'drop table '||segment_name from dba_segments where owner='VPMUSER' and segment_type='TABLE';
-- 创建表对象
select
'create table '||segment_name || ' as select * from '||segment_name ||'@DBLINK'
from dba_segments where owner='VPMUSER' and segment_type='TABLE';

-- 检查表是否完整导入
select segment_name from dba_segments@aaa where owner='VPMUSER' and segment_type='TABLE'
and (segment_name not like 'BIN$%'
and segment_name not like '%201%')
minus
select segment_name from dba_segments where owner='VPMUSER' and segment_type='TABLE'  and segment_name not like 'BIN$%'


--查询用户所有表的语句1
select t.table_name,t.comments from user_tab_comments t

--查询用户所有表的语句2:
select r1, r2, r3, r5
from (select a.table_name r1, a.column_name r2, a.comments r3
          from user_col_comments a),
       (select t.table_name r4, t.comments r5 from user_tab_comments t)
where r4 = r1


-- 查找表的所有索引（包括索引名，类型，构成列）：
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表
-- 查找表的主键（包括名称，构成列）：
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表

-- 查找表的唯一性约束（包括名称，构成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表

-- 查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表

-- 查询外键约束的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键名称

-- 查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名

-- 查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表

    
--备份表数据
create table emp as select * from scott.emp

--还原表数据
insert into emp select * from scott.emp
    
--查看已经执行过的sql这些是存在共享池中的，用户名需要大写，必须具有DBA 的权限
select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('用户名') order by t.LAST_ACTIVE_TIME desc
    
    
--ORACLE11G 字符集更改（这里更改为AL32UTF8）
sqlplus sys as sysdba

--执行下面命令，有可能造成数据库中已有数据混乱的情况，所以在进行操作前，要进行数据库的备份操作
shutdown immediate;
STARTUP MOUNT;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE character set INTERNAL_USE AL32UTF8;
ALTER SESSION SET SQL_TRACE=FALSE;
shutdown immediate;
startup;

--察看 NLS_LANG 信息：
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';


UPDATE STAFF
SET MODIFY_TIME = TO_DATE('2016/04/22 00:01:00', 'yyyy/MM/dd hh24:mi:ss')
WHERE MODIFY_TIME < TO_DATE('2016/04/22 00:01:00', 'yyyy/MM/dd hh24:mi:ss');

UPDATE STAFF
SET MODIFY_TIME = TO_TIMESTAMP('19-03-2008 02:36:00.360000', 'dd-MM-yyyy hh24:mi:ss.ff')
WHERE STAFF_ID = '01';