《Oracle系列》Oracle常见问题处理记录_oracle创建schema

Oracle常见问题处理记录

一、Oracle密码过期问题，设置永不过期

Oracle用户密码过期的根本原因，还是在于该用户所属的profile的life_time到期

1.总体思路

## 1.查看该用户的过期时间，profile
select username,expiry_date,profile from dba_users where username = 'ZXY';
## 2.查看该profile的有效时长
SELECT  *  FROM  dba_profiles  s  WHERE  s.profile = 'DEFAULT'  AND  resource_name = 'PASSWORD_LIFE_TIME';
## 3.将该profile设置为永不过期
alter profile default limit password_life_time unlimited;
## 4.重新设置一下该用户的密码
alter user ZXY identified by newpassword;

1
2
3
4
5
6
7
8
9

2.查看用户所属profile及过期时间

SYS@helowin>select username,expiry_date,profile from dba_users where username = 'ZXY';

USERNAME                       EXPIRY_DATE         PROFILE
------------------------------ ------------------- ------------------------------
ZXY                            2022-05-20 02:28:28 DEFAULT_ZXY
1
2
3
4
5
6

可以看到整个用户会在2022-05-20 02:28:28时候失效

3. 查看该profile的有效时长

这里的DEFAULT_ZXY就是上一步通过用户名从dba_users中查出的PROFILE的值

SYS@helowin>select * from dba_profiles s where s.profile = 'DEFAULT_ZXY' and resource_name = 'PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT_ZXY                    PASSWORD_LIFE_TIME               PASSWORD 30
1
2
3
4
5

可以看到还有30天到期

4.将该profile设置为永不过期

将密码设置为永不过期后，可以通过前两步做校验，这里已经成功设置为永不过期

SYS@helowin>alter profile DEFAULT_ZXY limit password_life_time unlimited;

Profile altered.

SYS@helowin>select username,expiry_date,profile from dba_users where username = 'ZXY';

USERNAME                       EXPIRY_DATE         PROFILE
------------------------------ ------------------- ------------------------------
ZXY                                                DEFAULT_ZXY

SYS@helowin>select * from dba_profiles s where s.profile = 'DEFAULT_ZXY' and resource_name = 'PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT_ZXY                    PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

SYS@helowin>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5.重置密码

如果你的密码已经过期了，那么需要你重新设置一下密码，无需重启数据库就会生效

alter user ZXY identified by zxy;
1

6.其余相关命令

## 创建profile并设置过期时间30天
create profile default_zxy limit password_life_time 30;
## 创建profile并设置永不过期
create profile default_zxy limit password_life_time unlimited
## 为用户指定profile归属
alter user ZXY profile default_zxy
1
2
3
4
5
6

二、Oracle模拟事务提交、表锁，处理表锁问题

1.查看AutoCommit

# 我当前自动提交设置为OFF，即默认不自动提交
SYS@helowin>show autocommit;
autocommit OFF

# 可通过set命令，选择是否开启自动提交事务
SYS@helowin>set auto
SP2-0281: autocommit missing set option
Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }
1
2
3
4
5
6
7
8

2.手动提交事务流程

通过可视化工具navicat执行插入语句，发现虽然我们设置了不自动提交，这里还是插入成功了。因为有些可视化工具会帮你commit。

我们打开终端，发现通过刚刚的操作已经存在一条数据

SYS@helowin>select * from zxy.study;

        ID NAME                 TESTTIME
---------- -------------------- --------------------------------------------------
         1 a                    2022-09-26


1
2
3
4
5
6
7
8

我们打开两个终端页面，先在终端一中模拟插入一条数据，不执行commit。看终端二中是否能看到插入的数据

2.1 终端一 尚未commit

# 在终端一模拟插入一条数据，不commit
SYS@helowin>insert into zxy.study values(2,'b','2022-09-26');

1 row created.
# 发现在当前终端中可以查到数据
# 在事务提交之前，只有操作数据库的这个人才能看到所做的事，别人只能等待提交后才可以
SYS@helowin>select * from zxy.study;

        ID NAME                 TESTTIME
---------- -------------------- --------------------------------------------------
         2 b                    2022-09-26
         1 a                    2022-09-26

1
2
3
4
5
6
7
8
9
10
11
12
13

2.2 终端二 查询

# 在终端二中不能查到数据
SYS@helowin>select * from zxy.study;

        ID NAME                 TESTTIME
---------- -------------------- --------------------------------------------------
         1 a                    2022-09-26


1
2
3
4
5
6
7
8

2.3 终端一 commit

SYS@helowin>commit;

Commit complete.

SYS@helowin>select * from zxy.study;

        ID NAME                 TESTTIME
---------- -------------------- --------------------------------------------------
         2 b                    2022-09-26
         1 a                    2022-09-26
1
2
3
4
5
6
7
8
9
10
11

2.4 终端二 查询

SYS@helowin>select * from zxy.study;

        ID NAME                 TESTTIME
---------- -------------------- --------------------------------------------------
         2 b                    2022-09-26
         1 a                    2022-09-26

1
2
3
4
5
6
7
8

3.模拟表锁

开启事务，但是一直未提交，会造成表锁

3.1 在终端一开启一个事务，但是不提交

SYS@helowin>insert into zxy.study values(3,'c','2022-09-26');

1 row created.

SYS@helowin>select * from zxy.study;

        ID NAME                 TESTTIME
---------- -------------------- --------------------------------------------------
         2 b                    2022-09-26
         3 c                    2022-09-26
         1 a                    2022-09-26
1
2
3
4
5
6
7
8
9
10
11
12

3.2 查询是否有表锁情况

select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
1
2
3
4
5
6
7
8
9

3.3 关闭该条未commit的事务

alter system kill session 'sid,serial#';
1

三、Oracle创建Schema、用户名及表空间

1 创建新用户

create user usename identified by password;

SQL> create user zxy identified by yyds;

User created.
1
2
3

2 查看当前表空间

SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

1
2
3
4
5
6
7
8
9
10
11

3 创建表空间

create tablespace spacename datafile 'filedir';

spacename : 是自定义的表空间名字，当然也是新的Schema的名称

filedir : 文件存储路径，可随意选择一个磁盘空间充足的目录

SQL> create tablespace zxy datafile '/home/oracle/app/oracle/oradata/helowin/zxy.dbf' size 100m;

Tablespace created.

1
2
3
4

4 将空间分配给用户

alter user username default tablespace spacename;

SQL> alter user zxy default tablespace zxy;

User altered.

1
2
3
4

5 给用户授权

grant create session,create table,unlimited tablespace to username;

SQL> grant create session,create table,unlimited tablespace to zxy;

Grant succeeded.

1
2
3
4

6 查看表空间

SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
ZXY

1
2
3
4
5
6
7
8
9
10
11
12

7 连接

SQL> conn zxy/yyds;
Connected.
1
2

四、oracle查看表空间

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

SELECT file_id,file_name 
FROM DBA_DATA_FILES D WHERE TABLESPACE_NAME = 'SYSAUX'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20