前言
- oracle只有NUMBER类型,兼容INT,但不支持INT(n)写法!
- oracle 索引名称长度默认30
- oracle 字符集是SIMPLIFIED CHINESE_CHINA.ZHS16GBK,使用windows的sqlplus执行SQL文件时,文件是asn1编码中文不会乱码,若是其他编码会导致中文乱码。
- 若字段设为not null约束,则不能存空字符串!
- oracle别名问题:字段可用as,也可不用;表起别名,不能用as关键字
number和int的区别
oracle本来就没有int类型,为了与别的数据库兼容,新增了int类型作为number类型的子集;
number可以存储浮点数,也可以存储整数;int只能存储整数;
int相当于number(22),存储总长度为22的整数;
别名问题
常见问题
账户频繁被锁
先附上解锁命令,再慢慢排查。
sys/change_on_install,或system/manager登录,
-- 解锁
ALTER USER NETSEAL_7 ACCOUNT UNLOCK;
-- 查看用户当前状态
SELECT * FROM DBA_USERS WHERE USERNAME='NETSEAL_7';
1.排查密码登录是否有过期限制;
-- 查询用户为NETSEAL_7的profile类型(一般都是DEFAULT)
select username,profile from dba_users WHERE USERNAME='NETSEAL_7';
-- 查询用户为NETSEAL_7的密码生命周期管理
select * from dba_profiles s where s.profile=(select profile from dba_users WHERE USERNAME='NETSEAL_7') and resource_name='PASSWORD_LIFE_TIME';
若为unlimited,需要查询是其他什么原因导致的频繁被锁。
2. 排查是否有登录失败次数的限制
-- 查询profiles策略下的最大连续失败次数
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';
-- 查询指定用户连续登录失败次数,登录成功后清0
select name,lcount from sys.user$ WHERE NAME='NETSEAL_7';
Windows上操作oracle
C:\Users\Administator>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 5月 6 10:26:06 2022
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
SQL> conn NETSEAL_7/NETSEAL_7;
已连接。
SQL>
SQL>
SQL>
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL>
SQL> start F:\1.sql;
表已创建。
索引已创建。
索引已创建。
提交完成。
SQL>
Linux上oracle建库建表
1.连接数据库
- 切换到oracle用户
su oracle
- 启动监听
lsnrctl start # status 状态;stop 停止
- 登录
sqlplus /nolog #不登录,较安全;
SQL>conn / as sysdba # 或者sqlplus / as sysdba
SQL>startup #启动实例,shutdown 停止
SQL>conn 用户名/密码 #连接
- 执行sql
SQL>start 具体路径/create.sql # 或者@具体路径/create.sql
- 退出
SQL>exit;
2.执行sql文件
-- Oracle支持表和索引位于不同表空间,此处创建3个表空间,提高性能。
--创建数据库文件表空间
CREATE TABLESPACE NETSEAL_4_DATA LOGGING DATAFILE 'NETSEAL_4_DATA.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--创建数据库临时文件表空间
CREATE TEMPORARY TABLESPACE NETSEAL_4_TEMP TEMPFILE 'NETSEAL_4_TEMP.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ;
--创建索引表空间
CREATE TABLESPACE NETSEAL_4_INDEX LOGGING DATAFILE 'NETSEAL_4_INDEX.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--创建用户,与数据库文件和数据库临时文件形成映射关系
CREATE USER NETSEAL_4 PROFILE DEFAULT IDENTIFIED BY NETSEAL_4
DEFAULT TABLESPACE NETSEAL_4_DATA TEMPORARY TABLESPACE NETSEAL_4_TEMP ACCOUNT UNLOCK;
--用户授权,授予connect和dba权限
GRANT CONNECT TO NETSEAL_4;
GRANT DBA TO NETSEAL_4;
--连接数据库
CONNECT NETSEAL_4/NETSEAL_4@orcl;
------------------------------------------ 管理员表---------------------------------------------------
-- 因为上面表和索引空间分离,所以此处的建表和建索引,一定要指定表的表空间位置和索引的表空间位置
CREATE TABLE SEAL_SYS_USER
(
ID NUMBER(19) NOT NULL,
ACCOUNT VARCHAR2(50) NOT NULL,
NAME VARCHAR2(50) NOT NULL,
PASSWORD VARCHAR2(50) NOT NULL,
ROLE_ID NUMBER(19) NOT NULL,
STATUS INT NOT NULL,
FAILED_NUM INT NOT NULL,
CHANGE_PASS INT NOT NULL,
COMPANY_ID NUMBER(19) NOT NULL,
TOKEN_SEED VARCHAR2(100),
GENERATE_TIME NUMBER(19) NOT NULL,
UPDATE_TIME NUMBER(19) NOT NULL,
MAC VARCHAR2(50),
CONSTRAINT PK_SEAL_SYS_USER PRIMARY KEY(ID) USING INDEX TABLESPACE NETSEAL_4_INDEX
) TABLESPACE NETSEAL_4_DATA;
CREATE UNIQUE INDEX UNIQUE_SEAL_SYS_USER ON SEAL_SYS_USER(ACCOUNT) TABLESPACE NETSEAL_4_INDEX;
CREATE UNIQUE INDEX UNIQUE_1_SEAL_SYS_USER ON SEAL_SYS_USER(NAME, COMPANY_ID) TABLESPACE NETSEAL_4_INDEX;
--初始化数据
insert into SEAL_SYS_USER (ID, ACCOUNT, NAME, PASSWORD, ROLE_ID, STATUS, FAILED_NUM, CHANGE_PASS, COMPANY_ID, TOKEN_SEED, GENERATE_TIME, UPDATE_TIME, MAC) values (0,'admin', '用户管理员', '3B/QDj7uuUD/RvRXv5fWa6f8w24LIIAjg94UKGDnauY=', 1, 1, 0, 0, 0, '', 1, 1, 'El49gHKKy7xWn7k/QCkFaNUcSDBIRrUc60YAacSXP6I=');
--提交
COMMIT;
3. 建库建表详解
创建表空间语法:
create tablespace 表空间名称
datafile '表空间文件存放路径'
size 表空间大小
autoextend on;
-- 例如创建一个名为yunshu,文件路径为’D:\test\yunshu.dbf’,大小为100m的表空间:
create tablespace yunshu
datafile 'D:\test\yunshu.dbf'
size 100m
autoextend on;
创建用户语法:
create user 用户名
identified by 密码
default tablespace 表空间(若无这句话,默认用户在USERS表空间下)
-- 例如创建一个用户名为test,密码为test,在表空间yunshu下的用户:
create user test
identified by test
default tablespace yunshu;
给用户授权:
grant connect to test;--拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。
grant resource to test;--拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。
grant dba to test;--dba为最高权限
建表语句:
Oracle支持表和索引位于不同的表空间下,此处示例两种
-- 不知道表的表空间和索引的表空间,默认在哪不知道
create table 表名(
列名 数据类型 primary key,
列名 数据类型 not null,
列名 数据类型
);
-- 指定表的表空间和索引的表空间
create table 表名(
列名 数据类型 not null,
列名 数据类型 not null,
CONSTRAINT 索引名 PRIMARY KEY(列名) USING INDEX TABLESPACE 索引表空间
) tablespace 表空间;
4. 基础知识
4.1 架构及思想
4.1.1 架构图
Oracle是个多实例数据库,orcl就是第一次创建的默认实例。
虽然支持多实例,但目前的实际使用中,我只使用了orcl实例。
一个数据库有多个表空间,在创建表空间时,定义表空间文件的存放路径。
一个表空间有多个用户,在创建用户时,定义用户存在某个表空间文件里,若未定义,则默认user表空间。
授权用户之后,才可以创建表,数据以表的形式存储在表里,在此对数据进行增删减改。
Oracle支持表和索引位于不同的表空间下,一旦把表和索引放在不同表空间后,在创建新表时,如果表有主键或唯一约束,记得要指定到索引的表空间
4.1.2 安装时的默认情况
4.1.3 设计思想
4.2 字段定义
4.2.1 所有
4.2.2 常用的
描述 | 字段 | 描述 | 字段 |
---|---|---|---|
小数值 | 字符 | ||
大数值 | 时间戳 | ||
小数 | 日期 | ||
字符串 | 文本 |
4.2.3 兼容情况
建表时的兼容情况:
4.2.4 敏感情况
表名:xx;
字段名:xx;
字段值:xx;
4.3 常见命令
4.3.1 命令行操作
注意大小写和分号问题!!!
登录:
4.3.2 SQL
4.3.2.1 表空间:
-- 查询有哪些表空间
SELECT * FROM dba_tablespaces;
-- 创建表空间
-- 修改表空间
-- 删除表空间
DROP TABLESPACE xxx INCLUDING CONTENTS;
4.3.2.2 用户:
-- 查询用户
SELECT username FROM dba_users;
-- 创建用户:方式1: 仅指定密码,需要额外授权;
-- 创建用户:方式2
-- 用户授权:
-- 修改密码:
-- 删除用户
4.3.2.3 表:
-- 创建表:
-- 删除表:
-- 查询表空间里的表
-- 查询用户的所有表
select TABLE_NAME from all_tables where owner='NETSEAL_7';
-- 生成清表SQL
select 'DELETE FROM '||TABLE_NAME||';' from all_tables where owner='NETSEAL_7';
4.3.2.4 索引:
-- 创建索引:
-- 删除索引:
4.3.2.5 查询类:
-- 查询数据库版本
select * from v$version;
-- 查询所有数据库
-- 查询指定数据库下所有表
-- 查询表是否存在
-- 查询指定用户的信息
SELECT * FROM DBA_USERS WHERE USERNAME='NETSEAL_7'
-- 查询指定表空间的信息
-- 查询用户为NETSEAL_7的密码生命周期管理
select * from dba_profiles s where s.profile=(select profile from dba_users WHERE USERNAME='NETSEAL_7') and resource_name='PASSWORD_LIFE_TIME';
-- 查询profiles策略下的最大连续失败次数
SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';
-- 查询指定用户连续登录失败次数,登录成功后清0
select name,lcount from sys.user$ WHERE NAME='NETSEAL_7';
-- 解锁
ALTER USER NETSEAL_7 ACCOUNT UNLOCK;
-- 查询指定用户的连接情况
select * from v$session where username='NETSEAL_7';
-- 查询现有连接里各用户的连接情况
select username,count(username) from v$session where username is not null group by username;