Oracle的基本操作

写在前面

一、登录

1.1、Linux命令行

跟安装方式有关，有时会比较麻烦
我这里有个,可供参考

先切换到oracle用户

su - oracle

sqlplus

这是会提示输入用户和密码

用户：sys
密码：as sysdba

1.2、Navicate工具

图一是Basic连接,根据

Host:
port:
Service Name:
User name:
pass:

图二是根据 SSH server,先ssh 连接装有Oracle的服务器，在尝试这种连接方式

1.3、SQLPLUS工具

1.4、PL/SQL

二、基本语句

2.1、查询表空间

---- 查询所有表空间 
select * from dba_tablespaces;
select * from user_tablespaces;

---- 查询使用过的表空间 
select distinct tablespace_name from dba_all_tables;
select distinct tablespace_name from user_all_tables;

---- 查询表空间中所有表的名称
select *  from user_all_tables where tablespace_name = 'USER';
select *  from dba_all_tables where tablespace_name = 'USER';

表空间统计

select a.file_id "FileNo",
       a.tablespace_name "表空间",
       a.bytes/1024/1021/1024 Bytes_size,
       a.bytes - sum(nvl(b.bytes, 0)) "已用",
       sum(nvl(b.bytes, 0)) "空闲",
       sum(nvl(b.bytes, 0)) / a.bytes * 100 "空闲百分率"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name;

查看表空间,文件路径

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name

2.2、查询用户

---- 查询系统用户

select * from all_users
select * from dba_users

----查看当前连接操作系统信息

select * from v$session

查看当前用户的身份

 select * from V$PWFILE_USERS

2.3、权限和角色查询

----查看当前用户权限
select * from session_privs;

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM DBA_SYS_PRIVS;

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

select * from dba_roles;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

2.4、查看函数，存储过程和视图

select * from user_source;

2.5、查询表信息

 select * from user_tables;
 
 select * from dba_tables;

2.6、常用脚本

禁用外键脚本

----ORACLE数据库中的外键约束名都在表user_constraints中可以查到。其中constraint_type='R'表示是外键约束。
----启用外键约束的命令为：
alter table table_name enable constraint constraint_name
----禁用外键约束的命令为：
alter table table_name disable constraint constraint_name
----然后再用SQL查出数据库中所以外键的约束名：
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

ORACLE禁用/启用外键和触发器

--启用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;
exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
end;
/
commit;

--禁用脚本
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
 EXECUTE IMMEDIATE c.v_sql;
 exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
 dbms_output.put_line(c.v_sql);
 begin
 execute immediate c.v_sql;
exception when others then
 dbms_output.put_line(sqlerrm);
 end;
end loop;
end;
/
commit;

2.7 get DDL

获取用户 DDL

select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users ;

select 'alter user ' || username ||' identified by values ''' || password || ''';'from dba_users;