Oracle笔记

Oracle权限赋予和新建序列

赋予表和序列权限

grant select,insert,update on res.plan_task to ishare
grant select,insert,update on res.plan_task_instance to ishare
grant select,insert,update on res.plan_task_object to ishare
grant select,insert,update on res.plan_task_photo to ishare

grant select,alter on res.seq_plan_task to ishare
grant select,alter on res.seq_plan_task_instance to ishare
grant select,alter on res.seq_plan_task_object to ishare
grant select,alter on res.seq_plan_task_photo to ishare

新建序列

CREATE SEQUENCE RES.seq_plan_task_photo
INCREMENT BY 1   
START WITH 1      
MINVALUE 1
MAXVALUE 99999999999999
NOCYCLE  
CACHE 2;

序列说明

索引

--在数据库中查找表名
select * from user_tables where  table_name like 'tablename%';

--查看该表的所有索引
select * from all_indexes where table_name = 'MANAGEDELEMENT';

--查看该表的所有索引列
select* from all_ind_columns where table_name = 'MANAGEDELEMENT';
--创建索引
create index INDEX_MANAGEDELEMENT on MANAGEDELEMENT(EMS);

新建表空间/用户授权

• 查询数据表空间位置

  select name from v$datafile;

• 创建表空间

  CREATE tablespace etl_space datafile '/opt/oracle/oradata/orcl/etl01.dbf'
  size 500m autoextend on next 5M maxsize unlimited;

• 创建用户

  CREATE USER tomcat_etl IDENTIFIED BY tomcat_etl
  DEFAULT tablespace etl_space 
  temporary tablespace temp
  profile DEFAULT ;

• 用户授权

  grant connect,resource,dba to tomcat_etl;

修改字符集

进入sqlplus

sqlplus / as sysdba

修改字符集

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
//ZHS16GBK、AL32UTF8
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
//启动数据库
SQL>STARTUP;
//查看字符集
SQL>select userenv('language') from dual;

卸载oracle

进入sqlplus

sqlplus / as sysdba

停止Oracle数据库服务

shutdown immediate;

停止监听服务

lsnrctl stop

删除/usr/local/bin下的文件

coraenv  dbhome  oraenv

删除/etc下的文件

oraInst.loc oratab

删除/opt下ORCLfmap

rm –rf ORCLfmap

删除oracle安装目录ORACLE_HOME

删除用户和组

userdel –r oracle
groupdel oinstall
groupdel dba

密码过期

-- 查询用户
select username,profile from dba_users
--查看密码有效期
Select * FROM dba_profiles s Where s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'

-- 设置密码永久有效
 ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
--修改密码
alter user ishare identified by 111111

常见问题

ORA－00054：资源正忙，要求指定NOWAIT

select session_id from v$locked_object;
 SELECT sid, serial#, username, osuser FROM v$session where sid = 39;
 
 ALTER SYSTEM KILL SESSION '39,1290';