SQL « Marcel Beck

DECLARE
  FUNCTION NAMED_DAYS_BETWEEN(VP_MIN IN DATE,
                              VP_MAX IN DATE,
                              VP_DAY IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN TRUNC(NEXT_DAY(VP_MAX, VP_DAY) - NEXT_DAY(VP_MIN - 1, VP_DAY)) / 7;
  END;

  FUNCTION BUSINESS_DAYS_BETWEEN(VP_MIN IN DATE, VP_MAX IN DATE)
    RETURN NUMBER IS
  BEGIN
    RETURN VP_MAX - VP_MIN -(NAMED_DAYS_BETWEEN(VP_MIN, VP_MAX, 'SAT') + -- German: SA
                             NAMED_DAYS_BETWEEN(VP_MIN, VP_MAX, 'SUN')) + 1; -- German: SO
  END;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Business Days: ' ||
                       BUSINESS_DAYS_BETWEEN(SYSDATE, SYSDATE + 15));
END;

DECLARE

FUNCTION NAMED_DAYS_BETWEEN(VP_MIN IN DATE,

VP_MAX IN DATE,

VP_DAY IN VARCHAR2) RETURN NUMBER IS

BEGIN

RETURN TRUNC(NEXT_DAY(VP_MAX, VP_DAY) - NEXT_DAY(VP_MIN - 1, VP_DAY)) / 7;

END;

FUNCTION BUSINESS_DAYS_BETWEEN(VP_MIN IN DATE, VP_MAX IN DATE)

RETURN NUMBER IS

BEGIN

RETURN VP_MAX - VP_MIN -(NAMED_DAYS_BETWEEN(VP_MIN, VP_MAX, 'SAT') + -- German: SA

NAMED_DAYS_BETWEEN(VP_MIN, VP_MAX, 'SUN')) + 1; -- German: SO

END;

BEGIN

DBMS_OUTPUT.PUT_LINE('Business Days: ' ||

BUSINESS_DAYS_BETWEEN(SYSDATE, SYSDATE + 15));

END;

SELECT TO_DATE(TABLE.DATE_FIELD) AS DATE_FIELD
  FROM TABLE
 WHERE TABLE.DATE_FIELD > TO_DATE(TABLE.DATE_FIELD)
   AND TABLE.DATE_FIELD < TO_DATE(TABLE.DATE_FIELD) + NUMTODSINTERVAL('1', 'day')

SELECT TO_DATE(TABLE.DATE_FIELD) AS DATE_FIELD

FROM TABLE

WHERE TABLE.DATE_FIELD > TO_DATE(TABLE.DATE_FIELD)

AND TABLE.DATE_FIELD < TO_DATE(TABLE.DATE_FIELD) + NUMTODSINTERVAL('1', 'day')

watch -n 1 --differences "mysql -u username -pPassword -e 'show processlist'"

1	watch -n 1 --differences "mysql -u username -pPassword -e 'show processlist'"

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

1 2	GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;

Move a complete MySQL Database to a new Server

You can even rename the database, just use another database name on the remote server.

Dump the database direct to the remote mysql server

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> | mysql -h <server host/ip> -u <username> -p'<password>' <database>

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> \| mysql -h <server host/ip> -u <username> -p'<password>' <database>

Use ssh if you do not have direct access to the remote mysql server. (Secure)

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> | ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>' <database>"

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> \| ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>' <database>"

Move a complete MySQL Server to a new Server

Dump all databases direct to the remote mysql server

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases | mysql -h <server host/ip> -u <username> -p'<password>'

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases \| mysql -h <server host/ip> -u <username> -p'<password>'

Use ssh if you do not have direct access to the remote mysql server. (Secure)

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases | ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>'"

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases \| ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>'"

SELECT *
FROM (SELECT INNER.*, ROWNUM RNUM
FROM (
/* SELECT Statement with Order BY */
)
INNER WHERE ROWNUM < = 2000)
WHERE RNUM >= 1000

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause

Single Parent:

SELECT T.ID
FROM TABLE T
CONNECT BY T.ID_PRE = PRIOR T.ID
START WITH T.ID = 1

Multiple Parents:

SELECT T.ID
FROM TABLE T
CONNECT BY T.ID_PRE = PRIOR T.ID
START WITH T.ID IN (1, 2, 3)

Oracle Doc: Hierarchical Queries

ORA-06550: Zeile 33, Spalte 24: PLS-00221: 'FUNKTIONS_NAME' ist keine Prozedur oder ist nicht definiert ORA-06550: Zeile 33, Spalte 24: PL/SQL: Statement ignored

Dieser Fehler ist bei mir aufgetaucht, weil ich eine Funktion als Prozedur aufgerufen haben. Oracle erwartet aber, dass der Rückgabewert einer Funktion in eine Variable eingelesen wird.

Fehlerhaft:

FUNKTIONSNAME(I_ID => UI_ID, I_ID2 => UI_ID2)

Korrekt:

L_RC := FUNKTIONSNAME(I_ID => UI_ID, I_ID2 => UI_ID2)

Literal quoting:

stmt := q'[SELECT * FROM table where column = 'text']';

Documentation for literals

Double single quote to denote single quote:

stmt := 'SELECT * FROM table where column = ''text''';

In my Opinion, the Q syntax is more flexible and readable.

So you wanted to change the value of “innodb_log_file_size” on your mysql server.

As it happens, you can’t just change it the “my.cnf” file, restart and make it work. If you do, Mysql will refuse to start and throw this error instead. You can the details of the startup error in your mysql error log file.

To safely change this setting follow these steps

Shutdown your mysql server, make sure to check the error log that the shutdown had no issues

Move the “ib_logfile0″ and “ib_logfile1″ files from your mysql data directory.

Change “innodb_log_file_size” to your desired setting.

Restart mysql. Check the error log to make sure everything started successfully, mysql may complain that the log files don’t exist, it will make new ones and start.
Make sure if you are changing things on a production server, you take due care of the mysql downtime due to the changes and restart, and have backups to recover quickly in case something goes wrong.

Marcel Beck

Ich mag Züge…

Archiv der Kategorie: SQL

Oracle – Calculate Business Days between dates

Oracle – Check if Field is Date or Datetime

MySQL – Simple Top / Processlist

MySql – Remote Root Access

MySQL – Move Database to new Server

Oracle – Hierarchical Query with multiple parents

Oracle – ORA-06550 PLS-00221 is not a procedure or is undefined

Oracle – PL/SQL – Escape single quote

“InnoDB: Error: log file ./ib_logfile0 is of different size” Error