Oracle使用过程遇到问题记录

Oracle 使用过程遇到问题

1. ORA-01031: insufficient privileges

Question: I just created a new database and when I try to create a directoty I get the error: “ORA-01031: insufficient privileges”. How do I stop the ORA-01031 error?

SQL> create directory EXT_DIR as 'c:\TEMP';
create directory EXT_DIR as 'c:\TEMP'
*
ERROR at line 1:
ORA-01031: insufficient privileges

Answer: The ORA-01031: “insufficient privileges” error occurs when you attempt to execute a program or function for which you have not been granted the appropriate privileges.
You must have the “CREATE ANY DIRECTORY” system privilege to create directories with Oracle. Or, log into Oracle with a user who possess the SYSDBA role.

SQL> connect system/manager as SYSDBA;

Connected.

SQL> grant create any directory to fred;

Granted.

SQL> connect fred/flintstone;

Connected.

SQL> create directory EXT_DIR as 'c:\TEMP';

Directory created.

2. oracle创建directory目录
   • 新建directory

     CREATE [OR REPLACE] DIRECTORY <目录变量名> AS '<目录路径>';
     
     例如：
     create or replace directory dump_dir as 'D:\dump\dir'
     这样把目录d:\dump\dir设置成dump_dir代表的directory

• 赋权，将目录权限赋给指定用户

  grant <权限，多个用逗号分隔> on directory <目录变量> to <用户>
  
  例如：
  
  grant read,write on directory dump_dir to user01

3. directory相关其他操作
   • 查询有哪些directory

     select * from dba_directories

• 删除指定directory

  drop directory <目录>
  
  例如：
  drop directory dump_dir

加载外表操作

1.

Table created.

INSERT INTO DW_CDR SELECT BEGINTIME,USERNUM,HOMEAREA,RELATENUM,RELATEHOMEAC,IMSI,IMEI,CURAREA,NEID,LAI,CI,LONGITUDE,LATITUDE,BILLTYPE,CALLTYPE,DTMF,CALLDURATION,CAUSE,RLGTIME,ALERTTIME,CONNECTTIME,DISCONNECTTIME,SID,IDFLAG,RAWRELATENUM,REDIRFLAG,ORIGCALLEDNO,DISCONNECTTYPE,NEWLAI,NEWCI,NEWLONGITUDE,NEWLATITUDE,TMSI,SPCODE FROM TB_CDR
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid dump file
"/opt/dw_data_temp/20200101/172/CDR/TB_CDR_10424.log"      --为什么会有这个文件？是dmp包里的？还是我的脚本生成的？d
                                                            -- 导入外表时，默认会在dmp文件同级目录下生成log日志文件，需要使用参数控制
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 41

2.

报错如下：
select count(1) from tb_cdr
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

解决：
在最后添加下面语句
reject limit unlimited;

如：
create table <table_name>
(...) organization external(...) reject limit unlimited;

3.

INSERT INTO DW_CDR SELECT BEGINTIM,USERNUM,HOMEAREA,RELATENUM,RELATEHOMEAC,IMSI,IMEI,CURAREA,NEID,LAI,CI,LONGITUDE,LATITUDE,BILLTYPE,CALLTYPE,DTMF,CALLDURATION,CAUSE,RLGTIME,ALERTTIME,CONNECTTIME,DISCONNECTTIME,SID,IDFLAG,RAWRELATENUM,REDIRFLAG,ORIGCALLEDNO,DISCONNECTTYPE,NEWLAI,NEWCI,NEWLONGITUDE,NEWLATITUDE,TMSI,SPCODE FROM TB_CDR
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'DW_DATA'
表空间权限有限：

下面语句解决：
alter user c##dw_data quota unlimited on DW_DATA;

grant unlimited tablespace to c##dw_data;

oracle导入导出

1. 使用sqluldr2工具高效导出oracle数据

   sqludlr2的帮助参数
   
   sqluldr2_linux64_10204.bin help=yes

注意：如果需要导出的数据不带表头，需要设置参数head=no，同时text使用默认设置。
如果使用text=CSV,则head参数无效，生成的数据中默认带表头

oracle修改表名

ALTER TABLE old_table_name RENAME TO new_table_name;

oracle 删除数据

用于删除表中的某行或整个数据表中的数据

语法：

DELETE FROM <table/view> [WHERE <condition>]

注意事项：
如果有外键关联，则删除数据之前,需先删除外键关联数据

oracle的日期格式

1. oracle在进行日期格式转换时，指定的日期格式与Java中的不一致，需要注意，否则匹配的出时间不正确

   例如：
   select * from keywords where to_char(create_time,'yyyy-mm-dd HH24:MI:SS') >'2019-02-02 00:00:00' order by create_time; 

Oracle导数时报错：ORA-12899: value too large for column

参考下面链接

https://www.linkedin.com/pulse/why-do-i-get-ora-12899-value-too-large-column-when-sinan-petrus-toma?trk=read_related_article-card_title

我现场操作是，选择修改有问题的表的字段，将字段的长度，由byte该为char。

最开始我打算使用省事的方式，就是直接修改数据库的nls_length_semantics参数，将默认的BYTE改为CHAR，但是发现好像不起作用，重新导入的表，字段的data_type还是BYTE。