数据库

查看当前打开的游标数

select count(*) from v$open_cursor;

显示游标配置
show parameter open_cursors;
更改游标配置
alter system set open_cursors=3000;
提交
commit;
查看当前打开游标的详细信息

 select o.sid, s.osuser, s.machine, count(*) num_curs from v$open_cursor o, v$session s where o.user_name = 'EAZYBIJIRA' and o.sid=s.sid group by o.sid, osuser, machine order by   num_curs desc;

创建表空间

CREATE TABLESPACE BITBUCKET
LOGGING
DATAFILE 'D:\app\oracledb\oradata\bitbucket\BITBUCKET01.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

创建用户,授权并和表空间关联

-- USER SQL
CREATE USER bitbucket IDENTIFIED BY bitbucket
DEFAULT TABLESPACE "BITBUCKET"
TEMPORARY TABLESPACE "TEMP";

-- QUOTAS,用户可以操作表空间的大小
ALTER USER bitbucket QUOTA UNLIMITED ON BITBUCKET;

-- ROLES
GRANT "DBA" TO bitbucket ;

-- SYSTEM PRIVILEGES

修改用户,授权并与表空间关联

CREATE TABLESPACE CROWD
LOGGING
DATAFILE 'D:\app\oracle\oradata\depmng\crowd\CROWD01.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 50M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

-- USER SQL
alter USER crowd 
DEFAULT TABLESPACE "CROWD"
TEMPORARY TABLESPACE "TEMP";

-- QUOTAS
ALTER USER crowd QUOTA UNLIMITED ON CROWD;

-- ROLES
GRANT "DBA" TO crowd ;

-- SYSTEM PRIVILEGES

删除用户

SQL〉DROP USER USER1 CASCADE
ERROR：ORA-01940: cannot drop a user that is currently connected
由于当前用户正连接到数据库，所以无法删除
解决办法：
1、查询此用户的会话进程，
SQL〉SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USER1';
SID SERIAL#
---------- ----------
24 25341
86 18117
2、结束此用户的所有会话
SQL>ALTER SYSTEM KILL SESSION '24,25341';
System altered.
SQL>ALTER SYSTEM KILL SESSION '86,18117';
System altered.
如果没有发现会话.要就要看是不是自己当前有没有使用这个用户.
3、删除用户
SQL〉DROP USER USER1 CASCADE；

添加用户

CREATE USER bitbucket IDENTIFIED BY bitbucket
DEFAULT TABLESPACE BITBUCKET
TEMPORARY TABLESPACE TEMP profile DEFAULT;

grant connect to bitbucket;
grant resource to bitbucket;
grant dba to bitbucket;

grant create session to bitbucket ;
grant create table to bitbucket ;

12c中CDB启动时不会将PDB同时启动,创建一个触发器,在启动时将所有PDB启动即可

conn / as sysdba
CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;

–将密码有效期由默认的180天修改为“无限制”
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;