oracle常用语法-2

--创建表空间

1.查询表空间的文件路径
select * from dba_data_files 
2.创建表空间
替换表空间路径文字为实际表空间路径
create tablespace  bidata datafile  '表空间路径\表空间文件名.dbf'  size 30G autoextend on  next  30M;

--创建用户并授权

-- Create the user 
create user ptjob
  identified by ptjob
  default tablespace ptjob
  temporary tablespace temp;
-- Grant/Revoke role privileges 
grant dba to ptjob;
grant resource to ptjob;
-- Grant/Revoke system privileges 
grant unlimited tablespace to ptjob;

--字符集问题

1	`select` `userenv('language')` `from` `dual;<br><br>在系统环境变量中加入 NLS_LANG 及上述sql查询出的值`

--批量修改字段长度

select 'alter table '||a.TABLE_NAME||' modify '||a.COLUMN_NAME||' varchar2(10);' from all_tab_columns a where (a.COLUMN_NAME like '%DEPT%' or a.COLUMN_NAME like '%DPCD%') AND a.OWNER ='NEWODS'  and a.DATA_LENGTH<5

--增加分区

 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202201'||' VALUES LESS THAN (TO_DATE(''2022-02-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202202'||' VALUES LESS THAN (TO_DATE(''2022-03-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202203'||' VALUES LESS THAN (TO_DATE(''2022-04-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202204'||' VALUES LESS THAN (TO_DATE(''2022-05-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202205'||' VALUES LESS THAN (TO_DATE(''2022-06-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202206'||' VALUES LESS THAN (TO_DATE(''2022-07-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202207'||' VALUES LESS THAN (TO_DATE(''2022-08-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202208'||' VALUES LESS THAN (TO_DATE(''2022-09-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202209'||' VALUES LESS THAN (TO_DATE(''2022-10-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202210'||' VALUES LESS THAN (TO_DATE(''2022-11-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202211'||' VALUES LESS THAN (TO_DATE(''2022-12-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'
 UNION ALL
 SELECT   'ALTER TABLE '||a.table_name||' ADD PARTITION PART_'||'202212'||' VALUES LESS THAN (TO_DATE(''2023-01-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss''));'
 FROM DBA_PART_TABLES A where owner IN ('NEWODS') and table_name not like '%$%'

ORACLE设置密码无限期

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

删掉有外键关联的表里数据

alter table test2 disable constraint F_TEST2;
truncate table test1;
alter table test2 enable constraint F_TEST2;



select 'alter table '|| a.Table_Name ||' disable constraint '|| c.Constraint_Name||';'
From User_Constraints a,
　　　user_Constraints b,
　　   user_Cons_Columns c, --外键表
　　   user_Cons_Columns d --主键表　　
Where a.r_Constraint_Name = b.Constraint_Name 　　
　　And a.Constraint_Type = 'R' 　　
　　And b.Constraint_Type = 'P' 　　
　　And a.r_Owner = b.Owner 　　
　　And a.Constraint_Name = c.Constraint_Name 　　
　　And b.Constraint_Name = d.Constraint_Name 　　
　　And a.Owner = c.Owner 　　
　　And a.Table_Name = c.Table_Name 　　
　　And b.Owner = d.Owner 　　
　　And b.Table_Name = d.Table_Name;

select 'alter table '|| a.Table_Name ||' enable constraint '|| c.Constraint_Name||';'
From User_Constraints a,
　　　user_Constraints b,
　　   user_Cons_Columns c, --外键表
　　   user_Cons_Columns d --主键表　　
Where a.r_Constraint_Name = b.Constraint_Name 　　
　　And a.Constraint_Type = 'R' 　　
　　And b.Constraint_Type = 'P' 　　
　　And a.r_Owner = b.Owner 　　
　　And a.Constraint_Name = c.Constraint_Name 　　
　　And b.Constraint_Name = d.Constraint_Name 　　
　　And a.Owner = c.Owner 　　
　　And a.Table_Name = c.Table_Name 　　
　　And b.Owner = d.Owner 　　
　　And b.Table_Name = d.Table_Name;

查询oracle外键

Select   a.Owner 外键拥有者,
　　　　a.Table_Name 外键表,
　　　　c.Column_Name 外键列,
　　　　b.Owner 主键拥有者,
　　　　b.Table_Name 主键表,
　　　　d.Column_Name 主键列,
　　　　c.Constraint_Name 外键名,
　　　　d.Constraint_Name 主键名
From User_Constraints a,
　　　user_Constraints b,
　　   user_Cons_Columns c, --外键表
　　   user_Cons_Columns d --主键表　　
Where a.r_Constraint_Name = b.Constraint_Name 　　
　　And a.Constraint_Type = 'R' 　　
　　And b.Constraint_Type = 'P' 　　
　　And a.r_Owner = b.Owner 　　
　　And a.Constraint_Name = c.Constraint_Name 　　
　　And b.Constraint_Name = d.Constraint_Name 　　
　　And a.Owner = c.Owner 　　
　　And a.Table_Name = c.Table_Name 　　
　　And b.Owner = d.Owner 　　
　　And b.Table_Name = d.Table_Name;

--to_char转换数字

select rtrim(to_char(1000.00,'fm99999990.999'),'.') from dual;   --1000
select rtrim(to_char(0.01,'fm99999990.999'),'.') from dual;    --0.01
select rtrim(to_char(0.001,'fm99999990.999'),'.') from dual;   --0.001
select rtrim(to_char(1000.01,'fm99999990.999'),'.') from dual;   --1000.01

--误删数据可恢复

select * from com_fun_controls as of timestamp to_timestamp('2021-1-25 16:40:12', 'YYYY-MM-DD HH24:MI:SS')

--com_fun_controls：表名

--to_timestamp('2021-1-25 16:40:12', 'YYYY-MM-DD HH24:MI:SS')：删除数据的某个时间

--跟踪SQL语句

SELECT sql_text, last_active_time,PARSING_SCHEMA_NAME FROM v$sql WHERE last_active_time > to_date('2021-04-14 15:37:00','yyyy-mm-dd hh24:mi:ss')
and PARSING_SCHEMA_NAME <> 'SYS'
ORDER BY last_active_time DESC;

根据起始日期和结束日期实现日期自增长的SQL语句

SELECT TO_DATE('2010-01-01','YYYY-MM-DD') + LEVEL - 1 CUR_DATE
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('2010-02-10','YYYY-MM-DD') - TO_DATE('2010-01-01','YYYY-MM-DD') + 1

怎样查看Oracle的数据库名称sid

select * from  V$database;

select instance_name from  V$instance;

---更改表所属表空间

SELECT 'alter table '||TABLE_NAME||' move tablespace PTJOB;' FROM USER_TABLES WHERE TABLESPACE_NAME != 'PTJOB'

修改表索引：SELECT 'alter index '|| INDEX_NAME ||' rebuild tablespace FCCMS;' FROM user_indexes;

表里包含的CLOB字段，针对Clob、Blob字段需单独做修改处理

ALTER TABLE TEST2 MOVE TABLESPACE USERS LOB(col_lob1,col_lob2) STORE AS(TABLESPACE FCCMS);

导出报错EXP-00003: 未找到段 (0,0) 的存储定义，需要在有clob、Blob字段里加一行。

--oracle查询表空间的位置

SELECT * FROM Dba_Data_Files ddf WHERE ddf.tablespace_name = 'TablespaceName';

--查询表占用的空间

SELECT segment_name "表名",
segment_type "对象类型",
sum(bytes) / 1024 / 1024 "占用空间（MB）"
FROM dba_extents
WHERE 1=1
GROUP BY segment_name, segment_type
ORDER BY "占用空间（MB）" DESC;