oracle杂记

 

查询有某一列的表名

select table_name from all_tab_columns 

where user = '你的用户' and column_name = ‘NAME列名’; 

   select * from user_tab_columns where column_name = 'CFDISCOUNTTYPE' 

 

查看DB中的NLS_CHARACTERSET的值（提供兩種方法）：

select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET'

or

select * from v$nls_parameters  where parameter='NLS_CHARACTERSET';

 

由上查出的NLS_CHARACTERSET（ZHT16BIG5）來設定exp的環境變量：

WINNT> set NLS_LANG=AMERICAN_AMERICA.ZHT16BIG5

LINUX> export NLS_LANG=AMERICAN_AMERICA.ZHT16BIG5

 

 

//创建数据表空间

create tablespace zfmi

logging

datafile 'D:\oracle\oradata\zfmi\zfmi.dbf' 

size 100m 

autoextend on 

next 32m maxsize 2048m

extent management local;

 

--创建用户

create user fans identified by fans

default tablespace zfmi temporary tablespace zfmi_temp;

 

修改用户system密码为manager. SQL> alter user system identified by manager;. 用户已更改

 

 

//给用户授予权限

grant connect,dba to zfmi; 

 

--本周

select trunc(sysdate,'d')+1 from dual;

select trunc(sysdate,'d')+7 from dual; 

--本月

select trunc(sysdate,'mm') from dual;

select last_day(trunc(sysdate)) from dual;

--本季

select trunc(sysdate,'Q') from dual;

select add_months(trunc(sysdate,'Q'),3)-1 from dual;

--本年

select trunc(sysdate,'yyyy') from dual;

select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;