Oracle数据库笔记-CSDN博客

本文链接：https://blog.csdn.net/zou_hailin226/article/details/124999143

运维

当前的数据库连接数

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';

oracle kill链接


/**
oracle杀链接示例
alter system kill session '1,14365';
*/
select sid, serial#,
'ALTER SYSTEM KILL SESSION  '''||sid||','||serial#||'''  IMMEDIATE;'
from v$session
where username = 'APP_XZCF';

常用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;

表和表数据统计

/******************************1、删除表******************************/
drop table test_tables_check;
/*2、新建表*/
create table test_tables_check
(
  table_name varchar2(2000),
  table_comments varchar2(2000),
  sl         varchar2(50)
);
comment on table test_tables_check is '数据库表数量核对';
comment on column test_tables_check.table_name is '表名称';
comment on column test_tables_check.table_comments is '表注释';
comment on column test_tables_check.sl is '数据量';   
/*******************************3、新建存储过程*******************************/
create or replace procedure test_tables_check_procedure(pageSize IN number) is
begin
  begin
    execute immediate 'truncate table test_tables_check';
    for t in 
      (select ut.table_name, b.comments from user_tables ut left join user_tab_comments b  on ut.table_name = b.table_name
   where 1=1
     and rownum<=pageSize) 
      loop
      execute immediate 'insert into test_tables_check ' || 'select ''' || t.table_name || ''' as table_name,''' || t.comments ||''' as table_comments,  count(1) as sl from ' || t.table_name;
         commit;
    end loop;
  end;
end test_tables_check_procedure;
/*******************************4、执行存储过程*******************************/
call test_tables_check_procedure(10);
/*******************************5、查询结果*******************************/
select * from test_tables_check;

常用运维操作

连接数据库

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；

查询oracle具体表使用情况

select * from (
  select 
       t.tablespace_name,
       t.owner, t.segment_name, 
       t.segment_type, 
       sum(t.bytes / 1024 / 1024) mb
  from dba_segments t
  where t.segment_type='TABLE'
  group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc;

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

# 新建表空间
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;

表分区

表空间及分区表的概念

表空间：

是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

分区表：

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表：

1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。

表分区的优缺点

优点：

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

表分区的几种类型及操作方法

范围分区：

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。

当使用范围分区时，请考虑以下几个规则：

每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
例一：
假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：

create table customer(     
  customer_id number not null primary key,     
  first_name  varchar2(30) not null,     
  last_name   varchar2(30) not null,     
  phone        varchar2(15) not null,     
  email        varchar2(80),     
  status       char(1) 
) 
partition by range (customer_id) (     
 partition cus_part1 values less than (100000) tablespace cus_ts01,     
 partition cus_part2 values less than (200000) tablespace cus_ts02 
)

例二：按时间划分

create table order_activities (
 order_id number(7) not null, 
 order_date date, 
 total_amount number, 
 custotmer_id number(7), 
 paid  char(1)
) 
partition by range (order_date) (
 partition ord_act_part01 values less than (to_date('01- MAY -2003','DD-MON-YYYY')) tablespace ord_ts01, 
 partition ord_act_part02 values less than (to_date('01-JUN-2003', 'DD-MON-YYYY')) tablespace ord_ts02, 
 partition ord_act_part02 values less than (to_date('01-JUL-2003', 'DD-MON-YYYY')) tablespace ord_ts03  
);

例三：MAXVALUE

create table rangetable(
 idd int primary key, 
 iname varchar(10), 
 grade int 
) 
partition by range (grade) (
 partition part1 values less then (1000) tablespace part1_tb, 
 partition part2 values less then (maxvalue) tablespace part2_tb
);

列表分区：

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
例一

create table problem_tickets (
 problem_id   number(7) not null primary key, 
 description  varchar2(2000), 
 customer_id  number(7) not null, 
 date_entered date not null, 
 status       varchar2(20)
) 
partition by list (status) (
 partition prob_active values ('ACTIVE') tablespace prob_ts01, 
 partition prob_inactive values ('INACTIVE') tablespace prob_ts02 
);

例二

create  table  listtable(
 id    int  primary  key, 
 name  varchar (20), 
 area  varchar (10)
) 
partition by  list (area) (
 partition part1 values ('guangdong', 'beijing') tablespace part1_tb, 
 partition  part2 values ('shanghai', 'nanjing')   tablespace part2_tb
);

散列分区：

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
例一：

create table hash_table (
 col number(8), 
 inf varchar2(100)
) 
partition by hash (col) (
 partition part01 tablespace hash_ts01, 
 partition part02 tablespace hash_ts02, 
 partition part03 tablespace hash_ts03
)
;

简写：

create table emp(
 empno number (4), 
 ename varchar2 (30), 
 sal   number
) 
partition by hash (empno) partitions 8
store in (
 emp1, emp2, emp3, emp4, emp5, emp6, emp7, emp8
);

hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

复合（范围列表）分区

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

create table sales (
  product_id varchar2(5), 
  sales_date date, 
  sales_cost number(10), 
  status varchar2(20)
) 
partition by range(sales_date) subpartition by list (status) (
 partition p1 values less than( to_date('2003-01-01', 'YYYY-MM-DD') ) tablespace rptfact2009 (
  subpartition p1sub1 values ('ACTIVE') tablespace rptfact2009, 
  subpartition p1sub2 values ('INACTIVE') tablespace rptfact2009
 ), 
 partition p2 values less than (to_date('2003-03-01', 'YYYY-MM-DD')) tablespace rptfact2009 (
  subpartition p2sub1 values ('ACTIVE') tablespace rptfact2009, 
  subpartition p2sub2 values ('INACTIVE') tablespace rptfact2009
 )
);

复合（范围散列）分区：

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test  (
   transaction_id number primary key, 
   item_id number(8) not null, 
   item_description varchar2(300), 
   transaction_date date 
)  

partition by range(transaction_date) subpartition by hash(transaction_id) subpartitions 3 store in ( dinya_space01, dinya_space02, dinya_space03 )  
(
  partition part_01 values less than(to_date('2006 - 01 - 01 ',' yyyy - mm - dd ')),      
  partition part_02 values less than(to_date('2010-01-01', 'yyyy-mm-dd' )), 
  partition part_03 values less than(maxvalue)  
);

有关表分区的一些维护性操作：

1、添加分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2、删除分区
以下代码删除了P3表分区：

alter table sales drop partition p3;

在以下代码删除了P4SUB1子分区：

alter table sales drop subpartition p4sub1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3、截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通过以下代码截断子分区：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

5、拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

6、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：

ALTER TABLE SALES COALESCA PARTITION;

7、重命名表分区
以下代码将P21更改为P2

ALTER TABLE SALESRENAME PARTITION P21 TO P2;

8、相关查询
跨分区查询

select 
  sum(*) 
from  (
  select count(*) cn from t_table_SS PARTITION (P200709_1) 
  union  all
  select count(*) cn from t_table_SS PARTITION (P200709_2)
);

查询表上有多少分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查询索引信息

select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' 
group by object_name,object_type,tablespace_name 
order by 4 desc

其他查询

--显示数据库所有分区表的信息： 
select * from DBA_PART_TABLES 

--显示当前用户可访问的所有分区表信息: 
select * from ALL_PART_TABLES 

--显示当前用户所有分区表的信息： 
select * from USER_PART_TABLES 

--显示表分区信息 显示数据库所有分区表的详细分区信息： 
select * from DBA_TAB_PARTITIONS 

--显示当前用户可访问的所有分区表的详细分区信息： 
select * from ALL_TAB_PARTITIONS 

--显示当前用户所有分区表的详细分区信息： 
select * from USER_TAB_PARTITIONS 

--显示子分区信息 显示数据库所有组合分区表的子分区信息： 
select * from DBA_TAB_SUBPARTITIONS 

--显示当前用户可访问的所有组合分区表的子分区信息： 
select * from ALL_TAB_SUBPARTITIONS 

--显示当前用户所有组合分区表的子分区信息： 
select * from USER_TAB_SUBPARTITIONS 

--显示分区列 显示数据库所有分区表的分区列信息： 
select * from DBA_PART_KEY_COLUMNS 

--显示当前用户可访问的所有分区表的分区列信息： 
select * from ALL_PART_KEY_COLUMNS 

--显示当前用户所有分区表的分区列信息： 
select * from USER_PART_KEY_COLUMNS 

--显示子分区列 显示数据库所有分区表的子分区列信息： 
select * from DBA_SUBPART_KEY_COLUMNS 

--显示当前用户可访问的所有分区表的子分区列信息： 
select * from ALL_SUBPART_KEY_COLUMNS 

--显示当前用户所有分区表的子分区列信息： 
select * from USER_SUBPART_KEY_COLUMNS 

--怎样查询出oracle数据库中所有的的分区表 
select * from user_tables a where a.partitioned='YES' 

--删除一个表的数据是 
truncate table table_name; 

--删除分区表一个分区的数据是 
alter table table_name truncate partition p5;