运维

当前的数据库连接数

select count(*) from v$process;

查看不同用户的连接数

select username,count(username) from v$session where username is not null group by username;

数据库允许的最大连接数

select value from v$parameter where name = 'processes';

修改最大连接数:

alter system set processes = 300 scope = spfile;

查看所有用户

select * from all_users;

查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;

当前的session连接数

select count(*) from v$session  

并发连接数

select count(*) from v$session where status='ACTIVE';

常用sql

oracle 查询当前用户下的表名 + 表注释

select a.table_name, b.comments
  from user_tables a left join user_tab_comments b on a.table_name = b.table_name
 where 1=1
 and (a.table_name like 'TA_%' OR a.table_name like 'TD_%' OR a.table_name like 'UP_%' OR a.table_name like 'WF_%')
 and not regexp_like(a.table_name,'\d{2}')
 order by a.table_name;

oracle 查询某表的所有字段 + 字段注释 + 字段类型

select t.table_name  tablename,
       t.column_name columnname,
       t.data_type   datatype,
       a.comments
  from user_tab_columns t
  left join user_col_comments a on t.table_name = a.table_name and t.column_name = a.column_name

oracle 查询某表表结构

select t.*
  from (select 
               lower(t.column_name) columnname,
               lower(t.data_type) datatype,
               t.data_length,
               (case
                 when t.nullable = 'N' then
                  'y'
                 else
                  ''
               end) nullable,
               lower(ucc.column_name) pk,
               replace(a.comments, chr(10), ';') comments
          from user_tab_columns t
          left join user_col_comments a
            on t.table_name = a.table_name
           and t.column_name = a.column_name
          left join user_constraints uc
            on uc.constraint_type = 'P'
           and uc.table_name = t.table_name
          left join user_cons_columns ucc
            on ucc.constraint_name = uc.constraint_name
           and ucc.column_name = t.column_name
         where t.table_name = upper('TA_YJS_STD_APPROVE_ELEMENT_NEC')
         order by t.column_id
        
        ) t

;

Oracle当没有主键数据去重复

delete from table_name 
where rowid not in(
select max(rowid) from table_name t group by t.column1, t.column2, t.column3 
);
原理：子sql通过分组找出最大的rowid，因为rowid是唯一的，可以暂时当主键使用，然后查出一组不唯一的数据，把其他的全部删除掉就可以了

Oracle删除用户下所有表

select 'drop table '||table_name||';'from cat where table_type='TABLE';

SEQUENCE查询、删除、新建

#查询SEQUENCE
select SEQUENCE_NAME,INCREMENT_BY,LAST_NUMBER from user_sequences;
#删除SEQUENCE
DROP SEQUENCE EMP_SEQUENCE;
#新建SEQUENCE
CREATE SEQUENCE seq_itv_collection
INCREMENT BY 1  -- 每次加几个  
START WITH 1399 -- 从1开始计数  
NOMAXVALUE      -- 不设置最大值  
NOCYCLE         -- 一直累加，不循环  
CACHE 10;

常用运维操作

连接数据库

sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]

设置CMD窗口下使用notepad.exe编辑SQL

#设置方法
SQL>define _editor=notepad.exe
#使用方法
SQL>ed

表空间查询

select * from SYS.DBA_DATA_FILES t

查询oracle表空间的使用情况

select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name；

新建（表空间、用户、授权）

# 新建表空间
create tablespace wanwan
datafile 'D:\app\tabletemplate\wanwan.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m;

# 新建用户
create user wanwan 
identified by wanwan  
default tablespace wanwan;

# 用户授权
grant connect,resource to wanwan;  
grant dba to wanwan;
# 新建视图权限权限不足
grant select any table to wanwan;
# 赋予create session的权限
grant create session to wanwan;
# 分配创建表，视图，触发器，序列，过程 权限
grant create table,create view,create trigger, create sequence,create procedure to wanwan;
# 授权使用表空间
grant unlimited tablespace to wanwan; 

命令行下进行Oracle用户解锁和锁定

alter user scott account unlock;解锁用户
alter user scott account lock;锁定用户
alter user scott identified by scott;修改用户密码
本地可这样登录
sqlplus  /nolog
sqlplus /as sysdba

ORACLE not available ORA-27101

sqlplus  /nolog
conn /as sysdba;
startup
如果已经启动
先shutdown immediate;然后startup;

数据库导入导出

ignore=y 忽略
full=y 全部
owner=(system,sys) 所有者
fromuser=CDMDC_RPT 来至哪个用户
touser=CDMDC_RPT 去哪个用户
●导入
　　 1 将XBRL_RPT.dmp中XBRL_RPT用户的数据导入orcl数据库中XBRL_RPT用户中
imp XBRL_RPT/XBRL_RPT@orcl file=d:\XBRL_RPT.dmp log=D:\log.txt fromuser=XBRL_RPT touser=XBRL_RPT
2 将c:\daochu.dmp 中的数据导入 TEST数据库中。
　　 imp wanwan/wanwan@orcl file=d:\daochu.dmp ignore=y full=y log=D:\log.txt
●导出
1 将数据库中XBRL_RPT用户与数据导出到XBRL_RPT.dmp中
exp XBRL_RPT/XBRL_RPT@192.168.18.61/GBICC file=d:\XBRL_RPT.dmp owner=XBRL_RPT
2 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
3 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
4 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat) 
5 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"
6  示例
exp cfa_0801/cfa_0801@192.168.18.247/orcl file=d:\daochu.dmp owner=(cfa_0801)
●初始化空数据表（这样空的数据表也可以导出出来）
1 查询空的数据表
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
2 查询空的数据表结果复制出来执行表初始化如下
alter table tableName allocate extent;