oracle系列之常用操作命令

将a表的某些列插入b表的某些列

1	insert into a(ac1,ac2) select bc1,bc2 from b where ...;

重命名表

1	ALTER TABLE table_name RENAME TO new_table_name；

重命名字段

1	ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

修改字段类型

1	ALTER TABLE tableName modify(columnName 类型);

增加多个字段

1
2
3

alter table test1 add (name varchar2(30) default ‘无名氏’ not null,
age integer default 22 not null,
has_money number(9,2));

主键操作

1 2	--设置主键 alter table jack add constraint pk_id primary key(object_id);

删除表

1	drop table tn;

查询表所属表空间

1
2
3

select tablespace_name, table_name
  from user_tables
 where table_name = 'EOMS_OUTER_PROJECT';

查询索引所在表空间

1	select index_name,tablespace_name from dba_indexes where index_name='INDEX_PS';

查询表的建表DDL

1	select dbms_metadata.get_ddl('TABLE','TABLE_NAME','DB_USER') from dual;

查看所有表空间大小以及使用情况

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 "使用比" desc;

指定表空间查询所有表名

1
2
3

Select Table_Name, Tablespace_Name
From Dba_Tables
Where Tablespace_Name = 'USERS';

通过指定表空间查询所属表的占用空间

1
2
3

select segment_name, bytes/1024||'KB' "占用空间"
from user_segments 
where segment_type='TABLE' and tablespace_name='USERS' order by segment_name asc;

删除列

1	ALTER TABLE table_name DROP COLUMN column_name;

查询所有表及其所使用的表空间

1	select table_name 表名 ,tablespace_name 所使用表空间 from user_tables t where t.TABLE_NAME='T_PRE_AI_ACTIVITY';

查询所有表空间大小

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;

查询版本

1	select * from v$version;

查看长时间未commit会话，10秒未commit

SELECT A.SID,
       A.SERIAL#,
       A.USERNAME,
       A.EVENT,
       A.WAIT_CLASS,
       A.SECONDS_IN_WAIT,
       b.LOCKED_MODE,
       C.OWNER,
       C.OBJECT_NAME,
       C.OBJECT_TYPE
  FROM V$SESSION A
 INNER JOIN V$LOCKED_OBJECT B ON A.SID = b.SESSION_ID
 INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = c.OBJECT_ID
 WHERE A.WAIT_CLASS = 'Idle'
   AND A.SECONDS_IN_WAIT > 10

查看锁主的记录rowid

SELECT SID,
       SERIAL#,
       AUDSID,
       PADDR,
       USER#,
       USERNAME,
       EVENT,
       WAIT_CLASS,
       SECONDS_IN_WAIT,
       ROW_WAIT_OBJ#,
       ROW_WAIT_FILE#,
       ROW_WAIT_BLOCK#,
       ROW_WAIT_ROW#,
       BLOCKING_SESSION_STATUS,
       BLOCKING_INSTANCE,
       BLOCKING_SESSION,
       C.OWNER,
       C.OBJECT_NAME,
       C.OBJECT_TYPE,
       dbms_rowid.rowid_create(1,
                               ROW_WAIT_OBJ#,
                               ROW_WAIT_FILE#,
                               ROW_WAIT_BLOCK#,
                               ROW_WAIT_ROW#)
  FROM V$SESSION A
 INNER JOIN V$LOCKED_OBJECT B ON A.SID = b.SESSION_ID
 INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = c.OBJECT_ID
 WHERE BLOCKING_SESSION IS NOT NULL;

oracle命令行导入SQL脚本

使用@导入

1	SQL>@/home/oracle/a.sql;