oracle数据库相关操作记录

一、相关指令

1. 根据表空间查看所有表信息

   select * from all_tables where owner=‘HYDROPOWER_JIANGXI’

2. 查询当前用户下的所有表

   select table_name from user_tables

3. 增加主键

   alter table S_USER add constraints s_key primary key (name)

4. 删除主键

   alter table S_USER drop constraint s_key 删除主键

5. 设置外键

   ALTER TABLE 主表 ADD CONSTRAINT 外键名 FOREIGN KEY(主表外键字段 ) REFERENCES 外键表(外键字段);

6. 添加随机数和时间sql

   insert into R_SAMPLE_VALUE values(trunc(DBMS_RANDOM.value(100,1000)),sysdate);

7. 找某个表的某个时间的数据（误删）

   select * from E_MESSAGE as of timestamp to_timestamp(‘2018-04-09 3:33:53’, ‘yyyy-mm-dd hh24:mi:ss’) where 条件

8. 查看当前的数据库连接数

   select count(*) from v$process ;

9. 数据库允许的最大连接数

   select value from v$parameter where name =‘processes’;

10. 修改数据库最大连接数

    alter system set processes = 300 scope = spfile;

11. select count(*) from v$session

    select count(*) from v$session where status=‘ACTIVE’;

12. 当前的session连接数

    select count(*) from v$session;

13. 关闭/重启数据库

    shutdown immediate; startup;

14. 查看当前有哪些用户正在使用数据

    select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
    from v$session a, v$sqlarea b
    where a.sql_address =b.address
    order by cpu_time/executions desc;
    

15. 相关视图介绍

    v$process：
    这个视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息
    服务进程分两类，一是后台的，一是dedicate/shared server
    pid, serial#     这是oracle分配的PID
    spid             这才是操作系统的pid
    program          这是服务进程对应的操作系统进程名
    
    v$session：
    这个视图主要提供的是一个数据库connect的信息，主要是client端的信息，比如以下字段：
    machine   在哪台机器上
    terminal  使用什么终端
    osuser    操作系统用户是谁
    program   通过什么客户端程序，比如TOAD
    process   操作系统分配给TOAD的进程号
    logon_time  在什么时间
    username    以什么oracle的帐号登录
    command     执行了什么类型的SQL命令
    sql_hash_value  SQL语句信息
    有一些是server端的信息：
    paddr   即v$process中的server进程的addr
    server  服务器是dedicate/shared
    

16. 联表更新

    update R_HYDROPOWER_OUTPUT o set o.delete_status='0'
    where exists(select 1 from r_hydropower rh where rh.hydropower_id=o.hydropower_id and rh.if_important='0'    )
    

二、跨库迁移数据

----------------------------------------------------创建跨库连接------------------------------------------------
create public database link dblink
connect to HYDROPOWER_JIANGXI identified by ffcsict123
using '(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.35.9)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = ORCL))
 )';    
------------------------------------------------------插入数据------------------------------------------------ 
insert into r_hydropower@dblink select * from r_hydropower
-----------------------------------------------------删除跨库连接------------------------------------------------ 
drop public database link dblink;

三、迁移oracle数据库

------------------------------------------------------先清除原来空间的数据------------------------------------------------
--搜索表空间
select * from dba_data_files
--删除表空间
drop tablespace HYDROPOWER_JIANGXI 
--删除表空间文件
C:\APP\86188\ORADATA\ORCL\HYDROPOWER_JIANGXI.dbf
--查询用户session
select username,sid,serial#,status from v$session where username = 'HYDROPOWER_JIANGXI';
--杀死用户session
alter system kill session '5,966' immediate;
--查询表用户
select * from dba_users;
--删除用户
drop  user HYDROPOWER_JIANGXI cascade;

------------------------------------------------------创建新空间的数据------------------------------------------------
--创建表空间
create tablespace HYDROPOWER_JIANGXI
logging  
datafile '/data1/oracle/oradata/orcl/HYDROPOWER_JIANGXI.dbf'
size 100m  
autoextend on  
next 50m 
extent management local; 
--创建用户
create user HYDROPOWER_JIANGXI identified by ffcsict123   default tablespace HYDROPOWER_JIANGXI 
--给用户功能
grant connect,resource,dba to HYDROPOWER_JIANGXI
    
------------------------------------------------------导出数据库前对空表进行处理------------------------------------------------   
--查询当前用户下的所有空表,如果存在，需要执行下一步：
select table_name from user_tables where num_rows='0'
--查看空表，并且执行
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null 
    
------------------------------------------------------导出、导入数据库------------------------------------------------  
--导出
imp HYDROPOWER_JIANGXI/xxxx@192.168.35.9/orcl    file=D:/sjk/myOracle.dmp full=y 
--导入
exp HYDROPOWER_JIANGXI/xxxx@192.168.35.9/orcl    file=D:/sjk/myOracle.dmp owner=(HYDROPOWER_JIANGXI)

四、扩展表空间

1. 查看表空间

   SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
   D.TOT_GROOTTE_MB "表空间大小(M)",
   D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
   TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
   F.TOTAL_BYTES "空闲空间(M)",
   F.MAX_BYTES "最大块(M)"
   FROM (SELECT TABLESPACE_NAME,
   ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
   ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
   FROM SYS.DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME) F,
   (SELECT DD.TABLESPACE_NAME,
   ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
   FROM SYS.DBA_DATA_FILES DD
   GROUP BY DD.TABLESPACE_NAME) D
   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
   ORDER BY 4 DESC;
   

2. 查看是否自动扩展

   SELECT T.TABLESPACE_NAME,D.FILE_NAME,
   D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
   FROM DBA_TABLESPACES T,DBA_DATA_FILES D
   WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
   ORDER BY TABLESPACE_NAME,FILE_NAME;
   

3. 扩展表空间

   alter tablespace HYDROPOWER_JIANGXI add datafile 'D:\FFCS\ORACLE\APP\ORADATA\ORCL\mainPP202273.DBF' size 500M;
   ALTER DATABASE DATAFILE 'D:\FFCS\ORACLE\APP\ORADATA\ORCL\mainPP202273.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;