SQL> create tablespace bwcx datafile '/data/oracle/oradata/orcl/bwcx1.dbf' size 1G autoextend on next 256M;
扩展表空间 后期使用
1
SQL> alter tablespace 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 * 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';
创建用户
1
SQL> create user bwcx_user identified by bwcx_user default tablespace bwcx temporary tablespace bwcx_t;
删除用户
1
SQL> drop user bwcx_user cascade;
授权用户
1 2 3
SQL> grant connect,resource to bwcx_user; SQL> grant create view to bwcx_user; SQL> grant create job to bwcx_user;
修改密码不过期
1
SQL> alter profile default limit password_life_time unlimited;
directory操作
查询directory
1
SQL> select * from dba_directories;
创建directory
1
SQL> create directory bwcxbak as '/databak/bwcxdata/dmp/';
删除directory
1
SQL> drop directory bwcxbak;
授权默认directory(使用Oracle默认的directory)
1
SQL> grant read, write on directory DATA_PUMP_DIR to bwcx_user;
授权directory给用户访问
1
SQL> grant read, write on directory bwcxbak to bwcx_user;
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"'
SQL> select * from abc as of timestamp to_timestamp('2020-01-14 19:00:00','yyyy-mm-dd hh24:mi:ss');
输出每页行数,缺省为24,为了避免分页,可设定为0。
1
set pagesize 0
输出一行字符个数,缺省为80
1
set linesize 500
查杀会话进程
通过top命令获取进程PID
通过sql查询SID及SERIAL#
1
selectSID,SERIAL# from v$session where paddr in (select addr from v$process where spid in ('15552','16309','18663'));
杀死会话进程
1
altersystemkillsession'223,10057';
根据PID查询执行的sql
1
SELECT sql_text FROM v$sqltext a WHERE (a.hash_value,a.address) IN (SELECTDECODE(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;