oracle常用查询

>>>oracle创建表，动态表名

begin  

execute immediate 'create table a_bak_'||to_char(sysdate, 'yyyymmdd')||' as select * from a';  

end;  

/  

 

>>>查dblink

select * from dba_db_links;

 

>>>查询索引

select index_name,column_name,table_name from USER_IND_COLUMNS where table_name ='T_table';

修改索引

alter index "oldname" rename to newname;

在plsql中修改索引，会报ORA-00953: 缺少或无效索引名，改用命令修改

>>>plsql查询表所有字段

打开一个SQLWindow窗口，选中要查询的table，拖到SQLWindow窗口，在弹出的列表中选中Select，然后就自动生成了select所有字段的查询语句

 

>>>ORA-01653: unable to extend table X by Y in tablespace

       1.insert into table1 values(...) select (...) from table2:(会占用temp tablespace,如果table2数据很大，temp tablespace不够大的话，可能会报出上面的问题)

2.update table1 set is_deleted='Y':(因为在table1上建有物化视图，而这个视图要新增对应的物化视图的log表，由于这个log表对应的tablespace满了，导致上面的问题)

3.insert into table1 values(...):(最正常不过的insert语句，由于table1对应的tablespace满了，导致上面的问题)

 

查看表空间对应的datafile是否可以自动扩展（当然有些场景开启自动扩展功能可能会带来一些潜在的问题，所以不是所有的公司都开启这个）

SELECT file_id,
      file_name,
      tablespace_name,
      autoextensible,
      increment_by
FROM  dba_data_files
WHERE  tablespace_name = 'PSR_DATA'
ORDER  BY file_id DESC;

查看表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;

 

>>>增加表空间大小的四种方法

Meathod1：给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;

Meathod2：新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

Meathod3：允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
Meathod4：手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
RESIZE 100M;

 

>>>创建表空间及用户

/*查看表空间物理文件的名称及大小*/

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

 

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

create temporary tablespace user_temp  

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' 

size 50m  

autoextend on  

next 50m maxsize 20480m  

extent management local;  

 

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

create tablespace user_data  

logging  

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' 

size 50m  

autoextend on  

next 50m maxsize 20480m  

extent management local;  

 

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

create user username identified by password  

default tablespace user_data  

temporary tablespace user_temp;  

 

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

grant connect,resource,dba to username;  

 

>>>Oracle中大批量删除数据的方法

create or replace procedure delBigTab(p_TableName in varchar2,

                                      p_Condition in varchar2,

                                      p_Count     in varchar2) as

  pragma autonomous_transaction;

  n_delete number := 0;

begin

  while 1 = 1 loop

    EXECUTE IMMEDIATE 'delete from ' || p_TableName || ' where ' ||

                      p_Condition || ' and rownum <= :rn'

      USING p_Count;

    if SQL%NOTFOUND then

      exit;

    else

      n_delete := n_delete + SQL%ROWCOUNT;

    end if;

    commit;

  end loop;

  commit;

  DBMS_OUTPUT.PUT_LINE('Finished!');

  DBMS_OUTPUT.PUT_LINE('Totally ' || to_char(n_delete) ||

                       ' records deleted!');

end;

调用： exec delBigTab('TABLEname','substr(addTIME,1,7) = ''2015-01''','10000')

 

>>>distinct较慢时，可改为select  /*+ parallel*/ distinct username from t_user;

      牺牲资源提高效率。

 

>>>查看和修改oracle的游标数大小

show parameter open_cursors;

Oracle查看当前打开的游标数目

select count(*) from v$open_cursor;

修改Oracle最大游标数

alter system set open_cursors=1000 scope=both;