Skip to main content

Jul 26, 16

"ORA-01502:索引或这类索引的分区处于不可用状态 2013-06-04 20:10:15
分类: Linux
ORA-01502:索引或这类索引的分区处于不可用状态
原因:出现这个问题,可能有人对表进行了move ,compress,或者disable了索引
1. alter table xxxxxx move tablespace xxxxxxx 命令后,索引就会失效。
2. alter index index_name  unusable,命令使索引失效。
解决办法:
1. 重建索引才是解决这类问题的完全的方法。
     alter index index_name rebuild (online);
     或者alter index index_name rebuild;
2. 如果是分区索引只需要重建那个失效的分区 。
     alter index index_name rebuild partition partition_name (online);
     或者alter index index_name rebuild partition partition_name ;
3. 或者改变当前索引的名字。
说明:
1. alter session set skip_unusable_indexes=true;就可以在session级别跳过无效索引作查询。
2. 分区索引应适用user_ind_partitions。
3. 状态分4种:"

Jul 25, 16

"[原]在 Oracle 中使用正则表达式

Oracle使用正则表达式离不开这4个函数:

1。regexp_like

2。regexp_substr

3。regexp_instr

4。regexp_replace

看函数名称大概就能猜到有什么用了。

 

regexp_like 只能用于条件表达式,和 like 类似,但是使用的正则表达式进行匹配,语法很简单:



regexp_substr 函数,和 substr 类似,用于拾取合符正则表达式描述的字符子串,语法如下:

regexp_instr 函数,和 instr 类似,用于标定符合正则表达式的字符子串的开始位置,语法如下:



regexp_replace 函数,和 replace 类似,用于替换符合正则表达式的字符串,语法如下:



这里解析一下几个参数的含义:

1。source_char,输入的字符串,可以是列名或者字符串常量、变量。

2。pattern,正则表达式。

3。match_parameter,匹配选项。

        取值范围: i:大小写不敏感; c:大小写敏感;n:点号 . 不匹配换行符号;m:多行模式;x:扩展模式,忽略正则表达式中的空白字符。

4。position,标识从第几个字符开始正则表达式匹配。

5。occurrence,标识第几个匹配组。
6。replace_string,替换的字符串。
 

说了一堆文绉绉的,现在开始实例演练了,在此之前先建好一个表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create table tmp as
with data as (
  select 'like' as id ,'a9999' as str from dual union all
  select 'like'       ,'a9c'          from dual union all
  select 'like'       ,'A7007'        from dual union all
  select 'like'       ,'123a34cc'     from dual union all
  select 'substr'     ,'123,234,345'  from dual union all
  select 'substr'     ,'12,34.56:78'  from dual union all
  select 'substr'     ,'123456789'    from dual union all
  select 'instr'      ,'192.168.0.1'  from dual union all
  select 'replace'    ,'(020)12345678' from dual union all
  select 'replace'    ,'001517729C28' from dual 
)
select * from data ;
 
select * from tmp ;
ID      STR
------- -------------
like    a9999
like    a9c
like    A7007
like    123a34cc
substr  123,234,345
substr  12,34.56:78
substr  123456789
instr   192.168.0.1
replace (020)12345678
replace 001517729C28
regexp_like 例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select str from tmp where id='like' and regexp_like(str,'A\d+','i'); -- 'i' 忽略大小写
STR
-------------
a9999
a9c
A7007
123a34cc
 
select str from tmp where id='like' and regexp_like(str, 'a\d+');
STR
-------------
a9999
a9c
123a34cc
 
select str from tmp where id='like' and regexp_like(str,'^a\d+');
STR
-------------
a9999
a9c
 
select str from tmp where id='like' and regexp_like(str,'^a\d+$');
STR
-------------
a9999
regexp_substr 例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
col str format a15;
select
  str,
  regexp_substr(str,'[^,]+')     str,
  regexp_substr(str,'[^,]+',1,1) str,
  regexp_substr(str,'[^,]+',1,2) str,  -- occurrence 第几个匹配组
  regexp_substr(str,'[^,]+',2,1) str   -- position 从第几个字符开始匹配
from tmp
where id='substr';
STR             STR             STR             STR             STR
--------------- --------------- --------------- --------------- ---------------
123,234,345     123             123             234             23
12,34.56:78     12              12              34.56:78        2
123456789       123456789       123456789                       23456789
 
select
  str,
  regexp_substr(str,'\d')        str,
  regexp_substr(str,'\d+'  ,1,1) str,
  regexp_substr(str,'\d{2}',1,2) str,
  regexp_substr(str,'\d{3}',2,1) str
from tmp     
where id='substr';
STR             STR             STR             STR             STR
--------------- --------------- --------------- --------------- ---------------
123,234,345     1               123             23              234
12,34.56:78     1               12              34
123456789       1               123456789       34              234
 
 
select regexp_substr('123456789','\d',1,level) str  --取出每位数字,有时这也是行转列的方式
from dual
connect by level<=9
STR
---------------
1
2
3
4
5
6
7
8
9
regex_instr 例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
col ind format 9999;
select
  str,
  regexp_instr(str,'\.'    ) ind ,
  regexp_instr(str,'\.',1,2) ind ,
  regexp_instr(str,'\.',5,2) ind
from tmp where id='instr';
STR               IND   IND   IND
--------------- ----- ----- -----
192.168.0.1         4     8    10
     
select
  regexp_instr('192.168.0.1','\.',1,level) ind ,  -- 点号. 所在的位置
  regexp_instr('192.168.0.1','\d',1,level) ind    -- 每个数字的位置
from dual
connect by level <=  9
  IND   IND
----- -----
    4     1
    8     2
   10     3
    0     5
    0     6
    0     7
    0     9
    0    11
    0     0
regex_replace 例子:

1
2
3
4
5
6
7
8
9
10
select
  str,
  regexp_replace(str,'020','GZ') str,
  regexp_replace(str,'(\d{3})(\d{3})','<\2\1>') str -- 将第一、第二捕获组交换位置,用尖括号标识出来
from tmp
where id='replace'; 
STR             STR             STR
--------------- --------------- ---------------
(020)12345678   (GZ)12345678    (020)<456123>78
001517729C28    001517729C28    <517001>729C28
综合应用的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
col row_line format a30;
with sudoku as (
  select '020000080568179234090000010030040050040205090070080040050000060289634175010000020' as line
  from dual
),
tmp as (
  select regexp_substr(line,'\d{9}',1,level) row_line,
  level col
  from sudoku
  connect by level<=9
)
select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)','\1 \2 \3 \4 \5 \6 \7 \8 \9') row_line
from tmp
 
ROW_LINE
------------------------------
0 2 0 0 0 0 0 8 0
5 6 8 1 7 9 2 3 4
0 9 0 0 0 0 0 1 0
0 3 0 0 4 0 0 5 0
0 4 0 2 0 5 0 9 0
0 7 0 0 8 0 0 4 0
0 5 0 0 0 0 0 6 0
2 8 9 6 3 4 1 7 5
0 1 0 0 0 0 0 2 0
分类: Oracle"

Jul 23, 16

"oracle中查看用户权限

博客分类: Oracle权限
OracleSQL 
1.查看所有用户:
  select * from dba_users;
  select * from all_users;
  select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
  select * from dba_sys_privs;
  select * from user_sys_privs;
 
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;

4.查看用户对象权限:
  select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;

5.查看所有角色:
  select * from dba_roles;

6.查看用户或角色所拥有的角色:
  select * from dba_role_privs;
  select * from user_role_privs;
 
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
 
 
注意:
1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
 
2、查看一个用户的所有系统权限(包含角色的系统权限)
Sql代码  
select privilege from dba_sys_privs where grantee='DATAUSER'  
union  
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );   "

Jul 23, 16

"How long old stats are kept

1
select dbms_stats.get_stats_history_retention from dual;
Set retention of old stats to 10 days

1
exec dbms_stats.alter_stats_history_retention(10);
Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)

1
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Show available stats that have not been purged

1
select dbms_stats.get_stats_history_availability from dual;"

Jul 23, 16

"默认表空间:
ALTER DATABASE DEFAULT TABLESPACE users;"

Jul 22, 16

" 重建及切换临时表空间 2015-09-23 12:37:14
分类: Linux
原文地址:重建及切换临时表空间 作者:aishu521
來源網絡
1、查看临时表空间 (dba_temp_files视图)(v_$tempfile视图)
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看

2、缩小临时表空间大小
alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize 100M;

3、扩展临时表空间:
方法一、增大临时文件大小:
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
方法二、将临时数据文件设为自动扩展:
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件:
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

4、创建临时表空间:
SQL> create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/temp11.dbf’ size 10M;

5、更改系统的默认临时表空间:
--查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间
alter database default temporary tablespace temp1;
所有用户的默认临时表空间都将切换为新的临时表空间:
select username,temporary_tablespace,default_ from dba_users;
--更改某一用户的临时表空间:
alter user scott temporary tablespace temp;

6、删除临时表空间
删除临时表空间的一个数据文件:
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
删除临时表空间(彻底删除):
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;

7、查看临时表空间的使用情况(GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询)
GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小
dba_temp_files视图的bytes字段记录的是临时表空间的总大小
SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name
8、查找消耗资源比较的sql语句
Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid
 
9、查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;

10、临时表空间组介绍
  1)创建临时表空间组:
create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2M tablespace group group1;
create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2M tablespace group group2;
 
 2)查询临时表空间组:dba_tablespace_groups视图
select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1                         TEMPTS1
GROUP2                         TEMPTS2

 3)将表空间从一个临时表空间组移动到另外一个临时表空间组:
alter tablespace tempts1 tablespace group GROUP2 ;
select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMPTS1
GROUP2                         TEMPTS2

 4)把临时表空间组指定给用户
alter user scott temporary tablespace GROUP2;

 5)在数据库级设置临时表空间
alter database default temporary tablespace GROUP2;

 6)删除临时表空间组 (删除组成临时表空间组的所有临时表空间)
drop tablespace tempts1 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                         TEMPTS2

drop tablespace tempts2 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME

11、对临时表空间进行shrink(11g新增的功能)
--将temp表空间收缩为20M
alter tablespace temp shrink space keep 20M;
--自动将表空间的临时文件缩小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;

临时表空间作用
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会一直增长。直到耗尽硬盘空间。
网上有人猜测在磁盘空间的分配上,oracle使用的是贪心算法,如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB。
也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用:
  索引create或rebuild;
  Order by 或 group by;
  Distinct 操作;
  Union 或 intersect 或 minus;
  Sort-merge joins;
  analyze.
二,修改
1.查询用户现在所使用的临时表空间
 
SQL> select username,temporary_tablespace from dba_users;
 
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP2
SYS                            TEMP2
SYSTEM                         TEMP2
DBSNMP                         TEMP2
SYSMAN                         TEMP2
SCOTT                          TEMP2
OUTLN                          TEMP2
MDSYS                          TEMP2
ORDSYS                         TEMP2
CTXSYS                         TEMP2
ANONYMOUS                      TEMP2
EXFSYS                         TEMP2
DMSYS                          TEMP2
WMSYS                          TEMP2
XDB                            TEMP2
ORDPLUGINS                     TEMP2
SI_INFORMTN_SCHEMA             TEMP2
OLAPSYS                        TEMP2
MDDATA                         TEMP2
DIP                            TEMP2
 
2.查询临时表空间所使用的数据文件
 
SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP02.DBF
3.创建新的临时表空间
SQL> create temporary tablespace temp tempfile 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/TEMP01.DBF' size 20m;
Tablespace created
4.切换到新创建后的临时表空间
SQL> alter database default temporary tablespace temp;
Database altered
SQL> select username,temporary_tablespace from dba_users;
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP
SYS                            TEMP
SYSTEM                         TEMP
DBSNMP                         TEMP
SYSMAN                         TEMP
SCOTT                          TEMP
OUTLN                          TEMP
MDSYS                          TEMP
ORDSYS                         TEMP
CTXSYS                         TEMP
ANONYMOUS                      TEMP
EXFSYS                         TEMP
DMSYS                          TEMP
WMSYS                          TEMP
XDB                            TEMP
ORDPLUGINS                     TEMP
SI_INFORMTN_SCHEMA             TEMP
OLAPSYS                        TEMP
MDDATA                         TEMP
DIP                            TEMP
5.删除原临时表空间
 
SQL> drop tablespace temp2 including contents and datafiles;
Tablespace dropped
SQL> select file_name,tablespace_name,bytes/1024/1024 MB,autoextensible
  2  from dba_temp_files
  3  /
FILE_NAME                                                          "

Jul 22, 16

"那就是先将该字段设置为UNUSED,然后在删除UNUSED字段即可。
例如:
ALTER TABLE test SET UNUSED COLUMN ROUTERADDR;
ALTER TABLE test DROP UNUSED COLUMNS;"

Jul 22, 16

"select object_name,status from dba_objects where object_id='0000052424';  "

Jul 21, 16

"-Dcom.sun.management.jmxremote \
-Dcom.sun.management.jmxremote.ssl=false \
-Dcom.sun.management.jmxremote.authenticate=false \
-Dcom.sun.management.jmxremote.port=1098"

Jul 20, 16

"<dependency>
<groupId>com.jayway.jsonpath</groupId>
<artifactId>json-path</artifactId>
<version>2.2.0</version>
</dependency>"

Jul 19, 16

"发现系统存在大量TIME_WAIT状态的连接,通过调整内核参数解决,
vi /etc/sysctl.conf

编辑文件,加入以下内容:
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
 
然后执行 /sbin/sysctl -p 让参数生效。
 
net.ipv4.tcp_syncookies = 1 表示开启SYN Cookies。当出现SYN等待队列溢出时,启用cookies来处理,可防范少量SYN攻击,默认为0,表示关闭;

net.ipv4.tcp_tw_reuse = 1 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接,默认为0,表示关闭;

net.ipv4.tcp_tw_recycle = 1 表示开启TCP连接中TIME-WAIT sockets的快速回收,默认为0,表示关闭。

net.ipv4.tcp_fin_timeout 修改系統默认的 TIMEOUT 时间
 "

Jul 19, 16

"查看LINUX进程内存占用情况
  可以直接使用top命令后,查看%MEM的内容。可以选择按进程查看或者按用户查看,如想查看oracle用户的进程内存使用情况的话可以使用如下的命令:

 (1)top

  top命令是Linux下常用的性能分析工具,能够实时显示系统中各个进程的资源占用状况,类似于Windows的任务管理器

  可以直接使用top命令后,查看%MEM的内容。可以选择按进程查看或者按用户查看,如想查看oracle用户的进程内存使用情况的话可以使用如下的命令:
  $ top -u oracle

内容解释:

  PID:进程的ID
  USER:进程所有者
  PR:进程的优先级别,越小越优先被执行
  NInice:值
  VIRT:进程占用的虚拟内存
  RES:进程占用的物理内存
  SHR:进程使用的共享内存
  S:进程的状态。S表示休眠,R表示正在运行,Z表示僵死状态,N表示该进程优先值为负数
  %CPU:进程占用CPU的使用率
  %MEM:进程使用的物理内存和总内存的百分比
  TIME+:该进程启动后占用的总的CPU时间,即占用CPU使用时间的累加值。
  COMMAND:进程启动命令名称

  常用的命令:

  P:按%CPU使用率排行
  T:按MITE+排行
  M:按%MEM排行

(2)pmap
  可以根据进程查看进程相关信息占用的内存情况,(进程号可以通过ps查看)如下所示:
  $ pmap -d 14596

 (3)ps

  如下例所示:
  $ ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid'  其中rsz是是实际内存
  $ ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' | grep oracle |  sort -nrk5
  其中rsz为实际内存,上例实现按内存排序,由大到小"

1 - 20 of 3874 Next › Last »
20 items/page

Diigo is about better ways to research, share and collaborate on information. Learn more »

Join Diigo