SQL语句

1.PostgreSQL查询主键

select
	tc.constraint_name,
	tc.table_name,
	kcu.column_name,
	constraint_type,
	ccu.table_name as foreign_table_name,
	ccu.column_name as foreign_column_name,
	tc.is_deferrable,
	tc.initially_deferred
from
	information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
where
	kcu.table_schema = ‘userName’ and constraint_type = 'PRIMARY KEY'
order by
	table_name;

select
	kcu.table_schema,
	kcu.table_name,
	tc.constraint_name,
	kcu.ordinal_position as position,
	kcu.column_name as key_column
from
	information_schema.table_constraints tc
join information_schema.key_column_usage kcu on kcu.constraint_name = tc.constraint_name
	and kcu.constraint_schema = tc.constraint_schema
	and kcu.constraint_name = tc.constraint_name
where
	tc.constraint_type = 'PRIMARY KEY' and kcu.table_schema = 'userName'
order by
	kcu.table_schema,
	kcu.table_name,
	position;

如果只需要查询主键的名称和关联的表名与字段名的话，用第二个SQL语句即可。

2.PostgreSQL查询表的relreplident值

select
	pcs.oid,
	pcs.relname,
	pns.nspname,
	(pcs.relreplident = 'f' or (pcs.relreplident = 'd' and ('p' in (select contype from pg_constraint where pcs.oid = conrelid)))) as canmigrate
from
	pg_class pcs,
	pg_namespace pns
where
	pcs.oid > 16384 and pcs.relkind = 'r' and pns.oid = pcs.relnamespace and nspname = 'userName'
order by
	relname;

3.PostgreSQL修改表的relreplident值

update pg_class set relreplident = 'f' where relname in ('tableName');

4.ORACLE查询表空间是否开启自动增长

SELECT
	DT.TABLESPACE_NAME,
	DDF.FILE_NAME,
	DDF.AUTOEXTENSIBLE,
	DDF.BYTES,
	DDF.MAXBYTES,
	DDF.STATUS
FROM
	DBA_TABLESPACES DT,
	DBA_DATA_FILES DDF
WHERE
	DT.TABLESPACE_NAME = DDF.TABLESPACE_NAME AND T.TABLESPACE_NAME = '表空间名'
ORDER BY
	TABLESPACE_NAME,
	FILE_NAME;

5.ORACLE查询表空间使用情况

SELECT TOTAL.NAME "TABLESPACE NAME",FREE_SPACE,(TOTAL_SPACE - FREE_SPACE) USED_SPACE,TOTAL_SPACE
FROM (SELECT TABLESPACE_NAME, SUM(BYTES / 1024 / 1024) FREE_SPACE
      FROM SYS.DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) FREE,
     (SELECT B.NAME, SUM(BYTES / 1024 / 1024) TOTAL_SPACE
      FROM SYS.V_$DATAFILE A, SYS.V_$TABLESPACE B
      WHERE A.TS# = B.TS#
      GROUP BY B.NAME) TOTAL
WHERE FREE.TABLESPACE_NAME = TOTAL.NAME;

6.ORACLE修改表空间为自动增长

ALTER DATABASE DATAFILE ‘对应的数据文件路径信息’ AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED;    //每次自动增长8M，无上限

ALTER DATABASE DATAFILE '对应的数据文件路径信息' AUTOEXTEND ON;     //打开自动增长

ALTER DATABASE DATAFILE '对应的数据文件路径信息' AUTOEXTEND OFF;    //关闭自动增长

7.ORACLE查询所有表空间及其对应的数据文件路径信息

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

8.ORACLE查询表所在的表空间

SELECT TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='tableName';

9.ORACLE查询数据类型所在的表

SELECT
    OWNER,
    ALL_TAB_COLUMNS.TABLE_NAME,
	ALL_TAB_COLUMNS.COLUMN_NAME,
	ALL_TAB_COLUMNS.DATA_TYPE
FROM
	ALL_TAB_COLUMNS
WHERE
	ALL_TAB_COLUMNS.DATA_TYPE = '数据类型';

10.ORACLE查询表数据大小

SELECT
	SEGMENT_TYPE,
	SEGMENT_NAME,
	TRIM( TO_CHAR( BB / 1024 / 1024, 99999999999990.99 ))|| ' MB'
FROM
	(SELECT
		SEGMENT_TYPE,
		SEGMENT_NAME,
		SUM(BYTES) BB
	FROM
		USER_SEGMENTS
	WHERE
		SEGMENT_TYPE NOT IN ('LOBSEGMENT') AND SEGMENT_NAME NOT IN (SELECT OBJECT_NAME FROM RECYCLEBIN)
	GROUP BY
		SEGMENT_TYPE,
		SEGMENT_NAME
	UNION ALL SELECT
		US.SEGMENT_TYPE,
		UL.TABLE_NAME,
		SUM(BYTES) BB
	FROM
		USER_SEGMENTS US,
		USER_LOBS UL
	WHERE
		US.SEGMENT_TYPE IN ('LOBSEGMENT') AND US.SEGMENT_NAME = UL.SEGMENT_NAME
	GROUP BY
		US.SEGMENT_TYPE,
		UL.TABLE_NAME
	ORDER BY
		BB DESC)
WHERE
	SEGMENT_NAME = 'tableName';

11.ORACLE查询表的字段类型和长度

SELECT
	USER_TAB_COLUMNS.COLUMN_NAME AS COLUMN_NAME,
	USER_TAB_COLUMNS.DATA_TYPE AS TYPE_NAME,
	USER_TAB_COLUMNS.DATA_PRECISION AS COLUMN_SIZE,
	USER_TAB_COLUMNS.CHAR_LENGTH AS CHAR_LENGTH
FROM
	USER_TAB_COLUMNS,
	USER_TABLES,
	USER_COL_COMMENTS
WHERE
	USER_TAB_COLUMNS.TABLE_NAME = USER_TABLES.TABLE_NAME
	AND USER_COL_COMMENTS.TABLE_NAME = USER_TAB_COLUMNS.TABLE_NAME
	AND USER_COL_COMMENTS.COLUMN_NAME = USER_TAB_COLUMNS.COLUMN_NAME
	AND USER = 'LCICBC9999'
	AND USER_TABLES.TABLE_NAME = 'BILLCODERULES'
ORDER BY
	USER_TABLES.TABLE_NAME ASC ,
	USER_TAB_COLUMNS.COLUMN_ID ASC

12.ORACLE插入RAW类型数据

CREATE
	TABLE
		RAWTABLE(
			ID INT,
			RAWDATA RAW
		);

INSERT INTO RAWTABLE VALUES(1, UTL_RAW.CAST_TO_RAW('highgo'));

13.ORACLE插入CHR(0)数据

CREATE
	TABLE
		HIGHGOTB (
			ID INT,
			NAME VARCHAR(10)
		);
		
INSERT INTO HIGHGOTB VALUES(1,CHR(0) || 'n');

14.ORACLE查询数据类型所在表的数据大小

SELECT
	SEGMENT_TYPE,
	SEGMENT_NAME,
	TRIM( TO_CHAR( B / 1024 / 1024, 99999999999990.99 )) || ' MB' AS DATA_SIZE
FROM
	(SELECT
		SEGMENT_TYPE,
		SEGMENT_NAME,
		SUM( BYTES ) B
	 FROM
		USER_SEGMENTS
	 WHERE
		SEGMENT_TYPE NOT IN ('LOBSEGMENT')
		AND SEGMENT_NAME NOT IN (
		SELECT
			OBJECT_NAME
		FROM
			RECYCLEBIN)
		GROUP BY
			SEGMENT_TYPE,
			SEGMENT_NAME
	 UNION ALL SELECT
		US.SEGMENT_TYPE,
		UL.TABLE_NAME,
		SUM( BYTES ) BB
	 FROM
		USER_SEGMENTS US,
		USER_LOBS UL
	 WHERE
		US.SEGMENT_TYPE IN ('LOBSEGMENT')
		AND US.SEGMENT_NAME = UL.SEGMENT_NAME
	 GROUP BY
		US.SEGMENT_TYPE,
		UL.TABLE_NAME
	 ORDER BY
		B DESC)
WHERE
	SEGMENT_NAME IN (
		SELECT
			ALL_TAB_COLUMNS.TABLE_NAME
		FROM
			ALL_TAB_COLUMNS
		WHERE
			ALL_TAB_COLUMNS.DATA_TYPE = '数据类型'
			AND owner = '用户名');