alter tablespace USERS add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
之前主要使用mysql,要开发实时数仓,ORACLE相对来说更加适合。然而其标准规范和ORACLE数据库有所不同。因而切换过来后有一些不适应的地方,现总结如下:
1.数据表及字段命名规范。
在ORACLE中查询数据时,明明数据库里确实有要查的表和字段,却总是弹出ora-00904 标识符无效的错误。
经过排查发现:在ORACLE中,数据表及字段一律要大写,才没有这个错误。特别是表名。在开发时,有从SQL SERVER、MySQL抽取一些操作性数据(Operational Data Store) 表同步到ORACLE中。其中,同步表结构和字段名就使用的小写或大小写混合的格式。在ORACLE上写sql时,会遇到表或视图不存在、标识符(字段名)无效等错误。
其中一个原因在于 oracle是大小写敏感的,如果定义表名称或列名称的时候没有用引号引起来的话 oracle会把他们全部转换为大写。我在数据库间同步表结构时使用的Navicat,在同步时已经替我们提前加好了引号。若在sql中未将大小写混合的表名或字段加引号,则结果类似于“Name” 与NAME,无法匹配的上。
另一个问题在于,一些ETL工具(使用的dataX或Kettle)均不会为转换抽取过程中利用到的字段自动加上引号。也会造成这种错误。
因而解决的最好方案就是数据表及字段一律大写。
在抽取原表结构后,可以使用如下SQL将ORACLE模式中所有字段转为大写:
- begin
- for cl in (SELECT table_name,column_name from user_tab_columns WHERE column_name<>upper(column_name) and upper(column_name) not in('SIZE','CHECK')) loop
- begin
- execute immediate 'alter table '||cl.table_name||' rename column "'|| cl.column_name ||'" to '||upper(cl.column_name);
- exception
- when others then
- dbms_output.put_line(cl.table_name||'.'||cl.column_name||'已存在');
- end;
- end loop;
- end;
参考:https://www.cnblogs.com/Ai-Hen-Jiao-zhi/p/13030848.html
2.日期和时间转换。
查询条件涉及到时间时,弹出ORA-01843: 无效的月份错误。经过排查发现:
ORACLE中只有DATE字段用于表示格式化的日期时间,不像MySQL中有date、datetime、time。其字段类型转换为字符串时也有很大的不同。
我从SQL Server中抽取的时间类型字段,为了方便导入,先用VARCHAR2类型存入ORACLE中。预期是"1966-08-11 00:00:00",结果转成字符串却是“11-8月 -66 12.00.00.000000000 上午”这种不伦不类的格式。要将字符串重新转为日期,可以使用to_timestamp
这一函数。使用方式如下
- 字符型转成timestamp
select to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM')
from dual ;
- timestamp转成date型
select cast(to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') as date) timestamp_to_date
from dual;
而在我的情况下,还要注意ORACLE数据库的语言问题。默认情况下: nls_date_language='AMERICAN';
导致我在运行: cast(to_timestamp('11-8月 -66 12.00.00.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') as date)
的时侯,会出现日期字符串无法识别的情况,因为英语里没有“月”,“上午”这样的字。 因而现将数据库语言暂时切换成中文:
ALTER SESSION SET nls_date_language='SIMPLIFIED CHINESE';
参考:https://www.modb.pro/db/619760
3. UUID 问题
ORACLE中自带 sys_guid()
函数可用于自动生成UUID,然而生成的可能是二进制的UUID,在查询界面会显示为乱码。一种解决方案是使用下列语句将UUID用16进制字符串去表示:
SELECT rawtohex(sys_guid() as RECORD_ID from dual;
如果您需要UUID是小写的字母+数字组合,那需要加一个函数:
SELECT lower(rawtohex(sys_guid())) as RECORD_ID from dual;
参考:https://www.cnblogs.com/zkwarrior/p/15582896.html
4. 表空间问题
在创建索引的时候,弹出莫名其妙的提示ORA-01652: unable to extend temp segment by 128 in tablespace USERS。经过搜索发现我的数据块文件没能正常自动扩展。
每个表空间(如system,USERS,sysdba可确定。)都有一个或多个用于存储数据的数据文件。数据文件的最大大小取决于数据库的块大小。默认情况下,每个数据文件的最大容量为32 GB。
扩展数据块一般是可以自动完成的,否则,需要首先运行下列代码,找到各个表空间数据块的储存位置:
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
之后,开启数据块自动扩展。
alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;
然而,单个数据块文件,最多只能储存32G,若超过了,还是会报如上错误。您可以选择手动添加数据块文件:
参考:https://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext
5. Limit 问题
在 Oracle 数据库中,没有与标准 SQL 中的 LIMIT
子句相对应的直接关键字。如果你想在查询结果中限制返回的行数,可以使用 ROWNUM
或FETCH FIRST x ROWS ONLY
来实现。二者都是在查询结果被返回之前应用的。其区别在于,
ROWNUM
是 Oracle 中的伪列,它表示结果集中的行号,是按照行被检索的顺序分配的。- 要限制返回的行数,可以将
ROWNUM
放在WHERE
子句中。 - 在使用
ROWNUM
时,通常需要注意其应用的顺序,例如,在排序之后使用它可能不会得到预期的结果。
FETCH FIRST n ROWS ONLY
是 ANSI SQL 标准的语法,可以在多个数据库管理系统中使用,不仅限于 Oracle。FETCH FIRST
通常与ORDER BY
子句一起使用,以确保获取了指定数量的行,并按照指定的排序顺序返回。
参考:chatGPT
6. 数据库管理问题
在初始化安装数据库时,没有自定义相关账户的情况下,可用sysdba账户登录数据库做相关的管理操作。用户名和口令为:conn /as sysdba
文章评论