Linux 操作 oracle 数据库
1、Oracle监听启动命令：lsnrctl start
2. sqlplus 登录数据库
3.OS版本及Oracle版本
select banner from v$version;
4.查询该数据库的实例名
select name from v$database;
5.查询该用户下所有的表
select * from tab;
6.查询当前用户的缺省表空间
select username,default_tablespace from user_users;
7.查询当前用户的角色
select * from user_role_privs;
8.可以看到表内容
SQL> select * from dba_directories;
//用户权限不够，需要给enfo_ods 导出目录的读写权限
SQL> conn sys/Oracle001@enfodw as sysdba;
Connected.
//由于目录权限不清楚，下面就用dba的身份新建一个目录
SQL> create directory wensm_dir as '/home/oracle/wensm';
Directory created.
SQL> grant write,read on directory wensm_dir to sjcj;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
9.查询所有表空间
select file_name,tablespace_name,bytes from dba_data_files;
查看表空间的名称及大小:
SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
查看表空间物理文件的名称及大小:
SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
查看所有表空间对应的数据文件名：
SQL>SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
查看表空间的使用情况:
SELECT SUM(BYTES)/(1024*1024) AS FREE_SPACE,TABLESPACE_NAME
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
COLUMN TABLESPACE_NAME FORMAT A18;
COLUMN SUM_M FORMAT A12;
COLUMN USED_M FORMAT A12;
COLUMN FREE_M FORMAT A12;
COLUMN PTO_M FORMAT 9.99;
SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSED FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE, SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) S GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) DESC;
查看数据文件的hwm（可以resize的最小空间）和文件头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,
NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+) AND V1.FILE_ID=V3.FILE_ID(+);
数据文件大小及头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,
NUM1 TOTLE_SPACE,
NUM3 FREE_SPACE,
NUM1-NUM3 USED_SPACE,
NVL(NUM2,0) DATA_SPACE,
NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+)
AND V1.FILE_ID=V3.FILE_ID(+);
(运行以上查询，我们可以如下信息：
Totle_pace:该数据文件的总大小，字节为单位
Free_space:该数据文件的剩于大小，字节为单位
Used_space:该数据文件的已用空间，字节为单位
Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位
File_Head:该数据文件头部占用空间，字节为单位)
10. 查看日志文件:
SQL> COL MEMBER FORMAT A50
SQL>SELECT * FROM V$LOGFILE;
11.如何查看当前数据库的字符集:
SQL>SELECT USERENV('LANGUAGE') FROM DUAL;
SQL>SELECT USERENV('LANG') FROM DUAL;
12.如何查看客户端登陆的IP地址:
SQL>SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;
如何在生产数据库中创建一个追踪客户端IP地址的触发器：
CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
END;
13.查询当前日期:
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;
14.修改用户密码
查看所有用户名、密码
select username,password from dba_users;
修改用户密码
//修改用户口令 格式 alter user 用户名 identified by 新密码;
SQL> alter user system identified by manager;
若切换另外一个用户就要断掉当前的登录
discon 回车键
若再连接其他用户
conn 回车键再录入其他登录用户信息即可
select * from dba_users; 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users; 查看你能管理的所有用户！
select * from user_users; 查看当前用户信息 ！
-- 查询你 当前用户下,有哪些表
SELECT * FROM user_tables
-- 查询你 当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]
SELECT * FROM all_tables
-- 查询当前数据库所有的表， 需要你有 DBA 的权限
SELECT * FROM dba_tables
使用例子：
1、创建新用户：jkw
--查询数据库表空间
select distinct TABLESPACE_NAME from tabs;
create tablespace SJCJ_DAT DATAFILE 'D:\app\oukf\virtual\oradata\orcl\SJCJ_DAT.DBF' SIZE 2048M AUTOEXTEND ON NEXT 5M MAXSIZE 3072M;
--修改表空间大小
ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/app/oracle/oradata/test/system02.dbf' SIZE 20480M AUTOEXTEND OFF;
--查看你能管理的所有用户
select * from all_users;
--删除用户
--drop user JKW cascade;
/*第3步：创建用户并指定表空间 */
create user jkw identified by jkw123
default tablespace RMDB
--temporary tablespace rmdb_temp;
CREATE USER SJCJ IDENTIFIED BY sjcj DEFAULT TABLESPACE SJCJ_DAT;
create user hc_notify identified by hc_password default tablespace NOTIFYDB temporary tablespace NOTIFYDB_TEMP;
/*第4步：给用户授予权限 */
grant connect,resource,dba to jkw;
grant dba to hc_notify;
grant connect,resource to hc_notify;
grant select any table to hc_notify;
grant delete any table to hc_notify;
grant update any table to hc_notify;
grant insert any table to hc_notify;
2、导入数据：
imp jkw/jkw123@112.230.253.54/rmdb file=C:\Users\Administrator\Desktop\mhc.dmp full=y
--查看临时表空间文件
select name from v$tempfile;
--查看用户和表空间的关系
select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS;
--如果有用户的默认临时表空间是NOTIFYDB_TEMP的话，建议进行更改
alter user xxx temporary tablespace tempdefault;
---设置tempdefault为默认临时表空间
alter database default temporary tablespace tempdefault;
--删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件
drop tablespace NOTIFYDB_TEMP including contents and datafiles;
--查看表空间文件
select name from v$datafile;
--停止表空间的在线使用
alter tablespace 表空间名称 offline;
--删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件
drop tablespace NOTIFYDB_TEMP including contents and datafiles;
--查看所有的用户
select * from all_users;
--查看当前用户信息
select * from user_users;
--查看当前用户的角色
select * from user_role_privs;
--查看当前用户的权限
select * from user_sys_privs;
--查看当前用户的表可操作权限
select * from user_tab_privs;
--查看某一个表的约束,注意表名要 大写
select * from user_constraints where table_name='TBL_XXX';
--查看某一个表的所有索引,注意表名要 大写
select index_name,index_type,status,blevel from user_indexes where table_name = 'TBL_XXX';
--查看索引的构成,注意表名要 大写
select table_name,index_name,column_name, column_position FROM user_ind_columns WHERE table_name='TBL_XXX';
--系统数据字典 DBA_TABLESPACES 中记录了关于表空间的详细信息
select * from sys.dba_tablespaces;
--查看用户序列
select * from user_sequences;
--查看数据库序列
select * from dba_sequences;
