Oracle SQL

1、Oracle 表名称最长允许30个字符

2、查找表中最近七天的所有数据

select * from table
where  datetime> to_char(sysdate-7,'yyyymmdd');

3、

问题描述：在存储过程中，判断变量是否等于''(即空字符串，不是null)，使用语句：if(v_tmp<>'') 失效

解决办法：不知道是什么原因，改为if(length(trim(v_tmp))<>0) 判断

4、查看index

select index_name from user_ind_columns where lower(table_name)='sdbdi';

5、hr用户下使用命令：set autotrace on statistics时，报错，

 

SQL> set autot on stat
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

 原因：没有PLUSTRACE角色

解决方法：

切换到sysdba，执行命令：

 

SQL> @?/sqlplus/admin/plustrce.sql
SQL> 
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


SQL> create role plustrace;

Role created.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off
SQL> 

 将plustrce角色赋给用户hr：

 

SQL> conn / as sysdba
Connected.
SQL> grant plustrace to hr ;

Grant succeeded.

 切换回hr用户：

 

SQL> conn hr/hr
Connected.
SQL> set autot on stat
SQL> 

 问题解决。

6、查看表空间使用率

 

SELECT a.tablespace_name 表空间名,
       b.total 表空间大小,
       a.free 表空间剩余大小,
       (b.total - a.free) 表空间使用大小,
       ROUND((b.total - a.free) / total, 4) * 100 使用率
  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / (1024 * 1024), 4) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, ROUND(SUM(bytes) / (1024 * 1024), 4) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

 7、查看数据库状态，比如open 还是unmount

select status from v$instance;
select open_mode from v$database;