ORACLE数据库常用SQL收集

各位程序大佬们，删库需谨慎,跑路要不留痕迹

oracle删库SQL：drop user cacade;

1.创建备份表

    create table tablename_bak as select * from tablename;

2.添加字段

    alter table test_table add (tablecolumn varchar2(30) default null null);

    comment on column test_table.tablecolumn is '备注信息';

3.修改字段

    alter table test_table rename column field_name to new_file_name;

4.删除字段

    alter table test_table drop column tablecolumn ;

5.在用户test下查看表y（用户和表名大写）

    select * from all_tables t  where t.table_name = 'TEST_TABLE' and t.OWNER = 'TEST';

6.查询锁表sql并杀掉进程

    select session_id,sid,s.serial#,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,

           o.OBJECT_NAME,s.logon_time

    from v$locked_object l,all_objects o,v$session s

    where l.object_id = o.OBJECT_ID and l.session_id =  s.sid

    order by sid,s.serial#;

       alter system kill session 'sid,serial#';

    alter system kill session '107,62374';

7.--查看数据库当前进程的连接数据

select count(*) from v$process;

8.--查看数据库当前会话的连接数

select count(*) from v$session;

9.--查看当前数据库的并发连接数

select count(*) from v$session where status='ACTIVE';

10.--查看当前数据库建立的会话情况

select sid,serial#,username,program,machine,status from v$session;

11.--查看数据库允许的最大连接数

select value from v$parameter where name = 'processes';

12.--修改数据库允许的最大连接数(需要重启数据库才生效)

alter system set processes = 300 scope = spfile;

13.--查看当前有哪些用户正在使用数据

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;

14.--查看所有表空间及表空间大小：

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

15.--查看所有表空间对应的数据文件：

select tablespace_name,file_name from dba_data_files;

16.--查看所有表的行数

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

17.--最后这个级联特别有用（删除用户以及所有关联的数据库对象）

           drop user XXXX cascade;

           删除用户 然后重建，这样最快：

1、在cmd中输入

    sqlplus / as sysdba

2、删除用户，级所有和用户A关联的数据

    drop user a cascade;

3、重建用户

    create user A identified by 密码;

    grant connect,resource to A;

4、登入用户，就ok了

    conn userName/passWord

这个方法挺快的，还方便啊。

18.查看表空间使用情况

        select c.file_id,a.tablespace_name "表空间名",

              c.file_name "表空间存放物理路径",

              round(b.total / (1024 * 1024),2)||'M' "表空间总大小",

              round(a.free / (1024 * 1024),2)||'M' "表空间剩余量",

              round((b.total - a.free) / (1024 * 1024),2)||'M' "表空间已使用",

              round((b.total - a.free) / total, 2) * 100||'%' "使用率",

              round(c.maxbytes / (1024 * 1024),2)||'M' "表空间的最大占多少",

              --increment_by字段单位是block,block是8KB

              c.autoextensible "是否自动扩展",round(c.increment_by*8/1024)||'M' "自动扩展增量"

       from (

            select tablespace_name, SUM(bytes) free from dba_free_space group by tablespace_name

       ) a left join(

           select tablespace_name, SUM(bytes) total from dba_data_files group by tablespace_name

       ) b on a.tablespace_name = b.tablespace_name

       left join dba_data_files c on c.TABLESPACE_NAME=b.tablespace_name

       order by c.file_id

19.查看临时表空间使用情况 

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",

NVL(FREE_SPACE,0) "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

20.dblink创建

create database link MemberDbLink

connect to member identified by MEMBER

using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 10.10.90.105)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';