SQL>createtablespace bwcx datafile '/data/oracle/oradata/orcl/bwcx1.dbf' size 1G autoextend on next 256M;
扩展表空间 后期使用
1
SQL>altertablespace bwcx add datafile '/data/oracle/oradata/orcl/bwcx2.dbf' size 1G autoextend on next 256M;
创建临时表空间
1
SQL>create temporary tablespace bwcx_t tempfile '/data/oracle/oradata/orcl/bwcx_t1.dbf' size 1G autoextend on next 256M;
删除表空间
1 2 3
SQL>drop tablespace bwcx_d including datafiles; SQL>drop tablespace bwcx_d including contents and datafiles; SQL>drop tablespace bwcx_d including contents and datafiles cascade constraint;
including contents #删除表空间及对象; including contents and datafiles #删除表空间、对象及数据文件; including contents cascade constraint #删除关联; including contents and datafiles cascade constraint #含前两项。
删除临时表空间
1
SQL>drop tablespace bwcx_t including contents and datafiles;
连接
1
SQL> conn bwcx_user/bwcx_user;
查询用户使用的表空间及临时表空间
1 2
SQL>select username,default_tablespace,temporary_tablespace from dba_users; SQL>select username,default_tablespace,temporary_tablespace from dba_users where username='TEST_USER';
查询临时表空间数据文件路径
1
SQL>select tablespace_name,file_name from dba_temp_files;
查询表空间数据文件路径
1 2
SQL>select tablespace_name,file_name,bytes from dba_data_files; SQL>select tablespace_name,file_name,bytes from dba_data_files where tablespace_name ='BWCX_D';
SQL>ALTERUSER TEST_USER IDENTIFIED BY "newpassword";
删除用户
1
SQL>dropuser 用户名 cascade;
修改用户表空间
修改用户默认表空间
1
SQL>alteruser test default tablespace test1;
修改用户临时表空间
1
SQL>alteruser test temporary tablespace test_t1;
授权用户
1 2 3 4 5 6 7 8 9 10 11 12
SQL>grantconnect,resource to 用户名; SQL>grantcreateviewto 用户名; SQL>grantcreate job to 用户名; SQL>grantcreate public database link to 用户名; SQL>grant unlimited tablespace to 用户名; SQL>grantcreate session to 用户名; SQL>grantselecton 表名 to 用户名; SQL>grantinserton 表名 to 用户名; SQL>grantupdateon 表名 to 用户名; SQL>grantselect,insert,update,delete,allon 表名 to 用户名; # 所有表查询(dba权限执行) SQL>grantselectanytableto reader;
撤销授权
1 2 3
SQL>revokeconnectfrom reader; SQL>revokeselecton wcpt_pt_pay_pre from reader; SQL>revokeselectanytablefrom reader;
查询用户权限
1 2 3 4
# 查看用户或角色系统权限(直接赋值给用户或角色的系统权限) SQL>select*from dba_sys_privs where grantee ='用户名'; # 查看用户或角色所拥有的角色 SQL>select*from dba_role_privs where grantee ='用户名';
query=TMS_ORDER_SHIP:'" where 1=2"',TMS_TRANS_SHIP_ROUTE:'" where 1=2"',TMS_ORDER_SHIP_FEE:'" where 1=2"',TMS_ASSET_DRIVER:'" where 1=2"',TMS_FULL_PROCESS_IMPORT2:'" where 1=2"'
select SID,SERIAL# from v$session where paddr in (select addr from v$process where spid in ('15552'));
查询锁死的session
1
select*from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
杀死会话进程
1
altersystem kill session '223,10057';
根据 PID 查询执行的 sql
1
SELECT sql_text FROM v$sqltext a WHERE (a.hash_value,a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address) FROM v$session b WHERE b.paddr=( SELECT addr FROM v$process c WHERE c.spid=15552)) ORDERBY piece ASC;
太多的时候可以获取 kill 语句
1
select'alter system kill session '''||sid||','||serial#||''';'from v$session where paddr in (select addr from v$process where spid in ('15552'));