Oracle日常使用整理

1.高效分页

SELECT *  FROM (SELECT a.*, ROWNUM rn  FROM (SELECT * FROM tablename) a WHERE ROWNUM <= 40) WHERE rn >= 21

2.查询某个字段是否出现在某张表中

SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS WHERE upper(COLUMN_NAME) like '%ull%'

3.略

4.死锁检查以及处理

查询：

SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID||', '||vss.SERIAL# 删除号,

lo.locked_mode,lo.SESSION_ID, vss.SERIAL#,vss.action Action,vss.osuser OSUSER, vss.LOGON_TIME,

vss.process AP_PID, VPS.SPID DB_PID ,vss.*

From v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS

Where lo.OBJECT_ID = dob.OBJECT_ID

and lo.SESSION_ID = vss.SID

AND VSS.paddr = VPS.addr

order by 2,3,DOB.object_name;

删除：

alter system kill session '173, 19819';

5.linux系统下oracle数据创建表空间和用户

1 、登录服务器

2 、查看磁盘空间是否够大df -h

切换到oracle用户（linux的一个用户名）

su - oracle              

3 、在/home/oracle/oradata 目录下新建一个文件夹，后面创建表空间需要用到

cd /home/oracle/app/oracle/oradata/gaosu

mkdir gaosu

4、创建数据库

进入sql命令：

sqlplus / as sysdba

-第1步：创建临时表空间  */

create temporary tablespace  gaosutyrz_temp  

tempfile '/home/oracle/app/oracle/oradata/gaosu/gaosutyrz_temp.dbf' 

size 10m  

autoextend on  

next 10m maxsize 1024m  

extent management local;  

--第2步：创建数据表空间  */

create tablespace gaosutyrz

logging  

datafile '/home/oracle/app/oracle/oradata/gaosu/gaosutyrz_data.dbf' 

size 10m  

autoextend on  

next 10m maxsize 1024m  

extent management local;  

--第3步：创建用户并指定表空间  */

create user gaosutyrz identified by gaosutyrz  

default tablespace gaosutyrz

temporary tablespace gaosutyrz_temp;

--第4步：给用户授予权限  */

grant connect,resource,dba to gaosutyrz;

--第5步：退出sql命令  */

quit

6.备份数据库导出生成dump文件

1.exp

exp NAUTHU/NAUTHU@10.125.3.240/sspccsm file=/home/oracle/NAUTHU_20170829.dmp owner= NAUTHU consistent=y direct=y log=/home/oracle/NAUTHU_20170829.log

//导出某张表数据(可以导出保护clob字段的数据)

exp partition/partition@192.128.2.68/orcl tables=(T_ATTRECODE_REPORT) file=C:\Users\Administrator\Desktop\123.dmp

2.expdp

1. create directory expdp_dir as '/home/oracle/fmt';

2. expdp username/pwd directory=expdp_dir logfile=test.log schemas=username

高版本向低版本导入,在导出的时候需要加入版本号:

(expdp test_c/test_c directory=temp1 logfile=test.log version=10.2.0.1.0)

7.恢复数据导入dump文件

1.imp（表空间，tablespace需要和dump文件中的一致，如果不一致，需要改dump文件中的tablespace）

imp th_livebos/th_livebos@10.125.3.240/sspccsm full=y file=/home/oracle/NACCOUNTU_20170829.dmp ignore=y;

//远程导入

imp spot/spot@192.128.1.6/chenn file="d:\student.dmp" full=y ignore=y

//导入某一张表的数据(可以包含clob字段)

imp partition/partition@192.128.2.68/orcl file=C:\Users\Administrator\Desktop\123.dmp tables=(t_attrecode_report) ignore=yes

2.impdp

1. create directory expnc_dir as '/home/oracle/fmt';

2. impdp oaold/oaold directory=expnc_dir dumpfile=livebos20180314.dmp remap_schema=livebos:oaold remap_tablespace=livebos:oaold EXCLUDE=STATISTICS TRANSFORM=OID:n

（从原来的livebos用户→oaold用户；从原来的livebos表空间→oaold表空间）

8.windows下进入sql/plus

sqlplus / as sysdba

9.备份表数据

create table tablename as select * from table where id=-1

10.复制A表到B表中(表结构必须一致)

insert into 目标表 select * from 原表;

11. 删除表空间操作

11.1 先删除用户

drop user oaold（用户） cascade;

注意：如果提示

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

（查看被占用进程）select username,sid,serial# from v$session where username='OAOLD';

（查看被占用进程状态）select saddr,sid,serial#,paddr,username,status from v$session where username ='OAOLD';

  (杀死占用进程) alter system kill session '224,15140';

11.2 删除表空间

drop tablespace oaold including contents and datafiles;

12.查询用户所属的表空间

select username,default_tablespace from dba_users where username='OAOLD'

（用户名是oaold，这里的OAOLD需要大写）

13.字符集导致 ORA-00957: duplicate column name

解决方法：

$ export nls_lang=AMERICAN_AMERICA.ZHS16GBK

14.查看当前用户的缺省表空间

SQL>select username,default_tablespace from user_users;

15.查看当前用户的角色

SQL>select * from user_role_privs;

16.查看当前用户的系统权限和表级权限

SQL>select * from user_sys_privs;

SQL>select * from user_tab_privs;

17.显示当前会话所具有的权限

SQL>select * from session_privs;

18.显示指定用户所具有的系统权限

SQL>select * from dba_sys_privs where grantee='GAME';

19.查看用户下所有的表

SQL>select * from user_tables;

20.查看某表的创建时间

SQL>select object_name,created from user_objects where object_name=upper('&table_name');

21.查看某表的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

22.查看放在ORACLE的内存区里的表

SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

23.查看序列号，last_number是当前值

SQL>select * from user_sequences;

24.查看视图的名称

SQL>select view_name from user_views;

25.Oracle表字段的增加、删除、修改和重命名

25.1 增加字段语法：alter table tablename add (column datatype [default value][null/not null],….);

说明：alter table 表名 add (字段名 字段类型 默认值 是否为空);

   例：alter table sf_users add (HeadPIC blob);

   例：alter table sf_users add (userName varchar2(30) default '空' not null);

 

25.2 修改字段的语法：alter table tablename modify (column datatype [default value][null/not null],….);

说明：alter table 表名 modify (字段名 字段类型 默认值 是否为空);

   例：alter table sf_InvoiceApply modify (BILLCODE number(4));

25.3 删除字段的语法：alter table tablename drop (column);

说明：alter table 表名 drop column 字段名;

   例：alter table sf_users drop column HeadPIC;

25.4 字段的重命名：

说明：alter table 表名 rename  column  列名 to 新列名   （其中：column是关键字）

   例：alter table sf_InvoiceApply rename column PIC to NEWPIC;

25.5 表的重命名：

说明：alter table 表名 rename to  新表名

   例：alter table sf_InvoiceApply rename to  sf_New_InvoiceApply;

26.创建视图

create or replace view  viewname as select 字段列表 from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件;

27.Oracle修改字段类型和长度语句：

ALTER TABLE tableName modify(columnName 类型);

28.建表

create table T_WARNINGSET

(

  ID                   VARCHAR2(40 CHAR) not null,

  NAME                 VARCHAR2(20 CHAR),

  ORG_ID               VARCHAR2(32 CHAR),

  TYPE                 NUMBER,

  THRESHOLD            NUMBER,

  eventStatus          NUMBER,

  personnelStatus      NUMBER,

  creatTime            DATE

)

29.数据误删除恢复;

查询系统时间:

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

2018-10-31 17:43:54

查询前20分钟的数据:

select * from t_attache as of timestamp sysdate-20/1440;

插入到表中:

insert into t_attache (select * from t_attache as of timestamp sysdate-20/1440 );

30.树节点查询

select pid from V_DATA_DICT connect by prior id=pId start with id = 'ec8b31c095bb4c5a8cd0f4df4963aec0'

31.exp按条件导出单张表

exp cari_partition/cari_partition@orcl file='d:\t_resouseinfo.dmp'  tables=t_resouseinfo query=\"where ORG_ID='ace6dba4d69246fcb72a19eb6687f5c7'\" indexes=n

32.导入单个dmp文件数据

imp test_p/test_p@orcl file='d:\t_resource.dmp' ignore=y full=y

31.查询树状结构的某个节点的所有子节点及孙节点

https://blog.csdn.net/LIUHUA121/article/details/80050263

a9fe42b86e854918b9cd3e3fabbff35e:父节点id

select * from V_DATA_DICT t

start with t.id = 'a9fe42b86e854918b9cd3e3fabbff35e'

connect by prior t.id  = t.pid