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';

文章作者: 苏叶新城
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 苏叶新城 !
  目录