Oracle经验

辽西迪特里斯

已于 2023-07-31 14:32:02 修改

阅读量1.1k

点赞数 11

文章标签： oracle 数据库

于 2023-07-28 14:10:31 首次发布

本文链接：https://blog.csdn.net/moyinhang/article/details/131978987

版权

Oracle 常见语句记录

查看用户连接信息

select sessionid,userid,userhost,comment$text,spare1,to_char(ntimestamp#+1/3,'yyyy-mm-dd hh24:mi:ss') from sys.aud$
where returncode=1017 order by ntimestamp# desc

查询当前用户下得分区表：

select * from user_tables where partitioned='YES'

如果要查询整个数据库中的分区表：

select * from dba_tables where partitioned='YES'

如果要查询某个用户下得分区表：

select * from dba_tables where partitioned='YES' and owner='ABCDEFG'

USER_TAB_PARTITIONS：可查看分区表的名字、归属表空间以及表的详细分区情况。

USER_PART_TABLES：可查看用户所有的分区表，以及分区方式。

查询数据库中带数据的表

select t.table_name,t.num_rows from user_tables t ORDER BY NUM_ROWS DESC;

查询数据量不准确时分析一下表就准了

analyze table tablename compute statistics;

–查找数据库中带索引和主键的表

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P';
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;

select a.uniqueness 索引类型,b.index_name 索引名称,b.column_name 字段 ,b.TABLE_NAME 表名 from user_indexes a ,user_ind_columns b
where a.table_name=b.table_name and a.index_name = b.index_name
and a.table_owner=upper('abc') order by a.uniqueness desc;

select a.constraint_name,  a.column_name ,a.table_name
from user_cons_columns a, user_constraints b 
where a.constraint_name = b.constraint_name 
and b.constraint_type = 'P';

–替换掉数据中回车

update tablename set columnName=replace(columnName,chr(10),'') where columnNamelike '%'||chr(10) || '%';

删除重复记录保留rowid最大的记录 column为字段名


```scala
delete from abc a
 where (column) in
       (select t.column  from abc t group by t.column having count(1)>1)
   and rowid not in (select min(rowid)
                       from abc
                      group by column
                     having count(*) > 1);

导出空表

select ' alter table  '|| table_name || ' allocate extent;' 
from user_tables where segment_created='NO';

select * from all_tab_comments -- 查询所有用户的表,视图等
select * from user_tab_comments -- 查询本用户的表,视图等
select * from all_col_comments  --查询所有用户的表的列名和注释.
select * from user_col_comments -- 查询本用户的表的列名和注释
select * from all_tab_columns --查询所有用户的表的列名等信息(详细但是没有备注).
select * from user_tab_columns --查询本用户的表的列名等信息(详细但是没有备注).

–查询某些表数据量

select t.table_name,t.NUM_ROWS,t.* from user_tables t where table_name in('abc','bcd')

–Oracle 空表无法导出处理

1、用以下这句查找空表：select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

–oracle查表数据量

select u.TABLE_NAME,u.NUM_ROWS from user_tables u order by u.NUM_ROWS desc

–1.正则判断,适用于10g以上版本

--非正整数 
select 字段 from 表 where regexp_replace(字段,'\d','') is not null;
--非数值类型
select 字段 from 表 where regexp_replace(字段,'^[-\+]?\d+(\.\d+)?$','') is not null;

–查询锁定进程

select * from gv$process
where addr in (
select paddr from gv$session where sid =1
);

–查询表中字段类型

select DATA_TYPE  
from User_Tab_Columns t 
where t.column_name=upper('columnName') 
  and  t.table_name =upper(trim('abc'));


--guid是raw类型，转成varchar类型
rawtohex(sys_guid())  as "SERIALNUM_ID"

字符集客户端

select userenv('language') from dual
select * from nls_database_parameters


--出现already use by another user
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;


alter system kill session '29,525';

–查询uuid

select sys_guid() from dual;

//oracle 11g 导出空表

show parameter deferred_segment_creation;

alter system set deferred_segment_creation=false; 

select table_name from user_tables where NUM_ROWS=0;

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0

//树形结构迭代查询

–子取父

select * from tb_org CONNECT BY PRIOR orgparent=orgid START WITH orgid='newsroom'

–子取父变形

select * from tb_org CONNECT BY PRIOR orgparent=orgid START WITH orgparent='newsroom'

–父取子（结果中没有父这条记录）

select * from tb_org CONNECT BY PRIOR orgid=orgparent START WITH orgparent='newsroom'

—父取子变形（多了父这一级）

select * from tb_org connect by prior orgid=orgparent start with orgid ='newsroom'

//查找用户号

select name,password, user# from sys.user$;

//修改用户名

1、windows 平台下运行 cmd2、sqlplus /nolog3、

SQL> connsys/lmis@lmisdx_localas sysdba 连接到数据库。

通常可以用sys用户登陆。

SQL> select * from user$; 找到 name为需要修改的用户的user#。
SQL> UPDATE USER$ SET NAME='新用户名' WHERE USER#=38;   已更新 1 行。6、SQL> COMMIT;提交完成。
SQL> ALTER SYSTEM CHECKPOINT;   系统已更改。
SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码;   此时会提示新用户不存在。
SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码        *ERROR 位于第 1 行:ORA-01918: 用户'新用户'不存在
SQL> ALTER SYSTEM FLUSH SHARED_POOL;   系统已更改。
SQL> ALTER USER 新用户 IDENTIFIED BY 新密码;    用户已更改。

11、测试连接

SQL> CONN 新用户/新密码@lmisdx_local;

已连接。

//修改DBF文件名称

2、在操作系统层面移动数据文件
下面以把文件“/u01/app/oradata/demo/history.dbf”移动到“/data/oracle/oradata/history01.dbf”为例介绍。
(1)、关闭数据库
[oracle@DB_SERVER ~]$ sqlplus / as sysdba(回车)

SQL>  shutdown   immediate(回车) #等待数据库提示关闭

SQL> exit

(2)移动文件(linux命令 windows下直接到路径下找到文件修改名字即可)

[oracle@DB_SERVER ~]$ mv /u01/app/oradata/demo/history.dbf /data/oracle/oradata/history01.dbf

3、修改数据库的文件挂载

[oracle@DB_SERVER ~]$ sqlplus / as sysdba(回车)

SQL> startup   mount(回车) #等待数据库提示装载成功

SQL> alter database rename file '/u01/app/oradata/demo/history.dbf' to '/data/oracle/oradata/history01.dbf';

#完成后打开数据库。

SQL> alter   database   open;
SQL> exit

数据库文件移动成功。

1.查询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

–整体表空间查询语句

SELECT TABLESPACE_NAME 表空间,
       TO_CHAR(ROUND(BYTES / 1024, 2), '99990.00') || '' 实有,
       TO_CHAR(ROUND(FREE / 1024, 2), '99990.00') || 'G' 现有,
       TO_CHAR(ROUND((BYTES - FREE) / 1024, 2), '99990.00') || 'G' 使用,
       TO_CHAR(ROUND(10000 * USED / BYTES) / 100, '99990.00') || '%' 比例
  FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
               FLOOR(A.BYTES / (1024 * 1024)) BYTES,
               FLOOR(B.FREE / (1024 * 1024)) FREE,
               FLOOR((A.BYTES - B.FREE) / (1024 * 1024)) USED
          FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME, SUM(BYTES) FREE
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) B
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
  --WHERE TABLESPACE_NAME LIKE 'CDR%'
 ORDER BY FLOOR(10000 * USED / BYTES) DESC;

2.查询oracle系统用户的默认表间和临时表空间

select default_tablespace,temporary_tablespace from dba_users

3.查询单张表的使用情况

select segment_name,bytes from dba_segments where segment_name = 'abc' and owner = USER

abc是您要查询的表名称

4.查询所有用户表使用大小的前三十名

select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30

5.查询当前用户默认表空间的使用情况

select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent) 
from 
( 
SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0))) as usecontent,sum(nvl(a.bytes,0)) as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent 
FROM dba_free_space a,dba_data_files b 
WHERE a.file_id=b.file_id and b.tablespace_name = (select default_tablespace from dba_users where username = user) 
group by b.tablespace_name,b.file_name,b.file_id,b.bytes 
) 
GROUP BY tablespacename

6.查询用户表空间的表

select * from user_tables

–解锁用户abc

ALTER USER abc ACCOUNT UNLOCK;

—解决连接oracle 11g报ORA-01034和ORA-27101的错误

我本机安装的数据库版本是ORACLE 11G R2，用plsql连接时候，报ora-12514如下错误：

但是在cmd里用sqlplus连接已经创建的用户时候，报如下错误：

ORA-01034 - Oracle not available
ORA-27101 - shared memory realm does not exist
网上查了几个解决资料，说是oracle home和sid的问题，或者是让使用Net Configuration Assistant重建本地网络服务名。实践了一下，都不管用。

经过总结，使用如下方法解决比较好：

1、首先看一下 ORACLE_BASE\ORACLE_HOME\DATABASE 下面的 ORADIM.LOG 文件
E:\app\psun\product\11.2.0\dbhome_1\database：
看日志才发现原来报的下面的错误

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

2、进过查找该错误，发现是tnsnames.ora文件中少了如下语句的原因
LISTENER_MAXIMO =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
添加后，重启数据库实例服务，重启监听程序：

再次使用SQLPLUS、plsql连接，好了！

–查询是否有回车的语句

select t.sqlserver_code from DXC_RANGE_STREETCODE t where t.sqlserver_code like '%'|| chr(10) ||'%'

–计算长度字段中有中文不按一位计算

LENGTHB

–不受group by 限制查看

 TO_CHAR(WMSYS.WM_CONCAT(T1.CNT)) AS CNT,

–oracle 导出时遇到exp 00003错误

最近接到一个case，由于服务器已经升级到9.2.0.7.0，而客户端还是原来的9.2.0.1.0。根据oracle的版本兼容性，这个应该是不应该存在很大问题，但是当某个表中含有lob（包括clob、blob等）大对象字段的情况下，如果使用低版本的客户端来export该表，则会报“未找到段 (X,XXXXXX) 的存储定义其它可正常导出”。这其实是oracle的一个bug。不同版本间大对象export间出现问题。
追根溯源，就是那个exu9tne视图中少了一句话。
于是找到rdbms\admin\catexp.sql获取exu9tne视图的定义：

CREATE OR REPLACE VIEW exu9tne (tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$WHERE ext# = 1

根据oracle的处理方法，应该在其后再增加一句话，即可处理版本间大对象的export问题。
Union ALLSELECT * FROM SYS.EXU9TNEB
则最后该视图的定义应该为：

CREATE OR REPLACE VIEW exu9tne (tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$WHERE ext# = 1
Union ALLSELECT * FROM SYS.EXU9TNEB

以sys用户来执行之，不过建议修改前备份好catexp.Sql脚本，以免出错。
经过不断test，发现这种方法是可行的。所以就记录下来，如大家分享。

–导出命令

 1、完全： 
imp abc/password@127.0.0.1/orcl BUFFER=64000 FILE=E:\aa.DMP FULL=Y 
 
 2、用户模式： 
imp abc/password@127.0.0.1/orcl    BUFFER=64000 FILE=E:\FULLConsole.DMP FROMUSER=console TOUSER=CONSOLE1

exp abc/password@127.0.0.1/orcl  BUFFER=64000 FILE=E:\aaa.DMP owner=abc

3、表模式： 
      EXP abc/password@127.0.0.1/orcl    BUFFER=64000 FILE=E:\abc.DMP OWNER=abc TABLES=(aaa)

–查询排查某些表后剩余待导出表

select  
listagg(table_name, ',') within group (order by table_name)
  from dba_tables
  where owner  in ('aaa') 
  and table_name not in                           
  ('tablename')    
  order by owner;

bat 命令中这样写：D:\instantclient12\exp.exe abc/pass@127.0.0.1/orcl file=E:\14oracle-aaa.DMP logfile=OracleExp.LOG tables=(待导出表)

–数据泵方式导出

expdp abc/pass@127.0.0.1/orcl dumpfile=abc.DUMP logfile=abc.LOG schemas=(abc)

–数据泵方式导入（导入导出用户名一致时）

impdp abc/pass@127.0.0.1/orcl dumpfile=文件名.DUMP logfile=abc.LOG schemas=(abc)

–数据泵方式导入（导入导出用户名不一致时）

//abc是dump文件里的用户名，bcd待导入用户

impdp abc/pass@127.0.0.1/orcl dumpfile=abc.DUMP logfile=abc.LOG REMAP_SCHEMA=abc:bcd

–带目标端版本

expdp abc/pass@127.0.0.1/orcl dumpfile=abc.dmp schemas=abcversion=10.2.0.1.0

–导出出错ORACLE 错误 904

EXP-00008: 遇到

ORA-00904: "POLTYP": invalid identifier

EXP-00000: 导出终止失败

发生EXP-00008: 遇到 ORACLE 错误 904 的导出报错。

根据网上的资料和总结，分析可能是本地使用的exp导出工具的版本与数据库服务器端exp版本不一致导致。

在命令操作中发现，本地安装的Oracle 版本是Release 11.2.0.1.0,而数据库服务器端安装的Oracle 是Release 10.2.0.4.0 .

由于Oracle的imp/exp组件的操作原则--向下兼容，且有一些规则：

规则1:低版本的exp/imp可以连接到高版本（或同版本）的数据库服务器，但高版本的exp/imp不能连接到低版本的数据库服务器；

规则2:高版本exp出的dmp文件，低版本无法imp（无法识别dmp文件）；低版本exp出的dmp文件，高版本可以imp（向下兼容）；

规则3:从Oracle 低版本Export的数据可以Import到Oracle高版本中，但限于Oracle的相邻版本，如从Oracle 10 到 Oracle 11.对于两个不相邻版本间进行转换，如从Oracle 9 到 Oracle 11,则应先将数据输入到中间版本-Oracle 10,再从中间数据库转入更高版本Oracle 11.

据此，最快捷的解决方案就是使用安装了10g Oracle 的机子，进行服务器上数据库数据的导出。4

–修改表空间为自动增长

alter database datafile 'd:/OracleTest/test001.dbf' autoextend on;

–修改表空间大小

alter database datafile 'E:\oracle\product\10.2.0\oradata\test.dbf' resize 2000m;

–查看表空间大小

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
group by tablespace_name

–删除表空间

DROP TABLESPACE EHR INCLUDING CONTENTS AND DATAFILES;

–查看表空间地址

select file_Name from dba_data_files

–创建表空间

CREATE SMALLFILE TABLESPACE "abc" DATAFILE 'E:\app\Administrator\oradata\orcl\abc.DBF' SIZE 50M REUSE LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

–创建用户用户授权

create user abcidentified by 1 default tablespace abc;
-- Grant/Revoke role privileges 
grant exp_full_database to abc;
grant imp_full_database to abc;
grant resource to abc;
-- Grant/Revoke system privileges 
grant create any job to abc;
grant create any procedure to abc;
grant select any table to abc;
grant unlimited tablespace to abc;

grant connect,resource,dba to hb_ph_mchis;

–删除所有表数据

SELECT 'TRUNCATE TABLE '|| table_name || ';' FROM USER_TABLES
ORDER BY TABLE_NAME;

–查询所有表数据量

select u.TABLE_NAME,u.NUM_ROWS from user_tables u order by u.NUM_ROWS desc



SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

–给表加主键

alter table  abcadd constraint  PK_abc  primary key (id);

–添加字段

alter table abc add upload_time date default sysdate;
comment on column abc.upload_time is '上传数据时间';

–查询字典表

select distinct table_name from user_tab_columns t 
where column_name = 'OPERATETIME' order by t.TABLE_NAME

–建立数据link

1未配置本地服务
create database link linkname
　　 connect to dbuser identified by dbpassword
　　 using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac)
    )
  )';

其中 linkname 是数据链名称 ,dbuser 登陆用户名 ,dbpassword 登陆密码 ,HOST= 对方ip,PORT =端口,SERVICE_NAME=数据库的sid

2 已配置本地服务
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘本地配置的数据的实例名’;
例如

create public database link linkfwq connect to dbuser
　　identified by dbpassword using 'dora'

–触发器创建

create or replace trigger auditinfotrigger
  after update on auditinfo  
  for each row
declare
  -- local variables here
begin
  update auditinfo set upload_time = sysdate;end trigger;

–输出

dbms_output.put_line();

–查看oracle版本

select * from product_component_version

MySQL经验

查看表数据量

SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = (SELECT database()) order by table_name;

分析表-用于查询准确数据量

analyze table tablename