oracle 常用语句

[TOC]

1. 修改用户密码

alter user 用户名 identified by 新密码;

2. 表空间扩容

1.增加数据文件
ALTER TABLESPACE TVVASDB ADD DATAFILE '/home/oracle/datefile/sdpdb/TVVASDB02.dbf' SIZE 30G;
2.修改数据文件大小
ALTER DATABASE DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFIRST\DITS.DBF' RESIZE 400M;
复制

3. 临时表空间扩容

alter database tempfile ‘+DG_ORA/sdpdb/ora_temp03’ resize 10G;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DG_ORA/sdpdb/ora_temp03’ SIZE 10G;

4. 删除用户

drop user 用户名 cascade;—cascade 级联

5. 创建dblink

– Drop existing database link

drop public database link PRODB_LINK_20170606;

– Create database link

create public database link chg2sub_LINK
connect to subdb identified by subdb_Db123456789
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.10.211 )(PORT = 1526)))(CONNECT_DATA =(SERVICE_NAME = sdpdb))
)’;

6. 用sqlplus远程连接oracle命令 语法：

sqlplus usr/pwd@host:port/sid

7. 创建表空间

create tablespace 表间名 datafile ‘数据文件名’ size 表空间大小 create tablespace data_test datafile ‘e:\oracle\oradata\test\data_1.dbf’ size 2000M;
create tablespace idx_test datafile ‘e:\oracle\oradata\test\idx_1.dbf’ size 2000M;

8. job创建

begin

  sys.dbms_job.submit(job => :job,

                      what => 'del_access_ext_210420',

                      next_date => to_date('21-04-2021 00:00:00',

                                           'dd-mm-yyyy hh24:mi:ss'),

                      interval => 'TRUNC(sysdate+1)');

 

  commit;

end;

/


复制

9. 修改时间显示格式

alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;

alter session set nls_timestamp_format=‘yyyy-mm-dd hh24:mi:ss’;

10. truncate表时删除

TRUNCATE TABLE name [DROP/REUSE STORAGE]

DROP STORAGE：显式指明释放数据表和索引的空间
REUSE STORAGE：显式指明不释放数据表和索引的空间
复制