約 4,452,112 件
https://w.atwiki.jp/mysqlmemo/pages/13.html
大見出しTEST
https://w.atwiki.jp/sfrontier/pages/85.html
Knowledge and Technique 作成日 2007/11/20 H.Naito 更新日 2007/11/20 H.Naito DB Information 便利SQLデータベースをロックしっぱなしになっているあほを見つけるための SQL 無茶な SQL 文を実行しているあほを見つけるための SQL DB Information オブジェクト SELECT * FROM USER_OBJECTS; 制約 SELECT * FROM USER_CONSTRAINS; SELECT * FROM USER_CONS_COLUMNS; INDEX SELECT * FROM USER_INDEX; SELECT * FROM USER_IND_COLUMNS; 便利SQL データベースをロックしっぱなしになっているあほを見つけるための SQL select * from v$locked_object l, user_objects o, v$session s where l.object_id = o.object_id and l.session_id = s.sid 無茶な SQL 文を実行しているあほを見つけるための SQL select * from v$session s, v$sql q where s.sql_address = q.address STATUS = ACTIVE のものが現在発行中の SQL PROCESS はホスト側の ProccessID ( Oracle のセッションではない。SQLを発行しているモジュールの ProcessID )
https://w.atwiki.jp/programhack/pages/22.html
Oracle関連 CHAR と VARCHAR2 の違い CHAR と VARCHAR2 の違いではまった。 比較するときにCHARをTRIMすることで一致するようになった。 例 UPDATE TXEA_06402_SIKAKU SIKAKU SET SIKAKU.KZOKUCD = ( SELECT KZOKU.ZOKUCD FROM KZOKU_CONV KZOKU WHERE TRIM(KZOKU.KZOKUORG) = SIKAKU.KZOKUCD ) 参考サイト? CHAR と VARCHAR2 の違い http //www.shift-the-oracle.com/element/data-type/varchar2-char-comparison.html SELECT結果を変数に保持する SELECT INTOでSELECT結果を変数に保持することができる。 参考サイト PL/SQL で SELECT INTO を行なう http //www.shift-the-oracle.com/plsql/select.html TRIMSPOOL 参考サイト TRIMSPOOL システム変数 http //www.shift-the-oracle.com/sqlplus/system-variable/trimspool.html
https://w.atwiki.jp/nofx/pages/115.html
インストールやりなおしインストール(昔) 起動 データベースへ接続できないとき設定し直す 設定cnfファイルをコピー サーバへ接続デフォルト ブランクユーザの削除 DBの管理新規ユーザ権限の設定Servlet用のDBとそれを扱うユーザを設定する バックアップmysqldump(SQLベースのバックアップ) コマンド・SQLテーブル名の変更 設定環境情報を見る 環境変数を見れる テーブル情報の表示 ファイルからのデータ挿入 AUTO_INCREMENTをリセット 重複レコードの抽出 インデックス関連インデックスを貼る インデックスの確認 インデックスの使用を確認する インデックスの削除 SQLの結果行数を取得 テーブルのコピー1 2 テーブルの全データ削除 テーブル構成の変更カラムの追加 カラムの削除 カラムのデータ型の変更 カラム名とデータ型の変更 テーブル名の変更 server再起動したらRubyからDBアクセスができなくなった件について 文字化けDB@FreeBSDが扱う文字列をUTF-8に統一 データ挿入 JDBC Link ~ インストールやりなおし 文字化けが直らずソースからインストールこすることに。 ダウンロード MySQL Community Serverをダウンロード(mysql-5.0.33.tar.gz) tar.gz形式ソース版http //dev.mysql.com/downloads/ グループ追加 pw groupadd mysql ユーザ追加 adduser -g mysql -d /usr/local/var mysql ファイル解凍 tar xvzf mysql-5.0.33.tar.gz configure cd mysql-5.0.33 ./configure --with-charset=sjis -with-extra-charsets=all --with-mysqld-user=mysql インストール make make install DB初期化 /usr/local/bin/scripts/mysql_install_db --user=mysql chown -R mysql /usr/local/var chgrp -R mysql /usr/local/var インストール(昔) sysinstallで入れた。 MySQLはrootでも起動でるが、昨今のネットワーク事情を考えると、専用ユーザーを使用した方がより安全。そこでグループとユーザーを追加する。ユーザーホームはMySQLのデータディレクトリ/var/db/mysql/を指定。 グループmysqlの追加; # pw groupadd mysql ユーザmysqlの追加; # adduser -g mysql -d /var/db/mysql/ ↑二つはしなくても設定済み? データベースを初期化、その後データディレクトリのオーナーを先ほど作成したmysqlユーザーに修正 # /usr/local/bin/mysql_install_db --user=mysql # chown -R mysql /var/db/mysql/ # chgrp -R mysql /var/db/mysql/ 起動 # /usr/local/bin/mysqld_safe --user=mysql # /usr/local/share/mysql/mysql.server start --default-character-set=utf8 停止 # /usr/local/share/mysql/mysql.server stop ソケットを介さない方法 # mysqladmin --host=133.XX.XX.XX shutdown データベーステーブルの確認 # /usr/local/bin/mysqlshow データベースへ接続できないとき 病状 パスワードを確実に入力しているものの接続を拒否される $ mysql ERROR 1045 (28000) Access denied for user ‘momota’@ #039;localhost’ (using password NO) $ mysql -p Enter password ERROR 1045 (28000) Access denied for user ‘momota’@ #039;localhost’ (using password YES) $ mysql -u root ERROR 1045 (28000) Access denied for user ‘root’@ #039;localhost’ (using password NO) $ mysql -u root -p Enter password ERROR 1045 (28000) Access denied for user ‘root’@ #039;localhost’ (using password YES) 原因の考察 ポートが開いていない 昔の設定が残っているからなんかおかしい(今回はこれっぽい感じだった) パスワードを本当に忘れた。 解決法 mysqlデーモンを再起動 skip-grant-tablesオプションを指定するとユーザー確認用のデータを読み込まずに起動 $ /usr/local/mysql/bin/mysqld_safe --skip-grant-tables 設定し直す パスワード設定 mysql UPDATE mysql.user SET Password=PASSWORD( #039;YOUR-PASSWORD #039;) WHERE User= #039;root #039;; mysql FLUSH PRIVILEGES; 確認 mysql SELECT Host,User,Password FROM mysql.user; 設定 rootユーザのパスワード設定 # mysqladmin -u root password #039;new_password #039; # mysqladmin reload mysqladmin を使ってパスワードを設定する場合、設定後にmysqladmin の reload コマンドを実行する必要があるらしい。 だが、拒否されてしまったので以下のようにしたらOKだった。 # mysqladmin reload -u root -p Enter password さっき設定したパスワード cnfファイルをコピー 自分の環境にあったものをコピー # cp /usr/local/share/mysql/my-***.cnf /var/db/mysql/my.cnf 実は/etc/の下に置くべき? 使用する文字セットを設定 以下、my.cnfファイルの中身。日本語化。 [client] #039; #039;default-character-set=utf8 #039; #039; #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] #039; #039;default-character-set=utf8 #039; #039; #039; #039;character-set-server=utf8 #039; #039; #039; #039;language=japanese #039; #039; port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] #039; #039;default-character-set=utf8 #039; #039; quick max_allowed_packet = 16M [mysql] #039; #039;default-character-set=utf8 #039; #039; no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout サーバへ接続 # mysql [-h host_name] [-u user_name] [-pyour_pass] オプション-pのあとにパスワードを入力できるが-pだけをうったら次の行にEnter password と表示されるからそれから入力したほうが安全。 デフォルト オプション デフォルト値 -h localhost -u UNIXのログイン名 設定ファイルにパスワードを書いておくこともできる。そうすることにより、毎回毎回コマンドラインに引数を与えなくてすむようになる。まず、自分のホームディレクトリに .my.cnf を作り、そのファイルの中の [client] セクションに接続用のパラメターを記述する。 [client] host=host_name user=user_name password=your_pass .my.cnf ファイルはグループやその他のユーザーが読み書きできないように。ファイルのパーミッションは 400 か 600 が最適。 ブランクユーザの削除 mysql delete from user where user= #039; #039;; mysql select * from user; DBの管理 新規ユーザ権限の設定 Servlet用のDBとそれを扱うユーザを設定する まずMySQLにルートでログイン servlet用のDBを作る create database servletDB; ユーザの追加 grant all on servletDB.* to ユーザ名@ #039;% #039; identified by #039;パスワード #039;; ユーザは全てのホストでservletDBを使用可能。 @以下を書き換えればドメインによる制限が可能。%は’*’みたいなもん。 例:@"%.domain.co.jp" ユーザテーブルのリフレッシュ flush privileges; しなくてもなったけど。。。 バックアップ mysqldump(SQLベースのバックアップ) 存在するデータをすべてSQLにしてテキスト形式に保存 $ mysqldump -u root -x --all-databases dump.sql 特定のデータベースのみのバックアップ $ mysqldump -u root データベース名 dump.sql mysqldumpのすべてのデータベースのバックアップを復元 $ mysql -u root -p dump.sql 特定データベースのみの復元 $ mysql -u root データベース名 dump.sql コマンド・SQL テーブル名の変更 ALTER TABLE 元のテーブル名 RENAME TO 新しいテーブル名; 設定環境情報を見る status; 環境変数を見れる show variables; テーブル情報の表示 desc テーブル名; ファイルからのデータ挿入 LOAD DATA INFILE #039;/home/momota/5to20a_ID_name.txt #039; INTO TABLE aid2aname FIELDS TERMINATED BY #039;, #039; LINES TERMINATED BY #039;\n #039;; AUTO_INCREMENTをリセット ALTER TABLE table_name AUTO_INCREMENT=max_id_number 重複レコードの抽出 SELECT * FROM テーブルA WHERE 列A IN (SELECT 列A FROM テーブルA GROUP BY 列A HAVING COUNT(*) 1); インデックス関連 インデックスを貼る CREATE INDEX インデックス名 ON テーブル名 (フィールド名[, フィールド名, ...]); ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名); インデックスの確認 SHOW INDEX FROM テーブル名; インデックスの使用を確認する EXPLAIN SQL文 インデックスの削除 DROP INDEX インデックス名 ON テーブル名; SQLの結果行数を取得 LIMIT句で取得レコードが限定された場合でも全体のレコード数を取得 SELECT SQL_CALC_FOUND_ROWS * FROM テーブル名 LIMIT 0,1; SELECT FOUND_ROWS(); と実行すると、直前クエリのLIMIT句で制限されないレコード数が取得できる。 WHERE句を使った場合もOK SELECT SQL_CALC_FOUND_ROWS * FROM bookmark WHERE url_id = 1; +--------+---------+-------+------------+ | url_id | user_id | tid | date | +--------+---------+-------+------------+ | 1 | 135 | 1365 | 2007-04-18 | | 1 | 135 | 17365 | 2007-04-18 | | 1 | 1238 | 1365 | 2006-01-14 | | 1 | 1238 | 426 | 2006-01-14 | | 1 | 1550 | 1365 | 2005-10-02 | | 1 | 2211 | 23083 | 2005-10-03 | | 1 | 3933 | 426 | 2006-01-12 | | 1 | 3933 | 1365 | 2006-01-12 | | 1 | 4613 | 1365 | 2007-04-26 | | 1 | 4613 | 1073 | 2007-04-26 | | 1 | 4613 | 882 | 2007-04-26 | | 1 | 12546 | 1365 | 2005-10-02 | | 1 | 12546 | 12637 | 2005-10-02 | | 1 | 15932 | 4776 | 2006-11-22 | +--------+---------+-------+------------+ 14 rows in set (0.04 sec) SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 14 | +--------------+ 1 row in set (0.00 sec) テーブルのコピー 1 CREATE TABLE カラム作成 以下のSQL INSERT INTO `to_db`.`to_table` SELECT * FROM `from_db`.`from_table`; 2 CREATE TABLE to AS SELECT * FROM from_tb; WHERE句で条件付きのコピーも可能 テーブルの全データ削除 TRUNCATE [TABLE] hogetb; deleteよりも高速に削除可能 テーブル構成の変更 カラムの追加 ALTER TABLE テーブル名 ADD カラム名 型; カラムの削除 ALTER TABLE テーブル名 DROP カラム名; カラムのデータ型の変更 ALTER TABLE テーブル名 MODIFY カラム名 新しいデータ型; カラム名とデータ型の変更 ALTER TABLE CHANGE 古いカラム 新しいカラム 新しいデータ型; テーブル名の変更 ALTER TABLE 古いテーブル名 RENAME AS 新しいテーブル名; server再起動したらRubyからDBアクセスができなくなった件について error /usr/local/lib/ruby/site_ruby/1.8/dbi.rb 329 in `load_driver Unable to load driver Mysql (DBI InterfaceError) from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb 227 in `_get_full_driver from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb 213 in `connect 参考: http //www.rollamonkey.com/blog/?p=20 やってみたら動いた % sudo gem install mysql -- --with-mysql-dir=/usr/local/var % cd /usr/local/lib/ruby/gems/1.8/gems/mysql-2.7/ % sudo ruby extconf.rb install mysql -- --with-mysql-dir=/usr/local/var % make % make install clean 文字化け DB@FreeBSDが扱う文字列をUTF-8に統一 [client] port= 3306 socket= /tmp/mysql.sock [mysqld] #039; #039;default-character-set= utf8 #039; #039; init-connect= SET NAMES utf8 port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id= 1 [mysqldump] quick max_allowed_packet = 16M [mysql] #039; #039;default-character-set = utf8 #039; #039; no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout データ挿入 ファイルからの場合はUTF-8に変換してから挿入するもしUnix上で select * from hoge; で文字化けして表示されてもJDBC経由でデータ取ってきたら化けてないので御注意を。 JDBC jdbc mysql //ホスト名/データベース名?useUnicode=true characterEncoding=UTF8 で接続 Link smart++ MySQLクイック・リファレンス MySQLについて 四 ALTER TABLE(テーブル構成の変更)
https://w.atwiki.jp/kuronekosoft/pages/13.html
黒猫 SQL Studio で Oracle に接続するには、OleDB Provider または、ODBCドライバを使用します。 ここでは、OleDB Provider を使用した接続方法を説明します。 まず、ドライバはOracle提供の Oracle Provider for OLE DB を使用してください。Microsoft の Oracleドライバは、実装が不十分で使用できません。 Oracleに接続するには、Oracleクライアントが正しく設定されている必要があります。 Oracleクライアントに付属の Net Configuration Assistant や Net Manager などを使用するか、tnsnames.ora を直接編集して接続先を設定します。 正しく設定されているか確認するには、tnsping や Sql plus などを使用します。 以下の手順は、TNSの設定が正しく行われていることが前提となります。 [データベース]-[データベースに接続...]をクリックします。 [データベース接続]ダイアログの[OleDB接続]タブを選択し、[新規追加]ボタンをクリックします。 [接続名]に接続を識別する任意の名前を入力します。 [接続文字列の編集]ダイアログで[接続文字列]の[...]ボタンをクリックします。 OLE DB プロバイダの一覧から、Oracle Provider for OLE DB を選択します。 [次へ]ボタンをクリックします。 [データソース]にtnsnames.oraで設定したホスト文字列を入力します。 [ユーザー名]と[パスワード]を入力します。 必要に応じて[パスワードを保存する]にチェックします。 [接続のテスト]ボタンをクリックして、接続をテストします。 [OK]ボタンをクリックします。 [接続文字列の編集]ダイアログの[OK]ボタンをクリックします。 [データベース接続]ダイアログの[接続]ボタンをクリックします。 ご自由にコメントをお書き込みください 名前 コメント
https://w.atwiki.jp/redcloud/pages/20.html
目次 目次 前提条件 MySQL5.0.67-tritonn1.0.12 インストール mysqlユーザの作成 ncurses-develパッケージのインストール mecab本体+mecab辞書インストール sennaインストール mysql+tritonn本体インストール 設定 初期設定 起動・停止確認 my.cnf修正 my.cnfが適用できているか確認 起動スクリプト作成、自動起動設定(サービス化) セキュリティ強化 DB動作確認 DB作成 ユーザ作成&権限付与 作成したユーザでのログイン確認 テーブル作成 日本語を含むデータをコマンドラインから投入、閲覧 全文検索テスト Tips Ver 5系 有名なお言葉 最適化TIPS 前提条件 CentOS5.4上に構築する MySQL5.0.67-tritonn1.0.12 インストール mysqlユーザの作成 # groupadd mysql # useradd -g mysql -s /sbin/nologin mysql ncurses-develパッケージのインストール # yum -y install ncurses-devel mecab本体+mecab辞書インストール mecab本体 $ wget http //downloads.sourceforge.net/project/mecab/mecab/0.98/mecab-0.98.tar.gz?use_mirror=jaist $ tar zxvf mecab-0.98.tar.gz $ cd mecab-0.98 $ ./configure --prefix=/usr/local --with-charset=utf8 $ make $ sudo make install mecab辞書 $ wget http //downloads.sourceforge.net/project/mecab/mecab-ipadic/2.7.0-20070801/mecab-ipadic-2.7.0-20070801.tar.gz?use_mirror=jaist $ tar zxvf mecab-ipadic-2.7.0-20070801.tar.gz $ cd mecab-ipadic-2.7.0-20070801 $ ./configure --prefix=/usr/local --with-mecab-config=/usr/local/bin/mecab-config --with-charset=utf8 $ make $ sudo make install sennaインストール $ wget http //osdn.dl.sourceforge.jp/senna/33763/senna-1.1.4.tar.gz $ tar zxvf senna-1.1.4.tar.gz $ cd senna-1.1.4 $ ./configure --prefix=/usr/local --with-mecab-config=/usr/local/bin/mecab-config nkfc.cのmakeにかなり時間が掛かる(場合によってはメモリallocateでエラーとなってしまう事もある)ので回避策 $ cd lib $ ../libtool --tag=CC --mode=compile gcc -I. -I.. -Wall -O0 -fno-strict-aliasing -g -MT nfkc.lo -MD -MP -MF .deps/nfkc.Plo -c -o nfkc.lo nfkc.c $ make $ sudo make install mysql+tritonn本体インストール $ wget http //osdn.dl.sourceforge.jp/tritonn/36449/tritonn-1.0.12-mysql-5.0.67.tar.gz $ tar zxvf tritonn-1.0.12-mysql-5.0.67.tar.gz $ cd tritonn-1.0.12-mysql-5.0.67 $ ./configure --prefix=/usr/local --with-charset=utf8 --with-extra-charsets=all --with-mysqld-user=mysql --with-readline --with-innodb --enable-local-infile --with-senna=/usr/local --with-mecab=/usr/local $ make $ sudo make install 設定 初期設定 $ cd /usr/local/bin/ $ sudo ./mysql_install_db --user=mysql $ sudo cp -p /usr/local/share/mysql/my-small.cnf /usr/local/etc/my.cnf $ cd /usr/local $ sudo chown -R mysql.mysql var 起動・停止確認 $ cd /usr/local/bin/ $ sudo ./mysqld_safe --user=mysql $ sudo ./mysqladmin -u root shutdown my.cnf修正 $ cd /usr/local/etc/ $ sudo vi my.cnf [mysqld] skip-character-set-client-handshake log log-warnings log-slow-queries=mysql-slow-queries long-query-time=2 key_buffer = 64M←DB専用サーバなら全メモリの30%~40%が目安、とりあえず10%弱程度 table_cache = 128←DB専用サーバなら全メモリの256が目安 max_connections = 100←運用サイトによりけりだが、300ぐらいが目安? thread_cache = 100←max_connectionsと同値が目安 max_allowed_packet = 1M←レコードサイズの大きいテーブルを使う場合などは8Mぐらいまで大きくしてもいいかも # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/var/ innodb_data_file_path = ibdata1 10M autoextend innodb_log_group_home_dir = /usr/local/var/ innodb_log_arch_dir = /usr/local/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 64M←DB専用サーバなら全メモリの70%が目安、とりあえず20%弱程度 innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M←innodb_buffer_pool_sizeの25%以上が目安 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1←よほどの事がない限り1で(特にミッションクリティカルなシステムは) innodb_lock_wait_timeout = 50 my.cnfが適用できているか確認 $ cd /usr/local/bin/ $ ./my_print_defaults mysqld 起動スクリプト作成、自動起動設定(サービス化) $ cd tar展開先 $ sudo cp -p support-files/mysql.server /etc/init.d/mysqld # cd /etc/init.d # chown root.root mysqld # chmod 755 mysqld # chkconfig --add mysqld # service mysqld start # service mysqld stop セキュリティ強化 # service mysqld start $ cd /usr/local/bin/ $ sudo ./mysqladmin -u root password proot $ ./mysql -u root -p mysql 匿名ユーザ削除 mysql delete from mysql.user where user = ; testデータベース削除 mysql drop database test; DB動作確認 DB作成 mysql create database apldb; ユーザ作成&権限付与 mysql grant all on apldb.* to apldbmaster@localhost identified by papldbmaster with grant option; mysql grant select,update,insert,delete on apldb.* to apldbuser@localhost identified by papldbuser ; mysql grant select,update,insert,delete on apldb.* to apldbuser@ 192.168.1.% identified by papldbuser ; 作成したユーザでのログイン確認 $ mysql -u apldbmaster -p apldb mysql テーブル作成 mysql CREATE TABLE t_sample ( - userid CHAR(8) NOT NULL - , username VARCHAR(20) NOT NULL - , telno CHAR(13) - , postno CHAR(8) - , mailaddress VARCHAR(100) - , pref CHAR(2) - , sex CHAR(2) - , passwd CHAR(15) NOT NULL - , entry_date DATETIME - , update_date DATETIME - , PRIMARY KEY (userid) - ) ENGINE=InnoDB DEFAULT CHARSET utf8 - ; 日本語を含むデータをコマンドラインから投入、閲覧 mysql INSERT INTO t_sample (userid,username,telno,postno,mailaddress,pref,sex,passwd,entry_date,update_date) - VALUES ( 00000001 , あいう 太郎 , 03-0000-0000 , 000-1111 , hoge@hoge.com , 01 , 01 , 11111111 ,NOW(),NOW()); mysql SELECT * FROM t_sample; +----------+--------------------+--------------+----------+---------------+------+------+----------+---------------------+---------------------+ | userid | username | telno | postno | mailaddress | pref | sex | passwd | entry_date | update_date | +----------+--------------------+--------------+----------+---------------+------+------+----------+---------------------+---------------------+ | 00000001 | あいう 太郎 | 03-0000-0000 | 000-1111 | hoge@hoge.com | 01 | 01 | 11111111 | 2008-05-13 23 59 33 | 2008-05-13 23 59 33 | +----------+--------------------+--------------+----------+---------------+------+------+----------+---------------------+---------------------+ 1 row in set (0.00 sec) 全文検索テスト mysql CREATE TABLE t_sample2 ( - userid CHAR(8) NOT NULL - , attr VARCHAR(20) NOT NULL - , etc TEXT - , PRIMARY KEY (userid) - ) ENGINE=MyISAM DEFAULT CHARSET utf8 - ; mysql CREATE FULLTEXT INDEX t_sample2_ftidx1 ON t_sample2 (attr,etc); mysql INSERT INTO t_sample2 (userid,attr,etc) VALUES ( 00000001 , ほげほげですが何か? , ほげほげと呼ばれてますが? ); mysql SELECT * FROM t_sample2 WHERE MATCH(attr,etc) AGAINST( 何か ); +----------+-----------------------------+-------------------------------------------------------------------------------+ | userid | attr | etc | +----------+-----------------------------+-------------------------------------------------------------------------------+ | 00000001 | ほげほげですが何か? | ほげほげと呼ばれてますが? | +----------+-----------------------------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Tips Ver 5系 有名なお言葉 MySQLでは、1つのクエリを実行するとき、1つのテーブルにつき1つのインデックスしか使用できない 最適化TIPS インデックス使わないパターン LIKEがワイルドカードで始まる時 WHEREとORDER BYのフィールドが違う時にはどちらかしか使われない カラムとカラムをORで検索する場合は複合インデックスが無効になる 複合インデックスが有効 WHERE col1=1; WHERE col1 AND col2=1; WHERE col1 AND (col2=1 OR col2=2); 複合インデックスが無効 WHERE col1=1 OR col2=1; //OR条件 WHERE col2=1 AND col1=1; //複合インデックスのカラム順定義と異なる WHERE col2=1; //複合インデックスの一部 WHERE col2=1 AND col3=1; //複合インデックス外のカラムを含む ソート(ORDER BY)関連 ソートする場合はインデックスがあるフィールドを「ORDER BY」において指定 グループ化(「GROUP BY」)する際にソートする必要がない場合でも「ORDER BY NULL」と指定。 「ORDER BY」がある場合、「DISTINCT」を使うと遅くなる ORDER BYでインデックスを使用できないパターン 複数のキーに対してORDER BYを実行する場合 SELECT * FROM t1 ORDER BY key1, key2; 連続しないキー部分に対してORDER BYを実行する場合 SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2; ASCとDESCが混在している場合 SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; 行の取り出しに使用されるキーが ORDER BYの実行に使用されるキーと異なる場合 SELECT * FROM t1 WHERE key2=constant ORDER BY key1; ORDER BYで多くのテーブルとカラムを結合していて、それら全てがレコードの取り出しに使用される最初の非 const テーブルではない場合(これは EXPLAINで出力される最初のテーブルで、かつ、constメソッドを使用していないテーブル) SELECT文の最適化 可能な限りSQL文は後から一括して実行 LEFT JOIN, STRAIGHT JOINとUSINGの組み合わせはWHEREより早い WHERE field INはかなり早い WHEREが使えるならHAVINGは使うな(HAVINGはindex使わず) SELECT等においてフィールドを呼び出す際、インデックスがあるものを優先して指定 WHERE節で不必要な()を使わない WHERE節で「LIKE」の代わりに「REGEXP」や「in」が使えないか検討 SELECT等においてフィールドを呼び出す際、不要なフィールドは呼び出さない LIMITを使い、呼び出すレコード数を制限 テーブルの結合はWHEREではなく「LEFT JOIN」を使用する 他 マルチプル「INSERT INTO」を利用。(※後から一括してSQL文を実行) UPDATEは遅い。一度「TRUNCATE TABLE」でテーブルを空にした後に更新されたデータを一気にマルチプル「INSERT INTO」することを検討 DELETEは遅い。「TRUNCATE TABLE」を検討 大量のレコードをDELETEした後は「OPTIMIZE TABLE」でテーブルを最適化 定期的に「ANALYZE TABLE」を実行 ハマりどころ UNION結合時、UNION内部クエリでORDER BYしても結果には反映されない。UNION外部でORDER BYする(=filesort発生)か、内部クエリそれぞれにLIMIT句を書いて件数を絞ると回避できる 参考サイト JOINは極力避ける JOINすると内部で複数テーブルの積に相当する一時テーブルを生成し、メモリを圧迫する インデックスが効かないORDER BY filesort発生(ディスクアクセス)、重い処理となる 参考サイト
https://w.atwiki.jp/wiki7_az/pages/8.html
rman でバックアップを取る dbca で rmanリポジトリ(データベース)を作成する。グローバル・データベース名 rmanrep SID rmanrep パスワード oracle キャラクタセット Unicode(AL32UTF8) 各国語キャラクタセット UTF8 oranavi で自動起動設定を ON にする。 rman ユーザを作成する。 $ export ORACLE_SID=rmanrep $ sqlplus "/ as sysdba" create user rman identified by rman quota unlimited on users; grant connect, resource, recovery_catalog_owner to rman; $ rman catalog rman/rman create catalog; connect target sys/oracle@orcl register database; backup database; rman tips Miracle Linux V4.0 で rman を使用できない $ which rman /usr/bin/X11/rman ← Oracle の rman とは別のプログラムを呼び出している。 アーカイブログ手動削除後に再同期するには crosscheck copy; delete expired copy; delete obsolete; リカバリ・カタログを更新する resync catalog; rman リポジトリの設定を確認する $ export ORACLE_SID=rmanrep rman connect target sys/manager@orcl.db01.oracle.co.jp connect catalog rman/rman@rmanrep show all RMAN 20003 エラーが発生する場合 reset database; DBA SQL DDL取得 set pagesize 0 SELECT DBMS_METADATA.GET_DDL( TABLE , USER_TABLES.TABLE_NAME), DBMS_METADATA.GET_DEPENDENT_DDL( INDEX , USER_TABLES.TABLE_NAME) FROM USER_TABLES; 表ロック調査 COL USERNAME FOR A8 COL OBJECT_NAME FOR A8 COL LMODE FOR 9999 COL PROGRAM FOR A8 COL CTIME FOR 9999 SELECT S.USERNAME, S.SID, S.SERIAL#, O.OBJECT_NAME, LMODE, V.CTIME, S.PROGRAM FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S, V$LOCK V WHERE O.OBJECT_ID = L.OBJECT_ID AND L.SESSION_ID = S.SID AND L.SESSION_ID = V.SID AND V.TYPE = TM ORDER BY CTIME DESC; 行ロック調査 SELECT S.USERNAME, S.SID, S.SERIAL#, O.OBJECT_NAME, LMODE, V.CTIME, S.PROGRAM FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S, V$LOCK V WHERE O.OBJECT_ID = L.OBJECT_ID AND L.SESSION_ID = S.SID AND L.SESSION_ID = V.SID AND V.TYPE = TX ORDER BY CTIME DESC; SQL調査 SELECT A.SQL_TEXT, A.ADDRESS FROM V$SQLAREA A, V$SESSION B WHERE A.ADDRESS = B.SQL_ADDRESS AND B.SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ( TX , TM )); 統計情報収集(スキーマ単位) BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ( ownname = スキーマ名 , options = GATHER AUTO ); END; / 統計情報収集(テーブル単位) BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME = スキーマ名 ,TABNAME = テーブル名 ); END; / 統計情報収集日付の確認 SELECT TABLE_NAME NAME, LAST_ANALYZED FROM USER_TABLES ORDER BY TABLE_NAME; 統計情報の内容確認 SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, BLOCKS FROM USER_TABLES ORDER BY TABLE_NAME; セッションを強制終了する SELECT SID|| , ||SERIAL#|| , ||STATUS|| , ||USERNAME FROM V$SESSION WHERE USERNAME = C_LIVE ; SID|| , ||SERIAL#|| , ||STATUS|| , ||USERNAME --------------------------------------------- 199,7860,INACTIVE,C_LIVE 213,6979,INACTIVE,C_LIVE ALTER SYSTEM KILL SESSION 199,7860 ALTER SYSTEM KILL SESSION 213,6979 DBID を確認する $ sqlplus "/ as sysdba" select dbid from v$database; select username, default_tablespace from dba_users; select FILE_NAME, TABLESPACE_NAME from dba_data_files; http //www.dbforums.com/archive/index.php/t-1066637.html select dbms_metadata.get_ddl( TRIGGER , name , owner ) from dual; ユーザ作成 $ sqlplus /nolog connect / as sysdba create user scott profile default identified by tiger default tablespace user01 temporary tablespace temp account unlock; grant resource, connect to amashin; quit; UNDO保存を保証する。 select tablespace_name, retention from dba_tablespaces; alter tablespace UNDOTBS1 retention guarantee; データファイルの自動拡張を有効化する。 select file_name, tablespace_name from dba_data_files; select autoextensible, tablespace_name from dba_data_files; alter database datafile /opt/app/oracle/oradata/orcl/undotbs01.dbf autoextend on; ユーザのアカウントロックを解除する。 -- ロック解除 alter user SCOTT account unlock; -- パスワード変更 $ sqlplus scott/tiger SCN からタイムスタンプを求める。 select current_scn scn, scn_to_timestamp(current_scn) timestamp from v$database; タイムスタンプから SCN を求める。 select timestamp_to_scn(systimestamp) scn, systimestamp timestamp from dual; SQL*Plus CSV書き出し set linesize 1000 set pagesize 0 set trimspool on spool user_data.txt select " ||id|| "," ||name|| " from users; シノニム PUBLICシノニムを作成する $ sqlplus "/ as sysdba" CREATE PUBLIC SYNONYM emp FOR scott.emp; シノニムを削除する drop public synonym samplelink; データベースリンク データベースリンクの定義を確認する $ export ORACLE_SID=orcl $ sqlplus "/ as sysdba" select owner,db_link,username,host,created from dba_db_links; データベースリンクを作成する $ export ORACLE_SID=orcl $ sqlplus "/ as sysdba" create public database link samplelink connect to scott identified by tiger using orcl.db01.oracle.co.jp ; データベースリンクを使用する $ export ORACLE_SID=orcl $ sqlplus scott/tiger select * from emp@samplelink データベースリンクを削除する drop public database link samplelink; スタンドバイ・データベースを rman でバックアップすると、ORA-19573 エラーが発生する 症状 ORA-19573 cannot obtain sub-shared enqueue for datafile 6 原因 RMAN in version 9.2.0.1 のバグです。(bug 2688591 (TAR 2708247.999)) 処置方法 SQL shutdown immediate; SQL startup nomount; SQL alter database mount standby database; SQL alter database recover managed standby database disconnect from session; 参考情報 http //www.trivadis.ch/Images/standbybackup_en_tcm17-7294.pdf If you create a new tablespace on the primary database, perform a logfile switch and want to back up this tablespace on the standby database,RMAN in version 9.2.0.1 aborts with an error RMAN backup tablespace test; ORA-19573 cannot obtain sub-shared enqueue for datafile 6 This behavior has now been accepted as bug 2688591 (TAR 2708247.999). But there is a workaround stopping and starting the standby database, after which the backup works correctly. export/import 全データベースモードでエクスポートする $ export ORACLE_SID=orcl $ exp system/パスワード file=fulldump.dmp log=fulldump.log full=y ユーザーモードでエクスポートする $ export ORACLE_SID=orcl $ exp scott/tiger file=scott.dmp owner=scott ユーザーモードでインポートする $ imp scott/tiger file=sccot.dmp fromuser=sccot touser=sccot destroy=y ignore=y ORA-19809 リカバリ・ファイルの制限を超えています pfile の db_recovery_file_dest_size を増やす。 $ export ORACLE_SID=devel $ sqlplus "/ as sysdba" SQL create pfile= $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora from spfile; SQL exit $ vi $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora 編集した pfile から spfile を作成する。 $ cd $ORACLE_BASE/admin/$ORACLE_SID/pfile $ mv init$ORACLE_SID.ora init$ORACLE_SID.ora.org $ sqlplus "/ as sysdba" SQL create spfile from pfile= $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora ; SQL startup 参考 14 ORA-19400~ORA-24276 SQL*Loader コマンド $ export ORACLE_SID=orcl $ sqlldr userid=user01/******** control=testtbl.ctl data=testtbl.dat シーケンスを使用する(参考URL) LOAD DATA CHARACTERSET JA16SJIS INTO TABLE T_COMPANY_RAW FIELDS TERMINATED BY , trailing nullcols (ID "SEQ_COMPANY_RAW_ID.NEXTVAL", CORP_TYPE, NAME, ・ ・ FUTURE) 改行があるデータの流し込み load data CHARACTERSET JA16SJIS append continueif last != " into table test_table fields terminated by , enclosed by " ( ID, NAME, VALUE, TESTDATE DATE "YYYYMMDD") 日付データの流し込み load data CHARACTERSET JA16SJIS append into table t_answers fields terminated by , trailing nullcols ( id, campaign_id, user_id, registered "to_date( registered, YYYY/MM/DD HH24 MI SS )" ) 「データ・ファイルのフィールドが最大長を超えています。」 load data CHARACTERSET JA16SJIS append into table t_encourage fields terminated by , ( ID, CAT2_ID, MAILBODY char(4000)) Oracle 10g R10.2.0.1 インストール (MiracleLinuxV3.0) memoRPM を更新してから Oracle をインストールする DHCP を使用している場合、一時的にTCP/IPの設定で固定IPアドレスを割り当ててから、Oracle をインストールする ダウンロードOracle 10g R10.2.0.1 をダウンロードする rootユーザでログインする ランレベルを 5 に変更する # init 5 oranavi を起動する # oranavi Oracle DBMS のインストール全てデフォルト設定でインストール Oracle 9i R9.2.0.4.0 インストール後に必要な作業 Run the following commands and scripts in order from $ORACLE_HOME in migrate mode on all updated databases. If you are using the OLAP option, then ensure the database listener is up. $ sqlplus "/ as sysdba" startup migrate spool patch.log @?/rdbms/admin/catpatch.sql shutdon immediate startup This step is optional, and will recompile all invalid PL/SQL packages now instead of when the packages are accessed the first time. The utlrcmp.sql script can be used to parallelize this in multiprocessor machines. @?/rdbms/admin/utlrp.sql Enter the following commands if using Oracle OLAP option alter user olapsys identified by password account unlock; connect olapsys/password @?/cwmlite/admin/postamd.sql ORA-06512 → 参考URL 現象 ORA-00604 error occurred at recursive SQL level 1 ORA-06521 PL/SQL Error mapping function ORA-06512 at "SYS.OLAPIHISTORYRETENTION", line 1 ORA-06512 at line 6 原因 BUG 3386542 処置 (metalink Note 266728.1) Disable OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER to avoid error from being generated. ALTER TRIGGER OLAPISTARTUPTRIGGER DISABLE; ALTER TRIGGER OLAPISHUTDOWNTRIGGER DISABLE; rmanでデータベースを複製する 前提条件Miracle Linux V3.0 Oracle 10g Standard Edition ターゲット・データベースへ接続できるようにする $ vi /opt/app/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora ACCOUNT1.DB01.ORACLE.CO.JP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = account1.db01.oracle.co.jp) (SERVER = DEDICATED) ) ) ACCOUNT1.RESEARCH01.ORACLE.CO.JP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = account1.oracle.co.jp) (SERVER = DEDICATED) ) ) $ vi /opt/app/oracle/product/10.1.0/db_1/network/admin/listener.ora (SID_DESC = (GLOBAL_DBNAME = account1.db01.oracle.co.jp) (ORACLE_HOME = /opt/app/oracle/product/10.1.0/db_1) (SID_NAME = account1) ) $ sqlplus scott/tiger@account1.db02.oracle.co.jp ターゲット・データベースのバックアップ (複製側) # su - oracle $ mkdir -p /opt/app/oracle/oradata/account1/backups ← ターゲットデータベース側・複製側の両方で作成する $ export ORACLE_SID=orcl $ sqlplus /nolog conn / as sysdba create user rman identified by rman temporary tablespace temp default tablespace users quota unlimited on users; grant recovery_catalog_owner, connect, resource to rman; $ rman catalog rman/rman@orcl connect target sys/manager@account1.db01.oracle.co.jp; create catalog; register database; configure retention policy to redundancy = 1; configure controlfile autobackup on; configure controlfile autobackup format for device type disk to /opt/app/oracle/oradata/account1/backups/%F ; backup database format /opt/app/oracle/oradata/account1/backups/full_%d_%T_s%s_p%p ; backup archivelog all delete all input format /opt/app/oracle/oradata/account1/backups/arc_%d_%T_s%s_p%p ; 補助データベースを作成する $ orapwd file=/opt/app/oracle/product/10.1.0/db_1/dbs/orapwaccount1 password=manager entries=30 force=y alter system set remote_login_passwordfile= EXCLUSIVE scope=spfile; shutdown immediate startup force nomount show parameters remote_login_passwordfile; $ cd /opt/app/oracle/admin/account1/ $ mkdir pfile bdump cdump create udump $ cd /opt/app/oracle/admin/account1/pfile $ vi initaccount1.ora DB_NAME=account1 CONTROL_FILES=(/opt/app/oracle/oradata/account1/control01.ctl, /opt/app/oracle/oradata/account1/control02.ctl, /opt/app/oracle/oradata/account1/control03.ctl) $ export ORACLE_SID=account1 $ sqlplus "/ as sysdba" create spfile from pfile= /opt/app/oracle/admin/account1/pfile/initaccount1.ora ; startup force nomount $ lsnrctl start $ rman target sys/manager@account1.db01.oracle.co.jp auxiliary sys/manager@account1.db02.oracle.co.jp duplicate target database to account1 nofilenamecheck; duplicate target database for standby nofilenamecheck; フラッシュバックドロップ ごみ箱の中を見る。 SELECT original_name, type, ts_name, droptime, dropscn, object_name FROM user_recyclebin; テーブルをリストアする。 flashback table test to before drop; インデックスをリストアするテーブルをリストアすると、インデックスや制約も復活するが、名前はごみ箱の中の時のままなので、手動でリネームする。 select index_name from user_indexes where table_name = TEST ; alter index "BIN$ExUXY+Nfh9zgQAB/AQAI6g==$0" rename to PK_TEST; ごみ箱を空にする。 purge dba_recyclebin; purge user_recyclebin; purge tablespace user01 user test; purge table test; purge index pk_test; ごみ箱を介さずに削除する。 drop table test purge; drop user scott cascade; drop tablespace user01 including contents; フラッシュバックデータベースを有効にする DB_RECOVERY_FILE_DEST、DB_RECOVERY_FILE_DEST_SIZE が設定されているか確認する。 select name,value from v$parameter where name like db_recovery_file_dest% ; 設定されていなければ、設定する。 alter system set db_recovery_file_dest= /opt/app/oracle/flash_recovery_area scope =both; alter system set db_recovery_file_dest_size = 2147483648 scope = both; アーカイブログモードにする。 shutdown immediate startup mount alter database archivelog; alter database open; archive log list; フラッシュバックログの保存期間を確認する。 select name, value from v$parameter where name = db_flashback_retention_target ; フラッシュバックデータベース機能を有効化する。 shutdown immediate startup mount alter database flashback on; alter database open select log_mode, flashback_on from v$database; どの時点までフラッシュバックできるか確認する。 select flashback_size/1024/1024 "SIZE(M)", estimated_flashback_size/1024/1024 "EST_SIZE(M)", oldest_flashback_scn, to_char(oldest_flashback_time, YYYY-MM-DD HH24 MI SS ) from v$flashback_database_log; フラッシュバックデータの書き込みサイズを確認する。 select to_char(begin_time, HH24 ) hour, flashback_data/1024 "FLASHBACK_DATA(K)", redo_data/1024 "REDO_DATA(K)" from v$flashback_database_stat; フラッシュバックデータベースを実行する SQL shutdwon immediate SQL startup mount SQL flashback database to scn 608964; SQL flashback database to time "to_timestamp( 06-05-06 22 41 00.059098 )"; RMAN flashback database to scn 608964; RMAN flashback database to time "to_timestamp( 06-05-06 22 41 00.059098 )"; RMAN flashback database to sequence 1 thread 1; データベースをオープンする。 shutdown immediate startup mount alter databse open read only; -- 読み取り専用でオープンする場合 alter database open resetlogs; -- 読み書き可能でオープンする場合 フラッシュバックログから表領域を除外する alter tablespace example flashback off; select name, flashback_on from v$tablespace; ブロック破損 ブロック破損の診断 $ dbv file=/opt/app/oracle/oradata/orcl/system01.dbf SQL analyze table test validate structure cascade; SQL analyze index pk_test validate structure; rman で物理破損ブロックを検出する。 RMAN backup validate datafile 1; SQL select * from v$database_block_corruption; rman で論理破損ブロックを検出する。 RMAN backup check logical datafile 1; rman で物理破損チェックを行わずにバックアップを行う。 RMAN backup nochecksum datafile 1; 破損ブロックを含んだままバックアップをとる。 RMAN run { set maxcorrupt for datafile /opt/app/oracle/oradata/orcl/system01.dbf to 2; backup datafile /opt/app/oracle/oradata/orcl/system01.dbf ; } rman によるブロックメディアリカバリ RMAN blockrecover datafile 1 block 1; RMAN blockrecover corruption list; 簡単なジョブの作成 ジョブを作成する $ export ORACLE_SID=rmanrep $ sqlplus "/ as sysdba" BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name = SYS.BACKUP , job_type = EXECUTABLE , job_action = /home/oracle/devel/shell/backup.sh , start_date = SYSTIMESTAMP, repeat_interval = FREQ=DAILY;BYHOUR=5;BYMINUTE=0 , enabled = TRUE, auto_drop = FALSE, comments = ORCLデータベースのバックアップ ); END; / ジョブを削除する BEGIN DBMS_SCHEDULER.DROP_JOB(job_name = sys.backup ); END; / プログラムとスケジュールを使用したジョブの作成 SCOTTユーザに CREATE JOB 権限を与える $ export ORACLE_SID=orcl $ sqlplus "/ as sysdba" GRANT CREATE JOB TO SCOTT; CONNECT SCOTT/TIGER; ストアドプロシージャの作成 CREATE OR REPLACE PROCEDURE test_del( p_id test.id%TYPE) AS BEGIN DELETE FROM test WHERE ID = p_id; END test_del; / プログラムの作成 BEGIN -- プログラムの作成 DBMS_SCHEDULER.CREATE_PROGRAM( program_name = SCOTT.TEST_PROGRAM , program_type = STORED_PROCEDURE , program_action = SCOTT.TEST_DEL , number_of_arguments = 1, enabled = FALSE, comments = TEST表のDELETE ); -- プログラム引数の定義 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT( program_name = SCOTT.TEST_PROGRAM , argument_name = P_ID , argument_position = 1, argument_type = NUMBER ); -- プログラムの有効化 DBMS_SCHEDULER.ENABLE( SCOTT.TEST_PROGRAM ); END; / スケジュールの作成 BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name = SCOTT.MONTHLY_SCHEDULE , start_date = SYSTIMESTAMP, repeat_interval = FREQ=MONTHLY;BYMONTHDAY=15 , comments = 毎月15日実行 ); END; / プログラムとスケジュールを利用したジョブの作成 BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name = SCOTT.TEST_JOB , program_name = SCOTT.TEST_PROGRAM , schedule_name = SCOTT.MONTHLY_SCHEDULE , enabled = FALSE, auto_drop = FALSE, comments = TEST表のDELETE ); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name = SCOTT.TEST_JOB , argument_name = P_ID , argument_value = 10 ); DBMS_SCHEDULER.ENABLE( SCOTT.TEST_JOB ); END; / PL/SQL PL/SQL実行後、エラーを表示する。 SHOW ERROR DBMS_OUTPUT.PUT_LINE などでの画面出力を有効にする。 SET SERVEROUTPUT ON
https://w.atwiki.jp/monkocho_0514/pages/18.html
マルチテナント ・CDBレベルで管理されるリソース(全DBで共有) ルートコンテナ(CDB$ROOT) ・SYSTEM表領域 ・SYSAUX表領域 ・UNDO表領域 ・REDOログファイル ・制御ファイル シードPDB(PDB$SEED) ・SYSTEM表領域 ・SYSAUX表領域 CDBレベルで管理される操作 PDBで実行可能な操作 マルチテナントアーキテクチャの利点 ・CDBの作成方法の特徴 SQLでCDBを作成する場合 1. enable_pluggable_database パラメータをTRUEに設定したインスタンスを起動 2. ENABLE PLUGGABLE DATABASE句を指定したCREATE DATABASE文を実行 $ cat initcdb3.ora db_create_file_dest= /u01/app/oracle/oradata db_name=cdb3 enable_pluggable_database=true $ export ORACLE_SID=cdb3 $ sqlplus / as sysdba SQL STARTUP MOUNT SQL CREATE DATABASE cdb3 ENABLE PLUGGABLE DATABASE 2 SEED SYSTEM DATAFILES SIZE 150M 3 SYSAUX DATAFILES SIZE 100M; SQL ( catalog.sqlなど実行) ・シードPDBのファイル配置を制御する要素 マルチテナント環境のデータディクショナリビュー CON_ID列(コンテナ番号)にはどんなものがあるか シードPDBのみ表領域を追加する方法 PDBを作成する方法 シードPDBからPDBを作成するとどうなるか 既存PDBをクローニングする場合の注意点 非CDBからPDBの作成はどのように行われるか 接続(プラグ)の条件 PDBの切断(UNPLUG)の注意点 PDBの削除(DROP)の注意点 CDBとPDBへの接続の特徴 PDBにサービスを追加するには PDB名を変更するには PDBの起動、停止方法 PDBの起動の特徴 PDBの自動起動の方法 PDBの停止の特徴 マルチテナントの初期化パラメータ変更はどこに保存されるか PDBでSCOPE=PFILEでパラメータを変更した場合の反映のされ方 PDBに接続してUNDO表領域を作成するとどうなるか マルチテナントの表領域とデータファイルの特徴 マルチテナントのデフォルト表領域、デフォルト永続表領域の特徴 マルチテナントの一時表領域、デフォルト一時表領域の特徴 マルチテナントの表領域のサイズ制限の方法 マルチテナントの共通ユーザ(C##xxx)の特徴 マルチテナントのローカルユーザの特徴 マルチテナントのCONTAINER=ALL句の特徴 マルチテナントのローカル権限と共通権限の特徴 ALTER SESSION SET CONTAINERによるコンテナ切替えの特徴は何か マルチテナントのOracleメタデータの特徴 CDBのバックアップ PDBのバックアップ マルチテナントの表領域のバックアップ マルチテナントのNOARCHIVELOGモード時のバックアップの注意点 マルチテナントのユーザー管理のバックアップ マルチテナントのインスタンス障害はどのレベルに影響するか マルチテナントの一時表領域のリカバリ方法 ルートコンテナのリカバリ方法 PDBの表領域のリカバリ方法 CDBレベルのDBPITR PDBレベルのPDBPITR マルチテナントの表領域レベルのTSPITR CDBのフラッシュバックデータベースはどのように実行されるか フラッシュバックアップ前にデータファイルの移動がある場合はどうなるか
https://w.atwiki.jp/mekagazira/pages/19.html
目次 目次 メモ データベースの種類 ちょいメモ(Bronze SQL基礎I) ちょいメモ(Bronze DBA11g) ちょいメモ(Silver DBA11g) 用語 Oracleのマニュアル 参考ページ メモ set linesize 1000 set pagesize 10000 set pages 10000 line 600 データベースの種類 リレーショナルデータベース・・・データが2次元の表形式をとり、データ間の関連をポインタでなく、データを使って表現する\_( ゚ロ゚)ここ重要 階層型・・・ツリー上の構造をとり、ポインタによってデータが結合されている。データのアクセスには、親データから辿って行かなければならない。つまりデータのアクセスは、親データが入り口となる ネットワーク型・・・ポインタを相互に組み込んで、網の目のような構造をとる。従属データは複数の親データを持つことができ、どのデータもアクセスの入り口となる事が出来る ちょいメモ(Bronze SQL基礎I) リテラル文字列は、一重引用符で囲む(ただし数値の場合は、囲む必要はない) 列別名の大文字小文字をくべつするときは一重引用符ではなく、二重引用符で囲みます。 「BETWEEN a AND b」は、「列 = a AND 列 = b」と同じ結果になり、上限と下限を含む。 「a NOT BETWEEN x AND y」は、「a x OR a y」 NOT LIKE条件では、NULL値のデータ検索は出来ない。 NVL ( expr1 , expr2 ) ・・・式 expr1 が NULL なら expr2 の値を戻す。Null Value Logic の略 グループ関数を使用した場合、SELECT句のグループ関数でない列はすべてGROUP BY句に指定する必要がある GROUP BY 句 がない場合の AVG(集計関数) 一般的にはグループ単位の平均を求める。⇒ GROUP BY 句 を使用していない場合には選択による結果セット(条件によって選択されたレコード) を1つのグループとして全体の平均を1レコード戻す。 列別名はデフォルトでは大文字で表現されます。大文字/小文字を区別したり、スペースや特殊記号(#や$など)を使用したりする場合は、二重引用符(")で列別名の前後を囲む必要があります LIKE演算子を使用してパターン検索を行う際に%や_をエスケープするために、ESCAPEオプションを使用して任意の文字をエスケープ文字として指定する。 SELECT ename FROM emp WHERE ename LIKE M\_% ESCAPE \ ; 日付の加算/減算 日付は数値の加算と減算のように 日付値に +1 することで 1日後、-1 することで 1日前のように加減算できる。 1秒後の求めるには 1/24/60/60 のように 1/86400 日を加算することで、1分後も同様に 1/24/60 を加算、1時間後も 1/24 を加算することで求めることができる。 日付を小数にして加減算する場合には理解しやすくするために datetime + 1/24/60/60 や datetime + 1/(24*60*60) のような表記をすることが多い。 select to_date( 2005/03/12 21 25 06 , YYYY/MM/DD HH24 MI SS ) + 1/24 from dual 単一行関数のネストには制限がないが、グループ関数のネストは2つまでである。 MAX関数は、文字データに使用した場合、アルファベット順に並べたとき最後に来る値を返す。なお、NULL値は無視される。 coalesce関数 (コウアレス)・・・与えられた引数のうち、NULLでない最初の引数を返す。 CASE式はANSI SQLに準拠、DECODE関数はOracle独自 指定した文字を取り除くには「TRIM( H from Hello World )」のように記述します。 TRIM( ABC )→ABC TRIM( x FROM xxABCxx )→ABC TRIM(LEADING x FROM xxABCxx )→ABCxx TRIM(TRAILING x FROM xxABCxx )→xxABC 日時書式要素の接尾辞…日時書式要素に付加できる接尾辞 TH 序数 DDTH 4TH SP フルスペルで表した数 DDSP FOUR SPTHまたはTHSP フルスペルで表した序数 DDSPTH FOURTH データ型CHARは、固定長の文字データ。サイズの指定を省略すると1バイト データ型VARCHAR2は、可変長の文字データ。サイズの指定を省略出来ない。サイズ指定必須。 データ型NUMBERは、精度と位取りのどちらも指定しない場合は、最大制度38の浮動小数となる。 列別名は、WHERE句では使用できない。SELECT句よりもWHERE句のほうが先に評価されるから ORDER BY句では、列別名を使用することが出来るが、GROUP BY句では列別名を使用することはできない。 GROUP BY句、ORDER BY句を併用する場合 通常、order by句は、select句で指定していない列にも使用可能でるが、group by句と併用する場合は、group by句で指定したいづれかの列しかORDER BY句に指定できない。 HAVING句は、GROUP BY句の前後どちらに記述してもOK 句を指定する順番 SELECT句 FROM句 WHERE句 GROUP BY句(HAVING句) HAVING句(GROUP BY句) ORDER BY句 SELECT句で指定していない列をHAVING句で条件付けすることも可能 HAVING句は、GROUP BY句がなくても記述する事が可能 FROM句で表別名を指定した場合、SELECT句WHERE句ON9では、その別名を使う必要がある。 SELECT句、HAVING句、ORDER BY句には、GROUP BY句で使用した列またはグループ関数が使用できる。(WHERE句では使えない。) USING句に指定した列は、そのSELECT文内のどこでも表名で修飾することができない。 自然結合の場合、結合列は、表名または別名で修飾してはいけない。(WHERE句でも列修飾は不要。FROM句でして指定した列と判断される) クロス結合の場合、両方の表に存在する列は、表名または表別名にて修飾する必要がある。 SEQUENCEに、CYCLEが設定されている場合、最大値に達した後は、MINVALUEの値から始まる。START WITHの値は関係ない!! 制約とデフォルトの書式 制約は定義の方法により、列制約と表制約に分類されます。 列制約は列のデータ型と同様に列の定義の中で制約を指定する方法です。表制約は列の定義とは別の場所で、表に対して制約を行います。 この2つには次のような違いがあります。 複数の列を指定する制約は、表制約でないと定義できない。 NOT NULL制約は列制約でないと定義できない。 NOT NULL制約 列制約 列名 データ型 [CONSTRAINT 制約名] NOT NULL 一意キー制約 列制約 列名 データ型 [CONSTRAINT 制約名] UNIQUE 表制約 [CONSTRAINT 制約名] UNIQUE (列名 [, 列名]...) 主キー制約 列制約 列名 データ型 [CONSTRAINT 制約名] PRIMARY KEY 表制約 [CONSTRAINT 制約名] PRIMARY KEY (列名 [, 列名]...) チェック制約 列制約 列名 データ型 [CONSTRAINT 制約名] CHECK (条件) 表制約 [CONSTRAINT 制約名] CHECK (条件) 参照整合性制約 列制約 列名 データ型 [CONSTRAINT 制約名] REFERENCES 親表名 (親キー列名) 表制約 [CONSTRAINT 制約名] FOREIGN KEY (外部キー列名 [, 外部キー列名]...)REFERENCES 親表名 (親キー列名 [, 親キー列名]...) デフォルト 列 列名 データ型 DEFAULT 値 曜日の表示を行う日付書式要素 D 曜日(1~7)。 DY 曜日の省略形。 DAY 曜日。空白を埋め込んで、この要素に使用される日付言語の一番長い曜日の幅で表示します。 月の表示を行う日付書式要素 月 MM 月を 2 桁であらわす (00 ~ 12) MON 月を省略名であらわす( 1月 ~ 12月 )。NLS_DATE_LANGUAGE などの初期化パラメータにより ( JAN ~ DEC ) に変化する。 最大桁数でフォーマットされる(後続スペースの付与)。 MONTH 月をフルスペルであらわす。日本語では関係なし( 1月 ~ 12月 ) ちょいメモ(Bronze DBA11g) データベース管理ツール Oracle Universal Installer(OUI) Database Configuration Assistant(DBCA) Database Upgrade Assistant(DBUA) Oracle Net Manager Oracle Enterprise Manager SQL*PlusとiSQL*Plus Recovery Manager(RMAN) Data Pump SQL*Loader 読み方 data pump データポンプ CREATE USERは、データ定義言語(DDL) Oracleインスタンスは、「システムグローバル領域(SGA)」と「バックグラウンドプロセス」から構成される。 プログラムグローバル領域(PGA)は、サーバプロセスに対して割り当てられる共有されないメモリ Oracleデータベースを構成するファイルは、「制御ファイル」、「データファイル」、「REDOログファイル」の3種類 インスタンスとは、「バックグラウンドプロセス群」+「共有メモリ」。イメージ的には、データベースを管理しているもの(プロセス+メモリ) 「NOMOUNT」状態は、インスタンスが機動した状態。 Oracle Universal Installer(OUI)を用いて、非対話形式のインストールを行う場合は、レスポンスファイルを使用する。 Linux環境で、Oracleをインストールする為には、以下のユーザ・グループが必要である。 Oracleユーザ・・・Oracleソフトウェアの所有者 oinstallグループ・・・Oracleイベントリグループ dbaグループ・・・OSDBAグループ root.shとorainstRoot.shスクリプトを、Oracleインストール時に実行する必要あり。 Oracle Enterprise Manager Database Control(Batabase Control または EM) Oracle Enterprise Manager Database Control(Batabase Control または EM)に初めからアクセス許可されているユーザ SYS・・・データディクショナリを構成する実表とビューを所有している SYSTEM・・・管理情報を格納する追加の表とビューおよび様々なOracleオプションとツールによって使用される内部表とビューを所有します。 SYSMAN・・・Oracle Enterprise Managerを使用して、データベースを操作するために使用します。 ★DBSNMPユーザは、EMへログインは可能。管理者権限は付与されていない。 Oracle Enterprise Manager Database Control(Batabase Control または EM)にログインして管理タスクを実行できるユーザを作成すると、以下のロールが付与される。 MGMT_USERロール クライアントが接続するOracleデータベース情報を解決する方法をネーミングメソッドと呼ぶ。 ディレクトリネーミング 名前解決にディレクトリサーバを使用する。名前解決情報を1箇所に集中させるため、ネットワーク構成の変更に柔軟。 LDAP(Lightweight Directory Access Protocol)はディレクトリサービスを提供するプロトコル。 外部ネーミング Network Information Service(NIS)などのOracle以外のネーミングサービスを使って名前解決解決する。 REDOログバッファは、データベース起動中にサイズを変更できません。 インスタンス起動時に読み込むファイルが、パラメータファイル ●サーバパラメータファイル(SPFILE) Oracleサーバによって読み書きが可能なバイナリーファイル。データベース起動中にコマンドで変更したパラメータ情報をほじするため、再起動時にも変更が有効となる。 手動では編集不可。テキスト初期化パラメータに優先して読み込まれる。 ●テキスト初期化パラメータファイル パラメータを記述したテキストファイル。Oracleサーバは読み込みは出来るが、書き込み不可。 手動では編集可能。編集した内容を反映させるには、データベースの再起動が必要。 自動共有メモリ管理では、SGA_TAGETパラメータで指定したサイズを元に、自動的に以下の領域に対してメモリを分配する。 共有プール データーベースバッファキャッシュ Javaプール ラージプール ※REDOログバッファは、自動共有メモリ管理の対象ではない。 LOG_BUFFERパラメータより、個別にサイズ調整する。 データーベースを起動するために必要な権限 SYSDBA SYSOPER ストライピング 1つのデータを2つ以上のハードディスクに分けて同時に書き込むこと。 ちょいメモ(Silver DBA11g) 制御ファイルの名前を確認する方法 V$CONTROLFILE ビュー show parameter control_filesコマンド 初期化パラメータファイル(CONTROL_FILES)で指定できる制御ファイルの数 1~8つのファイル名 初期化パラメータファイル(LOG_ARCHIVE_DEST_n)で指定できるアーカイブログファイル出力先 1~10つのファイル名 ブロック ファイルとメモリ間のI/Oの最小単位。SYSTEM表領域で使用するブロックサイズを標準ブロックサイズという。 標準以外のブロックサイズを使用する表領域も作成可能だが、標準ブロックサイズの変更は不可。 2KB、4KB、8KB、16KB、32KBのブロックサイズが指定できる。 エクステント オブジェクトの作成時に、表領域中にそのオブジェクト用に獲得する領域の最小単位。 エクステントは、複数のデータファイルにまたがることは出来ない。 1つの表領域内では、同一のブロックサイズでなければならない。 データディクショナリビューの一覧を取得する。 SELECT TABLE_NAME FROM DICTIONARY; Management Service Grid Control用のユーザインターフェースを提供するJ2EEWEBアプリケーションのこと。 ALTER SYSTEM SET ~ コマンド 初期化パラメータ を SQL から変更するコマンド ALTER SYSTEM SET には、 稼動しているインスタンス設定のみを変更する MEMORY スコープ 設定ファイルのみを変更する SPFILE スコープ その両方を設定する BOTH スコープがある。 ALTER SESSION文 データベースへの接続に影響するすべての条件またはパラメータを、設定または変更できます。 この文は、データベースとの接続を切断するまで有効です。 DBA_TABLESPACES データベース内の表領域をすべて表示 用語 リテラル文字・・・SELECT句に含まれた「文字、数字、日付」のこと。文字と日付のリテラルは、単一引用符( )で囲む必要がある Oracleのマニュアル ここから取ってきた リリース1とリリース2のものが混ざってます。 SQL リファレンス エラー・メッセージ アプリケーション開発者ガイド パフォーマンス・チューニング・ガイド データ・ウェアハウス・ガイド PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス グローバリゼーション・サポート・ガイド 概要(Oracle Database 概要マニュアル) 管理者ガイド リファレンス ユーティリティ 参考ページ ORACLE MASTER Bronze DBA講座(1) オラクル基本の落とし穴
https://w.atwiki.jp/kamiyoc/pages/18.html
設定確認 その他確認 設定変更 character_set ユーザー作成 DB・テーブル確認 DB作成 テーブル作成・編集 テーブルバックアップ・復元 ファイル出力 ログ出力 設定確認 C \ProgramData\MySQL\my.ini mysql show variables; mysql show variables like innodb_% ; mysql show variables like %mem% ; mysql show variables like %datadir% ; mysql status; SHOW WARNINGS; ポート確認 mysql show variables like port ; 上へ その他確認 処理中プロセス mysql show processlist; 現在の接続しているスレッド数 mysql show status like Threads_connected ; ロック確認 SHOW ENGINE INNODB STATUS; select * from information_schema.INNODB_TRX \G; select * from information_schema.INNODB_LOCKS \G; select * from information_schema.INNODB_LOCK_WAITS\G; SELECT @@GLOBAL.tx_isolation, @@tx_isolation; 大文字小文字の区別 show variables where variable_name= lower_case_table_names ; my.cnf [mysqld] lower_case_table_names = 0 で変更 DBサイズ SELECT table_schema, sum(data_length+index_length) /1024 /1024 AS MB FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC; テーブルサイズ SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, floor((data_length+index_length)/1024/1024) AS all_mb, floor((data_length)/1024/1024) AS data_mb, floor((index_length)/1024/1024) AS index_mb FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC; 上へ 設定変更 # service mysqld restart 再起動後、ログが適用されたら書き込みがないうちにすぐに停止 # service mysqld stop my.cnf でログファイルサイズの書き換え # vi /etc/my.cnf ログファイルの移動(念のため削除ではなくバックアップ) # mv /var/lib/mysql/ib_logfile* /(backupdir)/ mysqld 起動 # service mysqld start ログファイルが指定したサイズで再作成されたことを確認 # ls -l /var/lib/mysql/ib_logfile* ※ innodb_buffer_pool_size は[mysqld]の下に置く 上へ character set set character set charset_name -- 以下を実行したことと同様 set character_set_client = charset_name; set character_set_results = charset_name; set collation_connection = @@collation_database; -- collation_databaseと同じ値に設定する 上へ ユーザー作成 ユーザー確認 SELECT host,user FROM mysql.user; SELECT * FROM mysql.user \G; ユーザー作成 GRANT ALL PRIVILEGES ON *.* TO sasuke@localhost IDENTIFIED BY aaa WITH GRANT OPTION; rootのパスワード SET PASSWORD FOR root@localhost=PASSWORD( hoge ); ユーザー削除 DELETE FROM mysql.user WHERE user= sasuke ; 上へ DB・テーブル確認 show databases; show tables; SHOW CREATE TABLE tbl_name ; show procedure status; show function status; show create procedure プロシージャ名; select table_schema,table_name,table_type,table_rows,table_collation from information_schema.TABLES; カラム確認 select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_SET_NAME ,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT from information_schema.COLUMNS; ビュー確認 SELECT * FROM information_schema.views WHERE TABLE_NAME = ビュー名 ストアド確認 SHOW CREATE FUNCTION test1 \G SHOW CREATE PROCEDURE test1 \G SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = PROCEDURE ; 上へ DB・テーブル作成 DB作成 mysql CREATE DATABASE データベース名 CHARACTER SET utf8; DB削除 mysql DROP DATABASE test_db1; 上へ テーブル作成・編集 テーブル削除 DROP TABLE IF EXISTS `testdb`.`createtest`; テーブル追加 CREATE TABLE `testdb`.`createtest` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`userid`), KEY `username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; テーブル編集 ALTER TABLE tbl_name ADD COLUMN col_name INT(11) DEFAULT 0 NOT NULL AFTER other_col_name; ALTER TABLE tbl_name CHANGE COLUMN old_col new_col INT(11) NOT NULL AUTO_INCREMENT; ALTER TABLE tbl_name MODIFY COLUMN col_name VARCHAR(128) NOT NULL; ALTER TABLE tbl_name DROP COLUMN col_name; alter table 対象テーブル add unique (対象フィールド); 上へ テーブルバックアップ・復元 バックアップ mysqldump -u napzak -p foo_db db_backup_`date +%Y%m%d-%H%M%S`.sql ストアドも mysqldump -R -u napzak -p foo_db db_backup.sql データのみダンプ mysqldump -u root -p DB名 --skip-dump-date --no-create-info --default-character-set=binary onlyData.sql スキーマとViewのみダンプ mysqldump -u root -p DB名 --no-data --skip-dump-date onlySchemaWithView.sql ストアドのみダンプ mysqldump -u root -p DB名 --skip-dump-date --no-create-info --no-data --routines onlyFunctions.sql テーブル単位 mysqldump -u root -p database_name table_name1 table_name2 dump.sql オプションの説明 --skip-dump-date ダンプした日を出力 --no-create-info create table文を出力しない --no-data データを出力しない --routines ストアド情報を出力 復元 shell mysql -u napzak -p --default-character-set=utf8 foo_db dump.sql または mysql source dump.sql 上へ ファイル出力 mysql -uroot -p -e "SELECT * FROM table_name" db_name /tmp/mysql.tsv SELECT * FROM test INTO OUTFILE /var/lib/mysql-files/test.dmp ; --secure-file-priv の権限が必要 上へ ログ出力 SET GLOBAL general_log= on SET GLOBAL log_output= table なんかやる SELECT * FROM mysql.general_log SET GLOBAL general_log= off 上へ ここを編集