ORACLE数据库常用SQL

1. 新增一个表，通过另一个表的结构和数据 

CREATE TABLE PRODUCT_BAK AS SELECT * FROM PRODUCT 

2. 如果表存在：

INSERT INTO PRODUCT_BAK SELECT * FROM PRODUCT; 

3.同一个表中，将A字段的指赋给B字段：

UPDATE PRODUCT SET B = A; 

4. 将一个表的字段数据插入到另一个表的字段数据中 

INSERT INTO PRODUCT(ID,NAME) SELECT ID,NAME FROM PRODUCT_BAK; 

5. 第4点的延伸，多个表的多个字段，插入同一个表的多个字段。 

INSERT INTO T0 (id, name, code)
SELECT a.id, b.name,b.code FROM T1 b,T2 a 
WHERE a.id='100289756423' AND b.code='1234569870';

6、针对大数据表建立索引

CREATE INDEX 索引名称 ON 数据表名(表内字段) NOLOGGING ONLINE;

7、Oracle查看表空间当前用户

SELECT USERNAME,DEFAULT_TABLESPACE FROM USER_USERS;

8、Oracle查看表所属表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = '表名称'

9、Oracle查看表空间名称大小

SELECT 
	a.TABLESPACE_NAME   "表空间名", 
	TOTAL 				"表空间大小", 
	FREE 			    "表空间剩余大小", 
	(TOTAL - FREE) 		"表空间使用大小", 
	TOTAL / (1024*1024*1024) 				"表空间大小(G)", 
	FREE / (1024*1024*1024)   				"表空间剩余大小(G)",  
	(TOTAL - FREE) / (1024*1024*1024) 		"表空间使用大小(G)", 
	ROUND((TOTAL - FREE) / TOTAL, 4)*100 	"使用率 %" 
FROM 
  (SELECT TABLESPACE_NAME, SUM(bytes) FREE FROM dba_free_space GROUP BY TABLESPACE_NAME) a, 
  (SELECT TABLESPACE_NAME, SUM(bytes) TOTAL FROM dba_data_files GROUP BY TABLESPACE_NAME) b 
WHERE 
   a.TABLESPACE_NAME = b.TABLESPACE_NAME;

10、查看Oracle表空间物理文件名称和大小

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROUND(bytes / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME, FILE_ID;

11、查看Oracle日志文件

SELECT MEMBER FROM v$logfile;

12、查看Oracle控制文件

SELECT NAME FROM v$controlfile;

13、查看Oracle数据库库对象

SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS 
GROUP BY OWNER, OBJECT_TYPE, STATUS;

14、查看Oracle数据库版本

SELECT VERSION FROM PRODUCT_COMPONENT_VERSION WHERE SUBSTR(PRODUCT, 1, 6) = 'Oracle';

15、查看Oracle数据库创建日期和归档方式

SELECT CREATED, LOG_MODE FROM v$database;