下载地址：Database Software Downloads ~| Oracle
常用命令
1、使用SQLPlus连接数据库（以SYSDBA身份）。
sqlplus 用户名/密码@IP地址:端口/服务名 AS SYSDBA
2、查询数据库版本。
select * from v$version;
3、查询语言。
select userenv('language') from dual;
4、SQLPlus断开与数据库的连接。
exit;
5、导出数据。
exp "用户名/密码@IP地址:端口/服务名" file="导出文件名.dmp" log="日志文件名.log" owner=用户名
注意：需要在服务器上执行，否则导出第一个表后就不执行了；命令后不能加分号，否则会报错：EXP-00010: 用户名; 是无效的用户名。
6、导入数据。
imp "用户名/密码@IP地址:端口/服务名" file="导入文件名.dmp" log="导入日志名.log" fromuser=来源用户 touser=要导入的用户 ignore=y
7、查询需要重建的索引。
SELECT 'alter index ' || INDEX_NAME || ' rebuild online nologging;' FROM USER_INDEXES
WHERE TABLESPACE_NAME = '表空间名称' AND STATUS = 'VALID' AND TEMPORARY = 'N' AND INDEX_TYPE = 'NORMAL' AND PARTITIONED = 'NO';
8、慢sql查询。
--select distinct a.sid,b.SERIAL#,b.PROCESS,b.STATUS from v$session_wait a,v$session b where a.SID=b.SID
select * from (
select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE, sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
9、查询阻塞时间段。
select MAX(SAMPLE_TIME), MIN(SAMPLE_TIME), EVENT,BLOCKING_SESSION,INSTANCE_NUMBER
from dba_hist_active_sess_history where sql_id='c607yg8ym0zb2' and BLOCKING_SESSION is not null
GROUP BY EVENT, BLOCKING_SESSION,INSTANCE_NUMBER;
10、创建表。（可以重复执行）
DECLARE
num NUMBER;
BEGIN
SELECT COUNT(1) INTO num from cols where table_name = upper('GIS_ASSETS_CONFIG');
IF num = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE GIS_ASSETS_CONFIG
(
ID NUMBER(20,0) NOT NULL PRIMARY KEY,
TENANT_ID VARCHAR2(12),
DEPT_ID NUMBER(20,0),
DEPT_CODE VARCHAR2(100),
DEPT_NAME VARCHAR2(45),
MATERIAL VARCHAR2(100),
DIAMETER VARCHAR2(100),
PRICE NUMBER(12,7),
CREATE_USER NUMBER(20,0),
UPDATE_USER NUMBER(20,0),
CREATE_TIME DATE,
UPDATE_TIME DATE,
IS_DELETED NUMBER DEFAULT 0
)';
EXECUTE IMMEDIATE 'COMMENT ON TABLE GIS_ASSETS_CONFIG IS ''资产报表配置''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.ID IS ''主键''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.TENANT_ID IS ''租户ID''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.DEPT_ID IS ''机构ID''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.DEPT_CODE IS ''机构编码''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.DEPT_NAME IS ''机构名称''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.MATERIAL IS ''材质''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.DIAMETER IS ''管径''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.PRICE IS ''价格''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.CREATE_USER IS ''创建用户''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.UPDATE_USER IS ''更新用户''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.CREATE_TIME IS ''创建时间''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.UPDATE_TIME IS ''更新时间''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN GIS_ASSETS_CONFIG.IS_DELETED IS ''是否删除''';
END IF;
END;
11、给数据表添加字段。（可以重复执行）
DECLARE
num NUMBER;
BEGIN
SELECT COUNT(1) INTO num from cols where table_name = upper('GIS_LAYER_DATA') and column_name = upper('DISPLAY_FIELD');
IF num = 0 THEN
execute immediate 'ALTER TABLE GIS_LAYER_DATA ADD (DISPLAY_FIELD VARCHAR2(255))';
execute immediate 'COMMENT ON COLUMN GIS_LAYER_DATA.DISPLAY_FIELD IS ''展示字段''';
execute immediate 'UPDATE GIS_LAYER_DATA SET DISPLAY_FIELD = ''''';
END IF;
END;