ORACLE基本语法

    oracle查看用户状态

    select username,account_status from dba_users;

    查看所有角色

    select * from dba_roles;

    锁定用户

    alter user XXX account lock;

    useradd -d /home/XXX -s /usr/bin/ksh -m XXX
    passwd XXX

    用户创建

    useradd -d /home/XXX -g users -s /bin/bash -m XXX -G users
    passwd XXX

    删除用户

    userdel -r XXX

    Linux查看进程

    ps -e|grep
    kill -9     (PID)

    linux查看版本信息

    lsb_release -a

    赋予debug权限给用户

    grant debug any procedure, debug connect session to prodb;

    oracle查询用户

    select username,password from dba_users;

    oracle删除用户

    drop user XXX cascade;

    如果遇到报错
    oracle cannot drop a user that is currently connected
    则执行以下命令

    以sysdba登陆

    shutdown immediate;
    startup restrict;
    drop user * cascade;
    alter system disable restricted session;

    Oracle监听

    lsnrctl start

    即可启动

    如果出现以sysdba用户连接无法连接到数据库的现象
    检查环境变量ORACLE_HOME,ORACLE_SID

    sqlplus下的删除符号

    ctrl+backspace

    sqlplus远程连接

    sqlplus 用户名/密码@10.10.10.10：1521/orcl

    查询表空间和对应数据文件的关系

    select t1.name, t2.name from voracle数据库常用命令整理 - 图1datafile t2 where t1.ts# = t2.ts#;

    删除表空间

    drop tablespace ** including contents and datafiles;

    取数据重复插入表

    insert into table_A(X,X,X,X) select X,X,X,X from table_B where XXX

    table_A和table_B可以是一张表，数据当然要经过更新之后再插入

    为用户赋debug权限

    GRANT debug any procedure, debug connect session TO USER XXX;

    查看不同用户的连接数

    select username,count(username) from v$session where username is not null group by username;

    查看执行日志

    select t.”SQL_TEXT”,t.”SQL_FULLTEXT”,t.”LAST_ACTIVE_TIME” from v$sqlarea t where t.PARSING_SCHEMA_NAME in (‘XXX’) and t.”SQL_TEXT” like ‘%XXX%’ and t.LAST_ACTIVE_TIME > to_date(‘201707130623’, ‘yyyymmddhhmi’) order by t.LAST_ACTIVE_TIME;