oracle更改用户默认参数文件

脑中藏真书万卷

已于 2024-06-18 10:56:57 修改

阅读量347

点赞数

分类专栏： sql 文章标签： oracle 数据库

于 2020-07-31 11:09:56 首次发布

本文链接：https://blog.csdn.net/diandiandididi/article/details/107708961

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1、查看用户时候用的参数文件
SELECT username,PROFILE FROM dba_users where username=‘CESHI’;

SQL> SELECT username,PROFILE FROM dba_users where username='CESHI';

USERNAME		       PROFILE
------------------------------ ------------------------------
CESHI			       DEFAULT

(2) 创建一个新的profile文件：
SQL>create profile ceshi_profile1
limit connect_time unlimited
idle_time unlimited;

SQL> create profile ceshi_profile1
limit connect_time unlimited 
idle_time unlimited;  2    3  

Profile created.

(3）查看新的参数文件
SELECT * FROM dba_profiles s WHERE s.profile=‘CESHI_PROFILE1’;

SQL> SELECT * FROM dba_profiles s WHERE s.profile='CESHI_PROFILE1';

PROFILE 		       RESOURCE_NAME			RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
CESHI_PROFILE1		       COMPOSITE_LIMIT			KERNEL	 DEFAULT
CESHI_PROFILE1		       SESSIONS_PER_USER		KERNEL	 DEFAULT
CESHI_PROFILE1		       CPU_PER_SESSION			KERNEL	 DEFAULT
CESHI_PROFILE1		       CPU_PER_CALL			KERNEL	 DEFAULT
CESHI_PROFILE1		       LOGICAL_READS_PER_SESSION	KERNEL	 DEFAULT
CESHI_PROFILE1		       LOGICAL_READS_PER_CALL		KERNEL	 DEFAULT
CESHI_PROFILE1		       IDLE_TIME			KERNEL	 UNLIMITED
CESHI_PROFILE1		       CONNECT_TIME			KERNEL	 UNLIMITED
CESHI_PROFILE1		       PRIVATE_SGA			KERNEL	 DEFAULT
CESHI_PROFILE1		       FAILED_LOGIN_ATTEMPTS		PASSWORD DEFAULT
CESHI_PROFILE1		       PASSWORD_LIFE_TIME		PASSWORD DEFAULT
CESHI_PROFILE1		       PASSWORD_REUSE_TIME		PASSWORD DEFAULT
CESHI_PROFILE1		       PASSWORD_REUSE_MAX		PASSWORD DEFAULT
CESHI_PROFILE1		       PASSWORD_VERIFY_FUNCTION 	PASSWORD DEFAULT
CESHI_PROFILE1		       PASSWORD_LOCK_TIME		PASSWORD DEFAULT
CESHI_PROFILE1		       PASSWORD_GRACE_TIME		PASSWORD DEFAULT

(4) 将这个新的配置文件分配给该用户：
SQL>alter user ceshi profile ceshi_profile1;

SQL> alter user ceshi profile ceshi_profile1;

User altered.

(5)查看是否分配成功
SQL> SELECT username,PROFILE FROM dba_users where username=‘CESHI’;

SQL> SELECT username,PROFILE FROM dba_users where username='CESHI';

USERNAME		       PROFILE
------------------------------ ------------------------------
CESHI			       CESHI_PROFILE1

ps：
更改回默认参数文件：alter user ceshi profile default;