sql语句相关

/*查询版本*/
select * from v$version
/*查询临时表空间*/
select * from dba_temp_files
select * from v$sort_segment
select * from v$sort_usage
select name from v$tempfile
select username,temporary_tablespace from dba_users
select tablespace_name, current_users, total_blocks, free_blocks from v$sort_segment

/*查询字符编码集*/
select * from v$nls_parameters;
select userenv('language') from dual;

/*临时表的使用情况*/
select * from user_tables a where a.temporary='Y' and a.table_name not like 'RUPD%'

/*表空间使用情况*/
SELECT A.TABLESPACE_NAME,
       A.BYTES/(1024*1024*1024) TOTAL,
       B.BYTES/(1024*1024*1024) USED,
       C.BYTES/(1024*1024*1024) 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;
  
select sum(bytes)/(1024*1024) as free_space, tablespace_name
  from dba_free_space
 group by tablespace_name;

/*总连接数*/
select machine,count(machine) from v$session group by machine
/*激活连接*/
select machine,count(machine) from v$session where status = 'ACTIVE' group by machine
/*未激活连接*/
select machine,count(machine) from v$session where status = 'INACTIVE' group by machine

/*导出DDL*/
SELECT u.username,DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;
SELECT ts.tablespace_name,to_char(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)) FROM DBA_TABLESPACES TS

/*使用到的表空间*/
select * from sys.dba_tablespaces s where s.tablespace_name not in ('SYSTEM','USERS') and s.tablespace_name in (select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT')

select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT'
select b.table_name,b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT' and b.tablespace_name in('SYSTEM','USERS')
select * from sys.dba_tables b where b.tablespace_name like '%SVR%'

select * from sys.dba_tables b where b.tablespace_name='SYSTEM'

SELECT A.TABLESPACE_NAME,
       A.BYTES / (1024 * 1024) as 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
   and A.TABLESPACE_NAME not in ('SYSTEM','USERS')
   and A.TABLESPACE_NAME in (select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT')

/*查询表空间（无table的）*/
select a.tablespace_name
  from dba_tablespaces a
minus (select distinct b.tablespace_name
         from dba_tables b
       union
       select distinct c.tablespace_name from dba_indexes c)

SELECT m.compile_state FROM User_Mviews  m

select * from user_jobs

 

--drop table interface_97to112_his
select * from interface_97to112_his t

select * from user_tables u where u.table_name like '%INTERFACE%'

create table interface_97to112_his
tablespace SVR_BASE_DAT
as
select * from where 1=2

/*添加字段*/
alter table tmp_telephone add newservice varchar(10);

DBMS_OUTPUT.PUT_LINE(sqlcode);

/*删除重复数据*/
delete from tmp_imp_user_tel t1 where rowid > (select min(rowid) from tmp_imp_user_tel t2 where t1.no2 = t2.no2)

/*更新2张表*/
UPDATE tmp_imp_user_97 a
   SET a.no5 = (SELECT b.no2
                  FROM tmp_imp_user_tel b
                 WHERE a.no1 = b.no1
                   and rownum = 1)
 WHERE EXISTS (SELECT 1 FROM tmp_imp_user_tel b WHERE a.no1 = b.no1);