from-sh

SELECT T.*, EVENTS.EVENT_CNT, ROUTINES.ROUTINE_CNT, TRIGGERS.TRIGGER_CNT
FROM 
(
SELECT TABLE_SCHEMA, COUNT(TABLE_NAME) AS TABLE_CNT
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA
) T
LEFT JOIN
(
SELECT TABLE_SCHEMA, COUNT(TRIGGER_NAME) AS TRIGGER_CNT
FROM INFORMATION_SCHEMA.TRIGGERS
GROUP BY TABLE_SCHEMA
) TRIGGERS ON T.TABLE_SCHEMA = TRIGGERS.TRIGGER_NAME
LEFT JOIN(
SELECT EVENT_SCHEMA, COUNT(EVENT_SCHEMA) AS EVENT_CNT
FROM INFORMATION_SCHEMA.EVENTS
GROUP BY EVENT_SCHEMA
) EVENTS ON T.TABLE_SCHEMA = EVENTS.EVENT_SCHEMA
LEFT JOIN(
SELECT ROUTINE_SCHEMA, ROUTINES(ROUTINE_NAME) AS ROUTINE_CNT
FROM INFORMATION_SCHEMA.EVENTS
GROUP BY ROUTINE_SCHEMA
) ROUTINES ON T.TABLE_SCHEMA = ROUTINES.ROUTINE_SCHEMA
WHERE T.TABLE_SCHEMA NOT IN 
(
'INFORMATION_SCHEMA','MYSQL','PERFORMANCE_schema','sys'
);

mysql 데이터 복구 하다 해당 에러 발생 

걍 백업 파일이 깨진거 다시 백업 후 복구!

ERROR 1064 (42000) at line 8823: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''<TABLE \\\"FONT-IZE: 12px\\\" cellSpacing=0 borderColorDark=white width=' at line 1

# ./mysqldump -uroot -p test > /data/test.sql
Enter password:
mysqldump: Couldn't execute 'SHOW FIELDS FROM `info`': SELECT command denied to user ''@'%' for column 'info1' in table 'status' (1143)

# ./mysql -uroot -p

mysql> SHOW FIELDS FROM info;
ERROR 1143 (42000): SELECT command denied to user ''@'%' for column ' info1' in table 'status'

mysql> select * from test.info limit 5;
ERROR 1449 (HY000): The user specified as a definer ('test'@'%') does not exist

-- 계정, host 생성 
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON *.* to 'test'@'%';

FLUSH PRIVILEGES;

# mysqldump -uroot -p test> /var/lib/mysql/test.sql
Enter password:
mysqldump: Error 1194: Table 'log' is marked as crashed and should be repaired when dumping table `log` at row: 8521321

# mysql -uroot -p
mysql> check table log;
+---------------+-------+----------+----------------------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                                 |
+---------------+-------+----------+----------------------------------------------------------+
| test.log | check | warning  | 3 clients are using or haven't closed the table properly |
| test.log  | check | error    | Found wrong packed record at 1049886676                  |
| test.log  | check | error    | Corrupt                                                  |
+---------------+-------+----------+----------------------------------------------------------+
3 rows in set (38.94 sec)

mysql> repair table log;
+---------------+--------+----------+-------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                  |
+---------------+--------+----------+-------------------------------------------+
| test.log  | repair | info     | Found wrong packed record at 1049886676   |
| test.log  | repair | warning  | Number of rows changed from 0 to 10540321 |
| test.log  | repair | status   | OK                                        |
+---------------+--------+----------+-------------------------------------------+
3 rows in set (6 min 45.35 sec)

mysql> check table log;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| test.log   | check | status   | OK       |
+---------------+-------+----------+----------+
1 row in set (36.82 sec)

MariaDB [(none)]> show global variables like '%expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000077 | 1073742455 |
| mysql-bin.000078 | 1073742613 |
| mysql-bin.000079 | 1073742043 |
| mysql-bin.000080 |  714294090 |
+------------------+------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> purge binary logs to 'mysql-bin.000079';
Query OK, 0 rows affected (0.634 sec)

MariaDB [(none)]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000079 | 1073742043 |
| mysql-bin.000080 |  714294090 |
+------------------+------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> exit
Bye

[maria@db binary]$ ll
total 1750224
-rw-rw---- 1 maria maria 1073742043 Jun  9 10:03 mysql-bin.000079
-rw-rw---- 1 maria maria  714703482 Jun 17 10:00 mysql-bin.000080
-rw-rw---- 1 maria maria         70 Jun 17 10:00 mysql-bin.index

** FK TABLE -> TRUNCATE X

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ALTER TABLE TABLE_NAME DISABLE PRIMARY KEY CASCADE;
ALTER TABLE TABLE_NAME ENABLE CONSTRAINT FK1ABB73AAB9B989D;

-- 제약조건 조회
SELECT * FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = 'TABLE_NAME';

-- 해당 컬럼 조회
SELECT * FROM DBA_CONS_COLUMNS
WHERE TABLE_NAME = 'TABLE_NAME';

-- 통계정보 확인

SELECT * FROM DBA_AUTOTASK_OPERATION;

-- 통계정보 수집 기능 OFF
begin
dbms_auto_task_admin.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL
);
end;
/

begin
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL
);
end;
/

begin
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
end;
/

ALTER TABLE TABLE_NAME SPLIT PARTITION PARTITION_NAME AS ('') INTO (PARTITION 추가할 파티션 이름, PARTITION 현재 MIN PARTITION NAME);

EX)

ORA-14260 incorrect physical attribute specified for this partition

HASH PARTITION NOLOGGING 옵션 사용 안됨.

ALTER TABLE TABLE_NAME MOVE PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME 

PARALLEL 32;

ALTER INDEX INDEX_NAME REBUILD PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME PARALLEL 32;

-- AQ table (Advanced Queuing table)
일반 DDL 명령어 X

-- TABLE 조회
SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME FROM DBA_INDEXES 

WHERE INDEX_TYPE LIKE 'IOT%';

VERSION 10.2.0.5 까지
EXEC MOVE_AQT.MOVE('<OWNER>','<QUEUE_TABLE_NAME>', '<TABLESPACE_NAME>');

VERSION 11.2.0.3 까지
BEGIN
MOVE_QT_PKG.MOVE_QUEUE_TABLE('OWNER','QUEUE_TABLE_NAME','FROM_TABLESPACE','TO_TABLESPACE');
END;
/

How To Move An IOT Table To Another Tablespace (Doc ID 453997.1)

-- ERROR 확인 
show errors