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;
--字符集问题
select userenv('language') from dual;
在系统环境变量中加入 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;
