Bazy danych « Różności …

Różności …

1 lipca 2019

mysql access denied for user root@localhost

Zaszufladkowany do: MySQL — Jacek @ 08:55
sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

With a single query we are changing the auth_plugin to mysql_native_password and setting the root password to root (feel free to change it in the query)

Now you should be able to login with root. More information can be found in mysql documentation

(exit mysql console with Ctrl + D or by typing exit)

https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost


22 sierpnia 2016

PostgreSQL

Zaszufladkowany do: Bazy danych,PostgreSQL — Tagi: — Jacek @ 10:44

https://help.ubuntu.com/community/PostgreSQL

19 kwietnia 2016

Rollback

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 09:08

What will happen to rollback session if database shutdown immediate/abort?

Na to pytanie udzielono już odpowiedzi.

bk75 Newbie

bk75 2009-11-28 15:58

I have a long running rollback session after i killed it. The status of session is ‘KILLED’ and when I check the v$transaction, it will take almost 24hours to complete rollback. We cannot wait for so long because we have other dependencies job need the rollback to be completed faster. At the end, we plan to shutdown immediate. Will this help? What will happen to the rollback session if we issue shutdown immediate or shutdown abort? Will the rollback completed successfully?

Hemant K Chitale

Prawidłowa odpowiedź przez Hemant K Chitale dnia 2009-11-28 16:15
If you SHUTDOWN ABORT when there are very many active transactions or transactions being rolled back, then the default FAST_START_PARALLEL_ROLLBACK that occurs on the subsequent Instance Startup will actually mean that the Rollback is executed faster.
However, if it is a single large transaction such as a DELETE against a table with an Index, then it is better to disable FAST_START_PARALLEL_ROLLBACK before restarting the Instance immediately after a SHUTDOWN ABORT. The Parallel Rollback of a single large transaction with an Index can be slower !

FAST_START_PARALLEL_ROLLBACK is an instance parameter which default to LOW (ie a setting of 2 x CPU_COUNT) and can be set to HIGH or FALSE.

Note : If you SHUTDOWN ABORT and STARTUP the database, Oracle only has to do a Rollforward from Redo Logs (for database blocks that haven’t been updated) before it does an OPEN. This is generally a very fast operation. The Rollback of uncomitted transactions is a deferred operation — this means that the Rollback is initiated by SMON or the Parallel Slaves after the Database is OPEN. If a block containing a row that was updated and not committed is referenced by a database session after the OPEN but before it has been rolled back, Oracle does an expedited rollback for that row of the block and then continues doing the rollback of other rows in the background.

Thus, if the transaction had been on Table “A” and the database is SHUTDOWN ABORT and STARTUP, then users can continue accessing and updating all other tables other than Table “A” while the transaction is being rolled back, in the background, after the OPEN.

Hemant K Chitale

Source:
https://community.oracle.com/thread/995750?tstart=0

11 grudnia 2015

oracle em agent unreachable

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 18:20

– To implement the solution, please execute the following steps::
1. Stop the agent
2. Remove all the files from AGENT_HOME/sysman/log/ directory.
3. Backup and edit AGENT_HOME/sysman/config/emd.properties.
4. Add this line:
NMUPM_TIMEOUT=120
5. Uncomment: ThreadPoolModel = LARGE
6. start the agent
7. Monitor the performance of the server

https://community.oracle.com/message/2558818#2558818

10 grudnia 2015

report schema

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 21:25

http://www.dba-oracle.com/t_rman_46_schema_report.htm

9 grudnia 2015

srvctl

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 21:59

https://oracleexamples.wordpress.com/2010/01/22/most-used-srvctl-commands/

What is srvctl? it is the Server Control Utility, we use SRVCTL to start and stop the database and instances, manage configuration information, and to add, move or remove instances and services.

These are some of the srvctl commands I frequently use, this is not a complete reference guide.

I organized the commands as follow:

  • To start a rac database
  • To stop a rac database
  • To check status and configurations
  • To start and stop instances
  • To start, stop and manage services

Start a rac database (order: nodeapps – asm – database)

srvctl start nodeapps -n nodename
srvctl start asm -n nodename
srvctl start database -d dbname
options are: srvctl start database -d dbname -o open | -o mount | -o nomount

Stop a rac database (order: database – asm – nodeapps)
srvctl stop database -d dbname -o immediate
options are: srvctl stop database -d dbname -o normal | -o transactional | -o immediate | -o abort

srvctl stop asm -n nodename
options are: srvctl stop asm -n nodename -o immediate

srvctl stop nodeapps -n nodename

To check status and configurations
Nodeapps:
srvctl status nodeapps -n nodename

srvctl config nodeapps -n nodename

ASM:
srvctl status asm -n nodename
srvctl config asm -n nodename

Database:
srvctl status database -d dbname
srvctl config database -d dbname (shows instances name, node and oracle home)

Instance:
srvctl status instance -d dbname -i instancename

Services:
srvctl status service -d dbname

To start and stop instances

srvctl start instance -d dbname -i instancename
srvctl stop instance -d dbname -i instancename

To start, stop and manage services
srvctl status service -d dbname
srvctl config service -d dbname
srvctl start service -d dbname -s servicename
srvctl stop service -d dbname -s servicename

srvctl relocate service -d dbname -s servicename -i instancename -t newinstancename [-f]

11 grudnia 2014

grant select on all tables

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 16:58

You could, of course, leverage dynamic SQL, i.e.

FOR x IN (SELECT * FROM user_tables)
LOOP
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<someone>>';
END LOOP;

Of course, if you’re doing this sort of thing, you’d normally grant the privileges to a role and grant that role to one or more users.

Login as your user name and then run the following

declare
cursor c1 is select table_name from user_tables;
cmd varchar2(200);
begin
for c in c1 loop
cmd := ‘GRANT SELECT ON ‘||c.table_name||’ TO YOURUSERNAME’;
execute immediate cmd;
end loop;
end;

22 listopada 2014

Error Opening RPD Online: [nQSError: 46036] Internal Assertion

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 13:12

http://bidirect.blogspot.com/2013/07/error-opening-rpd-online-nqserror-46036.html

6 listopada 2014

Oracle BI

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 10:18

http://www.oracle.com/us/solutions/business-analytics/business-intelligence/overview/index.html

http://www.oracle.com/technetwork/middleware/bi-foundation/overview/index.html?ssSourceSiteId=ocomen

10 października 2014

ora-00604 timezone region not found sql developer

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 07:06

check timezone in your database

add following entry do sqldeveloper.conf with appropriate timezone name

AddVMOption -Duser.timezone=America/Santiago

restart sqldeveloper

VOILA!

11 sierpnia 2014

APEX upgrade

Zaszufladkowany do: ApEx,Bazy danych,Oracle,Programowanie — Tagi: , — Jacek @ 13:20

http://infotechinspiration.blogspot.com/2010/07/upgrade-oracle-apex-40-in-11g-database.html

29 lipca 2014

dbfdump

Zaszufladkowany do: Bazy danych — Jacek @ 08:52

dbfdump.pl –fs “,” mytable.dbf > mytable.csv

21 lipca 2014

datapump expdp impdp

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 09:33

CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS ‘/u01/app/oracle/oradata/’;
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp hr/HR directory=DATADUMP dumpfile=FMC_LOGICAL_VESSELS.DMP remap_schema=VTRACK:HR exclude=index,constraint,grant,trigger

expdp user/pass schemas= directory=dumpdir \
dumpfile=.dmp \
logfile=expdp_.log

impdp user/pass schemas=schema1 directory=dumpdir \
remap_schema=schema1:schema2 \
dumpfile=schema1.dmp \
logfile=impdp_schema2.log

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

http://oradbaeasy.wordpress.com/2009/06/11/importing-and-exporting-oracle-database-using-datapump-utility/

expdp username/password FULL=y INCLUDE=GRANT INCLUDE= INDEX DIRECTORY=dpump_dir1 DUMPFILE=dba.dmp CONTENT=ALL

17 lipca 2014

table to csv

Zaszufladkowany do: Bazy danych,Linux,Oracle,Oracle — Tagi: — Jacek @ 18:19

#!/usr/bin/bash

FILE=”emp.csv”

sqlplus -s scott/tiger@XE <

SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 200
SET FEEDBACK OFF

SPOOL $FILE

SELECT * FROM EMP;

SPOOL OFF
EXIT
EOF

- See more at: http://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html

SET PAGESIZE 50000 => Set this to a much bigger value. This value indicates the number of lines per page. The header line will get printed in every page. In order to avoid this, set it to a bigger value so that the header appears only once.

SET COLSEP “,” => Setting the column separator to “,”. With this setting, the list displayed by the SELECT clause will be comma separated. This is the most important setting of this script.

SET LINESIZE 200 => The number of characters per line. The default is 80 which means after 80 characters, the rest of the content will be in the next line. Set this to a value which is good enough for the entire record to come in a single line.

SET FEEDBACK OFF => When a select query is executed, a statement appears at the prompt, say “25 rows selected”. In order to prevent this from appearing in the CSV file, the feedback is put off.

SPOOL $FILE => Spool command records the session queries and results into the file specified. In other words, this will write the results of the query to the file.

SELECT * FROM EMP => The query which gives the entire table contents of EMP. If only a part of the table is desired, the query can be updated to get the desired result.

SPOOL OFF => To stop writing the contents of the sql session to the file.

- See more at: http://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html#sthash.XLJ9iSpO.dpuf

2 czerwca 2014

Oracle on Solaris 10 : Fixing the ‘ORA-27102: out of memory’ Error

Zaszufladkowany do: Bazy danych,Oracle,Oracle,Solaris — Tagi: , — Jacek @ 21:12

https://blogs.oracle.com/mandalika/entry/oracle_on_solaris_10_fixing

ASM

Zaszufladkowany do: Bazy danych,Oracle,Oracle — Tagi: — Jacek @ 17:48

http://blog.sina.com.cn/s/blog_4ea0bbed0100zd6d.html

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG94156

17 lutego 2014

joins

Zaszufladkowany do: Bazy danych,Oracle,Programowanie,SQL — Tagi: — Jacek @ 06:30

NATURAL JOIN

INNER JOINS

SELECT employee_id, last_name,
department_name
FROM
hr.employees JOIN hr.departments
USING (department_id) ;

SELECT l.city, d.department_name
FROM
locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

SELF JOIN
Sometimes you need to join a table to itself. To find the name of each employee’s manager,
you need to join the EMPLOYEES table to itself, or perform a self-join.

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);

NONEQUIJOINS


To return the unmatched rows, you can use
an OUTER join. An OUTER join returns all rows that satisfy the join condition and also returns
some or all of those rows from one table for which no rows from the other table satisfy the join
condition.
There are three types of OUTER joins:
•LEFT OUTER
•RIGHT OUTER
•FULL OUTER

SELECT e.last_name, e.department_id, d.department_name
FROM
employees e LEFT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the EMPLOYEES table, which is the left table, even if there
is no match in the DEPARTMENTS table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all the rows in the DEPARTMENTS table, which is the table at the right,
even if there is no match in the EMPLOYEES table.

SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;

This query retrieves all rows in the EMPLOYEES table, even if there is no match in the
DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no
match in the EMPLOYEES table.

——— CARTESIAN PRODUCT ————

SELECT last_name, department_name
FROM
employees CROSS JOIN departments ;

13 lutego 2014

group_by

Zaszufladkowany do: Bazy danych,Programowanie,SQL — Tagi: — Jacek @ 06:57

group by

AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

22 stycznia 2014

ORACLE – archive log mode.

Zaszufladkowany do: Bazy danych,Oracle,Oracle — Tagi: — Jacek @ 20:49
SQL> startup mount
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

ORACLE – Renaming and moving files.

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 09:15

http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php

Starsze wpisy »

Strona startowa: www.jaceksen.pl