Database

From wiki
Jump to navigation Jump to search

MySQL

GRANT Syntax

https://dev.mysql.com/doc/refman/5.7/en/grant.html www


Database Privileges

  • Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name.* syntax:
  GRANT ALL ON mydb.* TO 'someuser'@'somehost';
  GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Table Privileges

  • Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:
  GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
  GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

Creating a Database

  • If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:
  CREATE DATABASE menagerie;

CREATE USER Syntax

https://dev.mysql.com/doc/refman/5.7/en/create-user.html

  • Specify the password as cleartext; the default plugin is used:
  CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'mypass';

Set up a cron job to back up your site

 #!/bin/sh
 #Set information specific to your site
 webroot="YOUR WEBROOT"
 db_host="YOUR DB HOST"
 db_user="YOUR DB USERNAME"
 db_password="YOUR DB PASSWORD"
 db_name="YOUR DB NAME"
 #Set the date and name for the backup files
 date=`date '+%F-%H-%M'`
 backupname="backup.$date.tar.gz"
 #Dump the mysql database
 mysqldump -h $db_host -u $db_user --password="$db_password" $db_name > $webroot/db_backup.sql
 #Backup Site
 tar -czpvf $webroot/sitebackup.tar.gz $webroot/web/content/
 #Compress DB and Site backup into one file
 tar --exclude 'sitebackup' --remove-files -czpvf $webroot/$backupname $webroot/sitebackup.tar.gz $webroot/db_backup.sql
 #Upload your files to cloud files.
 #First argument is the location of the backup file, second argument is the name to be used when uploaded
 php $webroot/cloudfiles_backup.php $webroot/$backupname $backupname
 #After your backup has been uploaded, remove the tar ball from the filesystem.
 rm $webroot/$backupname

In the above script, you will need to replace the portions in ALL CAPS with your actual information as follows:

YOUR WEBROOT - This is the absolute path to your files. You can find this path by clicking on Hosting, Cloud Sites, Features, and scrolling down. This will be the “Linux Path” listed there. An example would be: /mnt/target02/123456/www.domain.com (Note, the /web/content is not included in that path)

YOUR DB HOST - Database Host an example would be mysql5-9.wc1 or mysql50-78.wc1.dfw1.stabletransit.com

YOUR DB PASSWORD - the password on this database (note the single quotes and that there is no space between the -p and the single quote)

YOUR DB USER - the database username (e.g.: 12345_username)

YOUR DB NAME - the name of the database you are backing up (e.g.: 12345_database_name). Note that there is no parameter for this option unlike the previous examples (-h -p and -u respectively).

ORACLE

Disable Primary Key

https://www.techonthenet.com/oracle/primary_keys.php

You can disable a primary key in Oracle using the ALTER TABLE statement. Syntax

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

  ALTER TABLE table_name
  DISABLE CONSTRAINT constraint_name;

Enable Primary Key

You can enable a primary key in Oracle using the ALTER TABLE statement. Syntax

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

  ALTER TABLE table_name
  ENABLE CONSTRAINT constraint_name;

FORCE DROP USER

 DECLARE
  open_count integer;
 BEGIN
  -- prevent any further connections
  EXECUTE IMMEDIATE 'alter user @USERNAME account lock';
  --kill all sessions
  FOR session IN (SELECT sid, serial# 
                  FROM  v$session 
                  WHERE username = '@USERNAME')
  LOOP
    -- the most brutal way to kill a session
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
  END LOOP;
  -- killing is done in the background, so we need to wait a bit
  LOOP
    SELECT COUNT(*) 
      INTO open_count 
      FROM  v$session WHERE username = '@USERNAME';
    EXIT WHEN open_count = 0;
    dbms_lock.sleep(0.5);
  END LOOP;
  -- finally, it is safe to issue the drop statement
  EXECUTE IMMEDIATE 'drop user @USERNAME cascade';
 END;

CHANGE SYS PASSWORD

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

If your ID's are identified externally you can sign-on to Oracle without a password and then change the SYS password. Users whose ID's are "identified externally" and participate in the OS DBA group can sign-on to SQL*Plus without a password:

  sqlplus /nolog
  connect / as sysdba
  ALTER USER sys IDENTIFIED BY "new_password";

ORACLE INSTALLATION

Start the Oracle Universal Installer (OUI) by running the runInstaller (UNIX) or setup.exe (Windows) script, specifying the location of a valid Java installation (at least Java 6).

On UNIX systems:

  $ ./runInstaller -jreLoc JAVA_HOME

For example:

  $ ./runInstaller -jreLoc /usr/lang/JAVA/jre1.6.0_20

IF after ssh -X user@server running dbca and fonts not shown change /home/oracle/app/oracle/product/11.2.0/dbhome_1/jdk/jre folder with last jre

On error >>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

 yum install xorg-x11-server-utils
 yum install xorg-x11-xauth
 yum install xdpyinfo
 yum install xorg-x11-fonts-*

How to drop all objects in schema

 BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
 END;
 /

Drop all objects by owner

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type,owner
                     FROM all_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             )
                             and owner='&OWNER')
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' '||cur_rec.owner||'."'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' '||cur_rec.owner||'."'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' '||cur_rec.owner||'."'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
 END;
 /

Force drop user in Oracle database

 DECLARE
  open_count integer;
 BEGIN
  -- prevent any further connections
  EXECUTE IMMEDIATE 'alter user @USERNAME account lock';
  --kill all sessions
  FOR session IN (SELECT sid, serial# 
                  FROM  v$session 
                  WHERE username = '@USERNAME')
  LOOP
    -- the most brutal way to kill a session
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
  END LOOP;
  -- killing is done in the background, so we need to wait a bit
  LOOP
    SELECT COUNT(*) 
      INTO open_count 
      FROM  v$session WHERE username = '@USERNAME';
    EXIT WHEN open_count = 0;
    dbms_lock.sleep(0.5);
  END LOOP;
  -- finally, it is safe to issue the drop statement
  EXECUTE IMMEDIATE 'drop user @USERNAME cascade';
 END;

Kill all sessions of a special user

 BEGIN
  FOR r IN (select sid,serial# from v$session where username = 'USERNAME')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 
      || ',' || r.serial# || '''';
  END LOOP;
 END;

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' 
FROM v$session 
where username='YOUR_USER';

Get table name by constraint name

  SELECT owner, table_name
  FROM dba_constraints
  WHERE constraint_name = <<your constraint name>>

will give you the name of the table. If you don't have access to the DBA_CONSTRAINTS view, ALL_CONSTRAINTS or USER_CONSTRAINTS should work as well.

Adding firewall rules for Oracle Database using iptables

To connect to a box on your network that is running Oracle Database, you will first need to allow connections to Oracle through your firewall.

If you’re running CentOS, RHEL, Fedora or any other Linux variant that uses iptables, use the following commands to create a firewall exception (Assuming you’re running your listener on port 1521 - check with sudo lsnrctl status):

 sudo iptables -I INPUT -p tcp --dport 1521 -j ACCEPT

Or to limit the connections to a specific IP address - e.g. 192.168.1.20 or an IP block - e.g. 192.168.1.0/24 use the -s option:

 sudo iptables -I INPUT -s 192.168.1.0/24 -p tcp --dport 1521 -j ACCEPT

Don’t forget to save your changes to make them permanent (still applied after reboot):

 sudo service iptables save

Or

 sudo /etc/init.d/iptables save

Bonus round

Check the full status of all your firewall rules with the following command:

 sudo iptables -L -n -v --line-numbers

Create database link using full tns entry

	
create database link testlink_db2
  connect to system identified by oracle
  using
  '(DESCRIPTION=
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=10.2.10.18)
   (PORT=1525))
   (CONNECT_DATA=
     (SID=test10)))'


Recompile Invalid Objects

	
Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
and
   object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER')
;

spool off;

set heading on;
set feedback on;
set echo on;

Oracle Database 12c: EM Database Express

How can I find the port on which EM Express is configured?

When dbca completes, it indicates the port on which EM Express is configured. If that information is no longer available, there are two ways to find the HTTP/HTTPS port for EM Express: > lsnrctl status | grep HTTP

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xxx.us.oracle.com)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))

or from SQL*Plus:

SQL> select dbms_xdb.getHttpPort() from dual;

GETHTTPPORT


8080

SQL> select dbms_xdb_config.getHttpsPort() from dual;

GETHTTPSPORT


5500

How do I set up EM Express?

DBCA allows you to set up EM Express at the time of database creation.

If you want to manually set up EM Express, simply configure the HTTPS or HTTP port by logging into the database and setting the port:

SQL> exec dbms_xdb_config.sethttpsport(5500);

or

SQL> exec dbms_xdb_config.sethttpport(8080);

If you are using a listener TCP port other than 1521, you will also need to set the dispatchers and local_listener initialization parameters. Please see the documentation for further information.

Who is locking your accounts (ORA-01017 and ORA-28000 errors)

ORA-01017/ORA-28000 without AUDIT_TRAIL

First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):

CREATE TABLE sys.logon_trigger
(
USERNAME VARCHAR2(30),
USERHOST VARCHAR2(128),
TIMESTAMP DATE
);

Second I created below trigger:

CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE);
    COMMIT;
  END IF;
END;
/

Then third simulated a wrong password access with my account and issued:

SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;

REVOKE DBA FROM USER

BEGIN
  DECLARE
    CURSOR dba_user
    IS
      SELECT grantee
      FROM dba_role_privs
      WHERE granted_role='DBA'
      AND grantee NOT  IN ('SYS','SYSTEM');
  BEGIN
    FOR revoke_user IN dba_user
    LOOP
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE DBA FROM '|| revoke_user.grantee;
        EXECUTE IMMEDIATE 'GRANT CREATE session, CREATE table, CREATE view,       
CREATE synonym, CREATE sequence, Create trigger, CREATE PROCEDURE TO '|| revoke_user.grantee;
        EXECUTE IMMEDIATE 'GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE to '|| revoke_user.grantee;
        EXECUTE IMMEDIATE 'GRANT connect TO '|| revoke_user.grantee;
      END;
    END LOOP;
  END;
END;

How do I get a list of locked users in an Oracle database?

SELECT username, 
       account_status
  FROM dba_users;

or

select username,
       account_status 
  from dba_users 
 where lock_date is not null;

SQL* Plus

The buffer can be edited or saved to a file. You can terminate a SQL statement in any of the following ways:

■ End with a semicolon ( ; ): The statement is completed and executed.

■ Enter a slash ( / ) on a new line by itself: The statement in the buffer is executed.

■ Enter a blank line: The statement is saved in the buffer.

You can use the RUN command instead of a slash to execute a statement in the buffer.

If you want to continue a SQL*Plus command onto the next line, you must end the current line with a hyphen ( - ), which indicates command continuation.

One way to edit the SQL*Plus buffer is to use the EDIT command to write the buffer to an operating-system file named afiedt.buf

You can use your favorite text editor by defining it in SQL*Plus. For example, to make Notepad your favorite editor, just issue the command DEFINE _EDITOR = NOTEPAD

LIST

The LIST command lists the contents of the buffer. The asterisk indicates the current line. The abbreviated command for LIST is L .

 SQL> L
 1 SELECT empno, ename
 2* FROM emp
 SQL> LIST LAST
 2* FROM emp


The command LIST n displays line n , and LIST * displays the current line. The command LIST m n displays lines from m through n . If you substitute * for m or n , it implies from or to the current line. The command LIST LAST displays the last line.

APPEND

The APPEND text command adds text to the end of a line. The abbreviated command is A .

 SQL> A WHERE empno <> 7926
 2* FROM emp WHERE empno <> 7926

CHANGE

The CHANGE /old/new command changes an old entry to a new entry. The abbreviated command is C . If you omit new , old will be deleted.

SQL> C /<>/=
2* FROM emp WHERE empno = 7926
SQL> C /7926
2* FROM emp WHERE empno =

The ellipses (...) can be used as wildcard characters. The following example changes everything in the line from “fro” to the new value.

SQL> l
1* select name from v$instance
SQL> c/fro.../from v$database
1* select name from v$database

The next example shows the substitution of a string in the middle of the line using ellipses.


 SQL> l
 1* select owner from dba_tables where table_name like 'HR%'
 SQL> c/dba...table/dba_views where view
 1* select owner from dba_views where views where table_name like 'HR%'


INPUT

The INPUT text command adds a line of text. Its abbreviation is I . If text is omitted, you can add as many lines as you want.

SQL>I
3 7777 AND
4 empno = 4354
SQL> I ORDER BY 1
SQL>L
1 SELECT empno, ename
2 FROM emp WHERE empno =
3 7777 AND
4 empno = 4354
5* ORDER BY 1

DEL

The DEL command used alone or with * deletes the current line. The DEL m n command deletes lines from m through n . If you substitute * for m or n , it implies the current line. The command DEL LAST deletes the last line.

SQL>3
3* 7777 AND
SQL> DEL
SQL> L
1 SELECT empno, ename
2 FROM emp WHERE empno =
3 empno = 4354
4* ORDER BY 1
SQL> DEL 3 *
SQL> L
1 SELECT empno, ename
2* FROM emp WHERE empno =

CLEAR BUFFER

The CLEAR BUFFER command (abbreviated CL BUFF ) clears the buffer. This deletes all lines from the buffer.

SQL> L
1 SELECT empno, ename
2* FROM emp WHERE empno =
SQL> CL BUFF
buffer cleared
SQL> L
No lines in SQL buffer.


Save the buffer to a file named myfile ; the default extension will be .sql :

 SQL> SAVE myfile
 Created file MYFILE.sql

edit the file:

 SQL> EDIT myfile

Bring the file contents to the buffer:

 SQL> GET myfile

Save the file using the REPLACE keyword:

SQL> SAVE myfile REPLACE

Execute the file:

SQL> START myfile

Last Modified Tables

  SELECT TABLE_OWNER, 
   TABLE_NAME, 
   INSERTS,
   UPDATES,
   DELETES,
   TIMESTAMP
FROM  ALL_TAB_MODIFICATIONS
WHERE TABLE_OWNER NOT IN('SYS','SYSMAN') and TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY') 
ORDER BY  TIMESTAMP DESC;

Remove Duplicates

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);
Delete from bol_temp
where rowid in ( select rid
from ( select rowid rid,
row_number() over
(partition by cuo_cod,ide_voy,dat,lin order by rowid) rn
from bol_temp ) where rn <> 1 );

Restore mistakenly deleted Datafiles.

SQL> startup
ORACLE instance started.

Total System Global Area 1720328192 bytes

Fixed Size                  2255904 bytes
Variable Size            1275069408 bytes
Database Buffers          436207616 bytes
Redo Buffers                6795264 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\ORCL\TRASHED01.DBF'


SQL> alter database datafile 6 offline drop;

Database altered.

SQL> alter database open;

Database altered.

Moving Datafiles

SQL> select tablespace_name, file_name from dba_data_files;

Result:

TABLESPACE_NAME      FILE_NAME
----------------------------------------------
SYSTEM	/opt/oracle/oradata/YOUR_SID/system01.dbf
SYSAUX	/opt/oracle/oradata/YOUR_SID/sysaux01.dbf
DB	/opt/oracle/oradata/YOUR_SID/db.dbf
USERS	/opt/oracle/oradata/YOUR_SID/users01.dbf
UNDOTBS1	/opt/oracle/oradata/YOUR_SID/undotbs01.dbf
SQL> alter tablespace YOUR_SID offline;

Tablespace altered.

SQL>

!
$ mv /opt/oracle/oradata/YOUR_SID/db.dbf /u03/oradata/YOUR_SID/db.dbf
$ exit

When in SQL*Plus, you can enter the character ! to temporarily exit to the command line. When you are ready to return to SQL*Plus, type exit at the command line.

exit

SQL> alter tablespace YOUR_SID rename datafile '/opt/oracle/oradata/YOUR_SID/db.dbf' to '/u03/oradata/YOUR_SID/db.dbf';

Tablespace altered.


SQL> alter tablespace USERS online;

Tablespace altered.

RMAN Validate Backup Tips

Then query the v$database_block_corruption view. As we have not validated the datafile, it must be empty:

SQL> 
select * from 
v$database_block_corruption;
no rows selected

Now use the backup validate command to check that datafile for any corruption. Then query the v$database_block_recovery view:

RMAN> backup validate tablespace tbs_test;

SQL> 
select * from 
v$database_block_corruption;

To validate the tablespace, use the following command:

RMAN> backup validate tablespace tbs_test;

To validate a specific datafile, use the following command:

RMAN> backup validate datafile 8;

To check the whole database, use the following command:

RMAN> backup validate database;

To check all archived redo log files, use the following command:

RMAN> backup validate archivelog all;

To check the spfile, use the following command:

RMAN> backup validate spfile;

To check the current control file, use the following command:

RMAN> backup validate current control file;

To check backup of the control file, use the following command:

RMAN> restore control file validate;

To check backup of the spfile, use the following command:

RMAN> restore spfile validate;

To check backup of tablespace users, use the following command:

RMAN> restore tablespace users validate;

To check backup of a datafile, use the following command:

RMAN> restore datafile 4 validate;

To check backup of all archived redo log files, use the following command:

RMAN> restore archivelog all validate;

To check backup of the whole database, use the following command:

RMAN> restore database validate;

How to show all privileges from a user in oracle

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

Create directory and import/export data

select * from ALL_DIRECTORIES
select * from DBA_DIRECTORIES
CREATE OR REPLACE DIRECTORY ctemp AS '/home/temp';
GRANT READ,WRITE ON DIRECTORY ctemp TO @user;

The default directory is DATA_PUMP_DIR
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

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

Finds gaps in Data

select instance_id, next_instance_id
from (
select instance_id, lead(instance_id) over (order by instance_id) next_instance_id
 from BOL
)
where instance_id <> next_instance_id-1;

Unmatched data

SELECT
    *
FROM
    Table2 T2
WHERE
    NOT EXISTS (SELECT *
        FROM
           Table1 T1
        WHERE
           T1.State = T2.State AND
           T1.Product = T2.Product AND
           T1.Distributor = 'X')

Drop all objects of user

SELECT
    'drop '
     || object_type
     || ' '
     || owner
     || '.'
     || object_name
     || DECODE(
        object_type,
        'TABLE',
        ' CASCADE CONSTRAINTS',
        ''
    )
     || ';'
FROM
    all_objects
WHERE
    owner = 'AREN_SO_ET'

How to re-open expired oracle database account without change password

sqlplus / as sysdba
select
'alter user ' || su.name || ' identified by values'
   || ' ''' || spare4 || ';'    || su.password || ''';'
from sys.user$ su 
join dba_users du on ACCOUNT_STATUS like 'EXPIRED%' and su.name = du.username;

ORA-00054: resource busy and acquire with NOWAIT specified

select object_name, s.sid, s.serial#, p.spid 
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

alter system kill session 'sid,serial#';

--Use the SESSION_ID to find the corresponding SERIAL# with this statement:
SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN (
    SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = 'TABLE_NAME'
);
--Locate the offending tuples SID, SERIAL# and release it like this:
ALTER SYSTEM KILL SESSION '985, 59214';

--Find locked objects
select * from v$locked_object where object_id = (select object_id from dba_objects where object_name = 'TABLE_NAME' and owner='USER_NAME')

Modify every VARCHAR2 column type in one table from BYTE to CHAR

DECLARE
  l_sql_stmt varchar2(1000);
BEGIN
  FOR c IN (SELECT *
              FROM user_tab_columns
             WHERE data_type = 'VARCHAR2'
               AND table_name = <<table name>>
               AND char_used = 'B')
  LOOP
    l_sql_stmt := 
       'ALTER TABLE <<table name>> MODIFY( ' || 
          c.column_name || ' VARCHAR2( ' || c.char_length || ' CHAR) )';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;

Difference Between Two Timestamps

 Method #1: use EXTRACT
  extract(day from (x-y))*24*60*60
  + extract(hour from (x-y))*60*60
  + extract(minute from (x-y))*60
  + extract(second from (x-y))

 Method #2: use CAST
  ( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400

The difference? Method #2 is faster (my tests indicate faster by a factor of 3), but does not include fractional seconds. Method #1 is a bit slower, but includes fractions of a second. See the SO link for details.

Rename a set of tables

rename all tables that end up with the string "_ARC" removing that string

begin
  for i in (select t.TABLE_NAME as old_name
                  ,substr (t.TABLE_NAME,1,length(t.TABLE_NAME)-4) as new_name
            from user_tables t
            where t.TABLE_NAME like '%\_ARC' escape '\')
    loop
      execute immediate 'rename '||i.old_name||' to '||i.new_name;
    end loop;
end;

Add Day to Timestamp

SELECT TO_TIMESTAMP('01-jan-2011 11-09-05','DD-Mon-YYYY HH24-MI-SS') + INTERVAL '2' DAY FROM dual;

Replace the space from data in all tables

DECLARE
    v_owner VARCHAR2(255):='SCIMOD';
    v_count NUMBER;
    v_val VARCHAR2(4000);
    j NUMBER:=0;
    CURSOR c_all_columns IS 
    SELECT table_name,column_name
    FROM all_tab_columns
    WHERE owner = v_owner AND  table_name LIKE 'RIMM%' AND DATA_TYPE='VARCHAR2';

BEGIN
FOR v_all_columns IN c_all_columns LOOP


select 
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT  COUNT(*) c  FROM '|| v_owner||'.'||v_all_columns.table_name ||'  WHERE '||  v_all_columns.column_name ||' NOT IN (SELECT regexp_replace( '|| v_all_columns.column_name ||','''||'\s{2,}'||''','|| ' '' ''' ||') FROM '||v_owner||'.'||v_all_columns.table_name ||')')),'/ROWSET/ROW/C')) as cnt
into v_count
from dual;


    IF v_count>0 THEN
    FOR i IN 0..v_count-1 LOOP
    
        
        select 
        extractvalue(xmltype(dbms_xmlgen.getxml('SELECT  '||  v_all_columns.column_name ||' C FROM '|| v_owner||'.'||v_all_columns.table_name ||'  WHERE '||  v_all_columns.column_name ||' NOT IN (SELECT regexp_replace( '|| v_all_columns.column_name ||','''||'\s{2,}'||''','|| ' '' ''' ||') FROM '||v_owner||'.'||v_all_columns.table_name ||')  OFFSET '||i||'  ROWS FETCH NEXT 1 ROWS ONLY')),'/ROWSET/ROW/C') as val
        into v_val
        from dual;

        dbms_output.put_line('UPDATE '||v_owner||'.'||v_all_columns.table_name||' SET '||v_all_columns.column_name||'= regexp_replace( '||v_all_columns.column_name ||','''||'\s{2,}'||''','|| ' '' ''' ||') WHERE '||v_all_columns.column_name ||'='''||v_val||''';');
     
    END LOOP;   
    END IF;


END LOOP;    
END;

DROP all tables starting with “AUDIT_”

BEGIN
  FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'AUDIT_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
  END LOOP;
END;

DISABLE & ENABLE all the triggers in the schema

BEGIN
  FOR i IN (SELECT trigger_name
    FROM user_triggers) LOOP

      EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE';

      EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' ENABLE';

  END LOOP;
END;

Find top tables by size

select
   *
from (
   select
      owner,
      segment_name,
      bytes/1024/1024 meg
   from
      dba_segments
   where
      segment_type = 'TABLE'
   order by
      bytes/1024/1024 desc)
where
   rownum <= 10;


How can I generate (or get) a ddl script on an existing table in oracle?

select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME')
from dual;

You can also do this for all tables at once:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables;

Listing privileges recursively for Oracle users

Users to roles and system privileges

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

System privileges to roles and users

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

Object privileges

select
  case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
  lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
  end
from
  (
  /* THE OBJECTS */
    select 
      null          p1, 
      null          p2,
      object_name   obj,
      owner         own,
      object_type   typ
    from 
      dba_objects
    where
       owner not in 
        ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS', 
         'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
      and object_type not in ('SYNONYM', 'INDEX')
  /* THE OBJECT TO PRIVILEGE RELATIONS */ 
  union
    select
      table_name p1,
      owner      p2,
      grantee,
      grantee,
      privilege
    from
      dba_tab_privs
  /* THE ROLES TO ROLES/USERS RELATIONS */ 
  union
    select 
      granted_role  p1,
      granted_role  p2,
      grantee,
      grantee,
      null
    from
      dba_role_privs
  )
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

grant all tables to user

declare
cursor tab_names is select table_name from user_tables;
begin
for tab in tab_names loop
--Replace USER_NAME with your user name
execute immediate 'GRANT SELECT ON '||tab.table_name||' to <USER_NAME>';
end loop;
end;

grant all views to user

declare
cursor view_names is select view_name from user_views;
begin
for v in view_names loop
--Replace USER_NAME with your user name 
execute immediate 'GRANT SELECT ON '||v.view_name||' to <USER_NAME>';
end loop;
end;

How to get ddl of users and roles?

Use dbms_metadata, read the documentation: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1015856

SYS@orcl > SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2 'ROLE_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
--------------------------------------------------------------------------------
   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2   'OBJECT_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT SELECT ON "HR"."EMPLOYEES" TO "SCOTT"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2  'SYSTEM_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT UNLIMITED TABLESPACE TO "SCOTT"

SYS@orcl >  SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

DBMS_METADATA.GET_DDL('ROLE','RESOURCE')
--------------------------------------------------------------------------------
   CREATE ROLE "RESOURCE"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2  'SYSTEM_GRANT','RESOURCE') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE')
--------------------------------------------------------------------------------
  GRANT CREATE INDEXTYPE TO "RESOURCE"
  GRANT CREATE OPERATOR TO "RESOURCE"
  GRANT CREATE TYPE TO "RESOURCE"
  GRANT CREATE TRIGGER TO "RESOURCE"
  GRANT CREATE PROCEDURE TO "RESOURCE"
  GRANT CREATE SEQUENCE TO "RESOURCE"
  GRANT CREATE CLUSTER TO "RESOURCE"

Query a view of another schema that also has linked tables to another schema

 Oracle prompts
   ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a 
   database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login. *Action: 
   Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator
   to regrant the privilege at the appropriate label. Error at Line: 1 Column: 21

Here's how it works:

You have 3 schemas:

   Schema1 - Holder of a table named "table1"
   Schema2 - Holder of a view "view1" selecting from schema1.table1
   Schema3 - User, selecting from schema2.view1 - has no select granted on schema1.table1.

You probably have done the following:

grant select on schema1.table1 to schema2 with grant option;

Now schema2, is allowed to grant select on its view to 3rd parties:

grant select on schema2.view1 to schema3;

For you:

grant select on COLLDESK.GESTIONED to LOCAL_IT with grant option;
grant select on LOCAL_IT.ACTIONS to GERBEN;

Count each number of table rows

select
   table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
from 
   user_tables
where  table_name like 'RIMM%'  
order by 
   table_name;


Removing duplicate rows from table in Oracle

Use the rowid pseudocolumn.

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.


Last DML Time

declare
    v_count TIMESTAMP;
    strsql VARCHAR2(200);
    tblname VARCHAR2(32);
begin

    for r in (select OBJECT_NAME from all_objects where OBJECT_TYPE='TABLE' 
    and OWNER='SCHEMA' 
    --and OBJECT_NAME LIKE 'T%' 
    ORDER BY OBJECT_NAME) 
    loop
      begin
        tblname := R.OBJECT_NAME;
        strsql := 'select SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM SCHEMA.' || tblname;
        execute immediate strsql into v_count;
        DBMS_OUTPUT.PUT_LINE(tblname || ' ' || v_count);
        EXCEPTION
        when others then 
          null;
         end; 
    end loop;
end;

How To Create SQL Loader Control File Dynamically In Oracle

 SELECT    'LOAD DATA
APPEND INTO TABLE '
       || 'TABLE_NAME'
       || '
FIELDS TERMINATED BY '',''  OPTIONALLY ENCLOSED BY '''
       || CHR (34)
       || '''
TRAILING NULLCOLS ('
  FROM DUAL
UNION ALL
SELECT tab_cols
  FROM (  SELECT column_name || ',' tab_cols
            FROM ALL_TAB_COLS
           WHERE table_name = 'TABLE_NAME' and owner = 'CSV'
        ORDER BY column_id)
UNION ALL
SELECT ')' FROM DUAL

List of Oracle views using specific table name

select 
    * 
from 
    all_dependencies
where 
    type='VIEW'
    and referenced_name like '%table_name%'
    and referenced_type = 'TABLE'

Disable foreign keys

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

delete all the data from all available tables

select 'truncate table ' || table_name || ';' from user_tables

Find MAX(ID) from all tables

declare
      l_id pls_integer;
        max_id pls_integer;
        max_tab_name varchar2(30);
    begin
        max_id := 0;
        for r in ( select TABLE_NAME,OWNER
                   from all_tab_columns
                   where column_name = 'ID' and owner = 'USER_NAME')
       loop
           execute immediate 'select max(ID) from '||r.owner||'.'||r.table_name
               into l_id;
           if l_id > max_id
           then
               max_id := l_id;
               max_tab_name := r.table_name;
           end if;
       end loop;
       dbms_output.put_line('Highest score = '||max_id||' table='||max_tab_name);
   end;

Show all active sessions

SELECT s.sid,
  s.serial#,
  s.username ,
  s.status ,
  SUBSTR(s.machine,1,10) ,
  s.osuser,
  s.module ,
  TO_CHAR(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
  -- idle time
  -- days separately
  ,
  SUBSTR('0'
  ||TRUNC(last_call_et/86400),-2,2)
  || ':'
  ||
  -- hours
  SUBSTR('0'
  ||TRUNC(mod(last_call_et,86400)/3600),-2,2)
  || ':'
  ||
  -- minutes
  SUBSTR('0'
  ||TRUNC(mod(mod(last_call_et,86400),3600)/60),-2,2)
  || ':'
  ||
  --seconds
  SUBSTR('0'
  ||mod(mod(mod(last_call_et,86400),3600),60),-2,2) idle_time
FROM v$session s,
  v$process p
WHERE s.username IS NOT NULL
  -- use outer join to show sniped sessions in
  -- v$session that don't have an OS process
AND p.addr(+) = s.paddr
  --and s.sid=2398
AND status <> 'INACTIVE'
ORDER BY idle_time DESC;


See all tables in another schema

To see all tables in another schema, you need to have one or more of the following system privileges:


SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE

or the big-hammer, the DBA role.

With any of those, you can select:


SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.


SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM ALL_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):


SELECT DISTINCT OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

Show all tables size in schema


SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024  * 1024)) AS GB
  FROM DBA_SEGMENTS DS
  WHERE SEGMENT_NAME IN (SELECT table_name FROM DBA_TABLES where owner = 'SCHEMA' )
  
 GROUP BY DS.TABLESPACE_NAME,
       SEGMENT_NAME
       order by 3 desc;


Make table read write


Select 'ALTER TABLE [Your schema].'||object_name||' READ WRITE;' from all_objects where object_type = 'TABLE' and owner = '[Your schema]'  ;

Bulk create public synonyms


select 'create public synonym ' || synonym_name ||
' for ' || table_owner || '.' || table_name || ';'
from dba_synonyms
where owner = 'PUBLIC'
and table_owner = 'THAT USER';

CPU usage by session


select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc;

Create database link


ALTER SESSION SET global_names=false
CREATE DATABASE LINK DBASYET
  CONNECT TO [schema] IDENTIFIED BY "[password]" USING '(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=[host or IP address])
(PORT=1521))
(CONNECT_DATA=
(SID=[Your SID])))'

Gather oracle statistics


exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
exec DBMS_STATS.GATHER_DICTIONARY_STATS;
exec dbms_stats.gather_database_stats(gather_sys=>TRUE);
exec dbms_stats.gather_schema_stats('[your schema]',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec dbms_stats.set_table_prefs('[your schema]', 'TABLE_NAME', 'DEGREE', DBMS_STATS.AUTO_DEGREE)
exec dbms_stats.gather_table_stats('[your schema]','TABLE_NAME', DEGREE => 32 )

Drop all sequences

BEGIN
  FOR s IN
  (SELECT sequence_name FROM user_sequences
  )
  LOOP
    EXECUTE immediate 'drop sequence ' || s.sequence_name;
  END LOOP;
END;

Drop all tables and views in schema


select 'drop table '||owner||'.'||table_name||' cascade constraints;' from dba_tables where owner='[your schema]';

select 'drop view '||view_name||';' from user_views;

Enable all constaints

SELECT 'ALTER TABLE '
  || OWNER
  || '.'
  || TABLE_NAME
  || ' ENABLE CONSTRAINT '
  || CONSTRAINT_NAME
  || ';' "SQL_CMD"
FROM DBA_CONSTRAINTS
WHERE OWNER='[your schema]';


Finds specified users, unlocks their accounts and resets the passwords

The way Oracle handles password expiration can be quite frustrating at times. To enable an expired user the DBA must basically set the password. Locked users can be unlocked with a simple command, but there is no way to unexpire an expired password. If the password expired by mistake and is used by a system rather than a user, changing it may be a big deal. You need to get things up and running and you need to do it fast. So, what to do?

Fortunately it is possible to set the password to the same value it had before using identified by values and the hashed password. Oracle does not show the hashed password in dba_users, but it can be retrieved.

This code finds specified users, unlocks their accounts and resets the passwords:

begin
  for rec in (select
    regexp_substr( dbms_metadata.get_ddl('USER', username), '''[^'']+''') pw,
    username from dba_users where username IN ('SYSMAN')) loop
    execute immediate 'alter user ' || rec.username || ' account unlock';
    execute immediate 'alter user ' || rec.username || 
      ' identified by values ' || rec.pw;
  end loop;
end;

Export all permissions

set heading off
set feedback off

spool backup_grants.sql
select 'grant '|| privilege || ' on ' || owner|| '.' ||table_name || ' to ' || grantee ||';' as query from dba_tab_privs where 
owner in (''PUT','LIST','OF','SCHEMAS','HERE') OR GRANTEE  IN (''PUT','LIST','OF','SCHEMAS','HERE') ;
spool off

Find invalid objects

select
   owner       c1,
   object_type c3,
   object_name c2
from
   dba_objects
where
   status != 'VALID'
order by
   owner,
   object_type;

Find table with column name

select table_name,column_name,data_type,data_length from all_tab_columns where column_name like '%[searched column name]%' ESCAPE '\'  and owner='[your schema]';

Grand select permission on tables


BEGIN
   FOR R IN (SELECT owner, table_name FROM all_tables    WHERE owner = 'REPMVIEW' )
   LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to AW_VIEWS';
   END LOOP;
END;

Grand select from one schema to another


select 'grant select on [schema1].'||table_name||' to [schema2];' 
from dba_Tables 
where owner = '[schema1]';

Stop and Kill impdp/expdp jobs

Stop or kill a datapump job

Summary A very simple way to stop/kill a datapump job.

1. First check what datapump jobs are running:


SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SET WRAP OFF;


SQL> select * from dba_datapump_jobs;



OWNER_NAME  JOB_NAME              OPERATION   JOB_MODE   STATE      DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ------------------------------ 
SYSTEM      SYS_EXPORT_FULL_01    EXPORT        FULL   EXECUTING     32         1

2. To stop/kill the datapump job, login as sysdba and execute from sqlplus:


DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('JOB_NAME','OWNER_NAME');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

for example:


DECLARE  h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_03','SYS');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

3. After stopping the datapump job, oracle removes and the dmp files that already have been produced. Check again:

SQL> select * from dba_datapump_jobs; 

Find max column length in table

SELECT MAX(CHAR_LENGTH),
  table_name,
  column_name,
  data_type
FROM dba_tab_columns
WHERE owner = '[schema]'
GROUP BY table_name,
  column_name,
  data_type

Show open cursor count

--Step 1: which user open too many cursors

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';

--Step 2: put SID from previous Step 1 and find address

SELECT COUNT(*), address
FROM v$open_cursor
 WHERE sid = 267
 GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

--Step 3: find query which open cursors by Step 2 address

 SELECT sql_fulltext
 FROM v$sql
WHERE address = '0000000095175420';

--Increase number of cursors by 1000

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

More information https://support.esri.com/en/technical-article/000010136

Open expire account without changing password

begin
  for rec in (select
    regexp_substr( dbms_metadata.get_ddl('USER', username), '''[^'']+''') pw,
    username from dba_users where username IN ('SYSMAN')) loop
    execute immediate 'alter user ' || rec.username || ' account unlock';
    execute immediate 'alter user ' || rec.username || 
      ' identified by values ' || rec.pw;
  end loop;
end;

Create user with standart permissions


CREATE USER [user] IDENTIFIED BY [password]
       DEFAULT TABLESPACE YOUR_TABLESPACE  
       TEMPORARY TABLESPACE temp
       QUOTA UNLIMITED ON YOUR_TABLESPACE;

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE synonym, CREATE sequence, Create trigger, CREATE PROCEDURE TO [user];
      
GRANT connect TO [user];

Search all tables for specific values

set serveroutput on size 30000;
DECLARE
  match_count     INTEGER;
  v_search_string VARCHAR2(4000) := '%[seachable value]%';
BEGIN
  FOR t IN
  (SELECT owner,
    table_name,
    column_name
  FROM all_tab_columns
  WHERE data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 'CLOB', 'NCLOB')
  AND owner='[schema]'
  )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| t.owner || '.' || t.table_name|| ' WHERE '|| UPPER(t.column_name)|| ' like UPPER(:1)' INTO match_count USING v_search_string;
      IF match_count > 0 THEN
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line( 'Error encountered trying to read ' || t.column_name || ' from ' || t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;

Search all tables and views with name

SELECT owner, table_name name1
  FROM dba_tables where table_name like '%RIMM/_ACC%' ESCAPE '/'
union  
SELECT owner, view_name name1
  FROM dba_views where view_name like '%RIMM/_ACC%' ESCAPE '/'

Catch ORA-01017/ORA-28000 without AUDIT_TRAIL

The only drawback of the previous solution is that you have to restart the database. And maybe two times because after problem solved you would like to deactivate auditing. This is most probably not reliable solution on a production database so I have been looking for a better solution with no database reboot.

I initially thought of the AFTER LOGON trigger but you need to be logged-in and the BEFORE LOGON does not exits. Then at same documentation place I found the AFTER SERVERERROR trigger and decided to give it a try.

First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):


CREATE TABLE sys.logon_trigger
(
USERNAME VARCHAR2(30),
USERHOST VARCHAR2(128),
TIMESTAMP DATE
);

Second I created below trigger:


CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE);
    COMMIT;
  END IF;
END;

Then third simulated a wrong password access with my account and issued:

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SET lines 200
SQL> col USERHOST FOR a30
SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;
 
USERNAME                       USERHOST                       TIMESTAMP
------------------------------ ------------------------------ --------------------
yjaquier                       ST\GVADT30596                  23-nov-2012 11:05:56

Usage

SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;


How to compare rows one by one without unique id

WITH a1 AS (
    SELECT
        ROWNUM r,
        column1,
        column2,
        column3
    FROM
        your_table
)
SELECT DISTINCT a1.*
FROM a1
    INNER JOIN a1 a2 ON a2.r = a1.r + 1
WHERE
    a2.column1 <> a1.column1
OR  a2.column2 <> a1.column2
OR  a2.column3 <> a1.column3;

Get all jobs in database

select owner as schema_name,
       job_name,
       job_style,
       case when job_type is null 
                 then 'PROGRAM'
            else job_type end as job_type,  
       case when job_type is null
                 then program_name
                 else job_action end as job_action,
       start_date,
       case when repeat_interval is null
            then schedule_name
            else repeat_interval end as schedule,
       last_start_date,
       next_run_date,
       state
from sys.all_scheduler_jobs
order by owner,
         job_name;

User jobs logs

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name, status,
   SUBSTR(additional_info, 1, 200) ADDITIONAL_INFO
   FROM user_scheduler_job_run_details ORDER BY log_date desc;

All constraints of the table

   select *
from
    all_constraints
where
    r_constraint_name in
    (select       constraint_name
    from
       all_constraints
    where
       table_name='MYTAB'); 

Large objects in Database

1. Check the large size of segment present in the database

col owner for a6
col segment_name for a26
select * from
(select owner,segment_name||'~'||partition_name segment_name,segment_type,bytes/(1024*1024) size_m
from dba_segments
ORDER BY BLOCKS desc) where rownum < 11;

2. Get the list of LOB objects present in database

select owner, table_name, column_name, segment_name, index_name from dba_lobs where segment_name in ( 'SYS_LOB0000340891C00008$$');

Unique constraint on multiple columns - allow for single null

Task

you can have NULLs in your columns unless the columns are specified NOT NULL. You will be able to store only one instance of NULLs however (no two sets of same columns will be allowed unless all columns are NULL) :

SQL> CREATE TABLE t (id1 NUMBER, id2 NUMBER);

Table created
SQL> ALTER TABLE t ADD CONSTRAINT u_t UNIQUE (id1, id2);

Table altered
SQL> INSERT INTO t VALUES (1, NULL);

1 row inserted
SQL> INSERT INTO t VALUES (1, NULL);

INSERT INTO t VALUES (1, NULL)

ORA-00001: unique constraint (VNZ.U_T) violated

SQL> /* you can insert two sets of NULL, NULL however */
SQL> INSERT INTO t VALUES (NULL, NULL);

1 row inserted
SQL> INSERT INTO t VALUES (NULL, NULL);

1 row inserted

Solution

create unique index idx_t_unique on t 
(
  case when id1 is null or id2 is null then null else id1 end,
  case when id1 is null or id2 is null then null else id2 end
);

Make Oracle password never expire

Solution for making a password never expire:


SQL> connect sys/secret_password as sysdba;

SQL> alter user sysman account unlock ;

-- SQL> alter user DBUSER identified by newpa$$word;
-- See these important notes in you want to changing_your_password.

Next, change the profile limit to unlimited.

SQL> alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;

Check the password expiry date

 
SQL> select username, account_status, EXPIRY_DATE from dba_users where username='YOUR_USER';

PostgreSQL

Server install

  1. sudo dnf install https://download.postgresql.org/pub/repos/yum/9.6/fedora/fedora-25-x86_64/pgdg-fedora96-9.6-3.noarch.rpm
  2. sudo dnf install postgresql96-server postgresql96-contrib
  3. sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
  4. sudo systemctl enable postgresql-9.6.service
  5. sudo systemctl start postgresql-9.6.service
  6. sudo -u postgres psql postgres
  7. # \password

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

answer:

sudo -u postgres psql postgresStarting the database server manually

# \password postgres
Enter new password: 

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

  • By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
  • by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

What version is the server?

[user@localhost ~]$ psql --version
psql (PostgreSQL) 9.6.2

or

 postgres=# SELECT version(); 

or

bash # cat $PGDATADIRECTORY/PG_VERSION

Clinet GUI install

$ sudo dnf install phpPgAdmin
$ sudo dnf install pgadmin3

user and database creation:

 postgres=# CREATE USER lenny WITH PASSWORD 'leonard'; 
 postgres=# CREATE DATABASE carl OWNER lenny; 

list all users

[user@localhost ~]$ sudo -u postgres psql
could not change directory to "/home/user": Permission denied
psql (9.6.2)
Type "help" for help.
 postgres=# SELECT usename FROM pg_user; 

Result

usename  
----------
postgres
test
(2 rows)

Create Schema and Grant permissions

Create a schema called test in the default database called postgres

 postgres=# CREATE SCHEMA test; 

Create a role (user) with password

 postgres=# CREATE USER xxx PASSWORD 'yyy'; 

Grant privileges (like the ability to create tables) on new schema to new role

 postgres=# GRANT ALL ON SCHEMA test TO xxx; 

Grant privileges (like the ability to insert) to tables in the new schema to the new role

 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx; 

Disconnect

postgres=# \q

What is the server uptime?

 postgres=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime; 

Locating the database's system identifier

[user@localhost ~]$ sudo pg_controldata /var/lib/pgsql/data | grep "system identifier"

Result
----------
Database system identifier:           6416904430895725631
sudo pg_controldata /var/lib/pgsql/data

Listing databases on this database server

sudo -u postgres psql -l

OR

 postgres=# select datname from pg_database; 

OR

postgres=# \x
 postgres=# select * from pg_database; 

How many tables in a database?

  SELECT count(*) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog'); 
OR
sudo -u postgres psql -c "\d"

How much disk space does a database use?

current database size

SELECT pg_database_size(current_database());

size of all the databases together

SELECT sum(pg_database_size(datname)) from pg_database;

Which are my biggest tables?

The following basic query will tell us the 10 biggest tables:

 SELECT table_name
,pg_relation_size(table_schema || '.' || table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

Changing parameters in your programs

  1. You can change the value of a setting during your session, like this:
SET work_mem = '16MB';
  1. This value will then be used for every future transaction. You can also change it only for the

duration of the "current transaction":

SET LOCAL work_mem = '16MB';
  1. The setting will last until you issue this command:
RESET work_mem;
  1. Alternatively, you can issue the following command:
RESET ALL;

Then, the following will show up in the pg_settings catalog view:

 postgres=# SELECT name, setting, reset_val, source
FROM pg_settings WHERE source = 'session';

Finding the current configuration settings

postgres=# SHOW work_mem;

OR

 postgres=# SELECT * FROM pg_settings WHERE name = 'work_mem';

The actual location of each configuration file can be asked directly to the PostgreSQL server, as shown in this example:

postgres=# SHOW config_file;

OUTPUT

config_file


/etc/postgresql/9.4/main/postgresql.conf

Which parameters are at nondefault settings?

postgres=# SELECT name, source, setting
FROM pg_settings
WHERE source != 'default'
AND source != 'override'
ORDER by 2, 1;

Updating the parameter file

The parameter file is the main location for defining parameter values for the PostgreSQL server. All the parameters can be set in the parameter file, which is known as postgresql.conf .

There are also two other parameter files: pg_hba.conf and pg_ident.conf . Both of these relate to connections and security

forcing PostgreSQL to reread the postgresql.conf file (and all other configuration files):

pg_ctl reload

Some other parameters require a restart of the server for changes to take effect, for instance, max_connections , listen_addresses , and so on. The syntax is very similar to a reload operation, as shown here:

pg_ctl restart

If you are working with PostgreSQL version 9.4 or later, you can change the values stored in the parameter files directly from your session, with syntax such as the following:

 ALTER SYSTEM SET shared_buffers = '1GB';

Create autoincrement field with trigger and sequence in Postgres

1.
CREATE SEQUENCE AlimentosSequencia;

CREATE TABLE alimento (
  id integer NOT NULL DEFAULT nextval('AlimentosSequencia') PRIMARY KEY
 ,name VARCHAR(255));

INSERT INTO alimento (name) VALUES ('lemon');


2.
CREATE SEQUENCE AlimentosSequencia;

CREATE TABLE alimento (
  id integer NOT NULL PRIMARY KEY
 ,name VARCHAR(255));

 CREATE OR REPLACE FUNCTION AlimentoFuncion()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id:=nextval('AlimentosSequencia');
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;

 CREATE TRIGGER AlimentosTrigger
 BEFORE INSERT
 ON alimento
 FOR EACH ROW
 EXECUTE PROCEDURE AlimentoFuncion();

 INSERT INTO alimento (name) VALUES ('lemon');
3.
CREATE TABLE tablename (
    colname SERIAL
);
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

How to create sequence if not exists

Postgres 9.5+

CREATE SEQUENCE IF NOT EXISTS myschema.myseq;

Postgres 9.4 or older

DO
$do$
DECLARE
   _kind "char";
BEGIN
   SELECT relkind
   FROM   pg_class
   WHERE  oid = 'myschema.myseq'::regclass  -- sequence name, optionally schema-qualified
   INTO  _kind;

   IF NOT FOUND THEN       -- name is free
      CREATE SEQUENCE myschema.myseq;
   ELSIF _kind = 'S' THEN  -- sequence exists
      -- do nothing?
   ELSE                    -- object name exists for different kind
      -- do something!
   END IF;
END
$do$;

List all sequences in a Postgres

postgres=# -- \ds command can be used to view all sequences in the currently connected database

The following query gives names of all sequences.

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';


Create User and Database

CREATE USER [user] WITH PASSWORD 'password' login;
CREATE DATABASE [database] owner [user];
alter database [database] set search_path = [database];
---connect with [user] user to [database] db and create schema [schema]
\connect [database]
create schema [schema];
alter schema [schema] owner to [user];

Allow connections to PostgreSQL

on Linux, both on Ubuntu and Red Hat-based systems, the default PostgreSQL configuration has connections turned off for the postgres user by default. So after install of Boundless Server, if you try to connect to PostgreSQL via the psql command-line utility or through pgAdmin, you will get the following connection error:

  psql: FATAL:  peer authentication failed for user "postgres"

There are two steps to allow connections to PostgreSQL:

1. Set a password for the postgres user 2. Allow local connections to PostgreSQL

Setting a password for the postgres user

On Linux systems, there is no default password set. To set the default password:

1. Run the psql command from the postgres user account:

 sudo -u postgres psql postgres

2.Set the password:

 \password postgres

Allowing local connections

The file pg_hba.conf governs the basic constraints underlying connection to PostgreSQL. By default, these settings are very conservative. Specifically, local connections are not allowed for the postgres user. To allow this: 1. As a super user, open /etc/postgresql/9.6/main/pg_hba.conf (Ubuntu) or /var/lib/pgsql/9.6/data/pg_hba.conf (Red Hat) in a text editor. 2. Scroll down to the line that describes local socket connections. 3. Change the peer method to md5. 4. To allow connections using pgAdmin, find the line that describes local loopback connections over IPv6: 5. Change the ident method to md5. 6. Save and close the file. 7. Restart PostgreSQL:

 sudo service postgresql-9.6 restart

8. To test your connection using psql, run the following command:

 psql -U postgres -W

MAKING BACKUPS OF SELECT TABLES

Backup specifically named tables - no tricks

SELECT 'pg_dump ' || ' -h localhost -p 5432 -U postgres  -F c -b -v -f "/pgbak/somedb_keytbls.backup" ' ||
  array_to_string(ARRAY(SELECT '-t ' || table_schema || '.' || table_name   
        FROM information_schema.tables 
        WHERE table_name LIKE '%_notes' AND table_schema NOT IN('pg_catalog','public' )
    ), ' ') || ' somedb';

Backup recently changed tables using stats view

SELECT 'pg_dump ' || ' -h localhost -p 5432 -U postgres  -F c -b -v -f "/pgbak/somedb_keytbls.backup" ' ||
  array_to_string(ARRAY(SELECT '-t ' || schemaname || '.' || relname 
        FROM pg_stat_user_tables 
        WHERE last_autoanalyze > ( CURRENT_TIMESTAMP - (INTERVAL '1 day') )  )
    , ' ') || ' somedb';

How to drop multiple tables in PostgreSQL using a wildcard

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT footgun('public', 'tablename');


Modify OWNER on all tables simultaneously in PostgreSQL

Tables

SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Sequences

SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

Views

SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Materialized Views


SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

Dropping all views in postgreSql

SELECT 'DROP VIEW ' || table_name || ';'
  FROM information_schema.views
 WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
   AND table_name !~ '^pg_' and table_schema = '[schema]';

Drop all tables in PostgreSQL

SELECT 'drop table if exists "' || tablename || '" cascade;' as pg_drop
FROM pg_tables
WHERE schemaname='[schema]' and tablename like 'table_%';

Delete Duplicate Records in PostgreSQL

“ctid”, which is used in PostgreSQL to identify the row in a table: the ctid is not a static thing. UPDATE statements, VACUUM FULL, etc. can actually change it. Therefore it is highly recommended to perform this operation only if you are the only one on the system and if the table is locked appropriately.

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

Search every column of every table for a particular value

Assuming:

Find any row with any column containing the given value in its text representation - as opposed to equaling the given value. Return the table name (regclass) and the item pointer (ctid), because that's simplest. Here is a dead simple, fast and slightly dirty way:

CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
  RETURNS TABLE(_tbl regclass, _ctid tid) AS
$func$
BEGIN
   FOR _tbl IN
      SELECT c.oid::regclass
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = relnamespace
      WHERE  c.relkind = 'r'                           -- only tables
      AND    n.nspname !~ '^(pg_|information_schema)'  -- exclude system schemas
      ORDER BY n.nspname, c.relname
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
       , _tbl, '%' || _like_pattern || '%')
      USING _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM search_whole_db('mypattern')

View Results:

select * from TABLE_NAME where ctid='(0,5)';

Provide the search pattern without enclosing %.

Why slightly dirty?

If separators and decorators for the row in text representation can be part of the search pattern, there can be false positives:

column separator: , by default whole row is enclosed in parentheses:() some values are enclosed in double quotes " \ may be added as escape char And the text representation of some columns may depend on local settings - but that ambiguity is inherent to the question, not to my solution.

Each qualifying row is returned once only, even when it matches multiple times (as opposed to other answers here).

This searches the whole DB except for system catalogs. Will typically take a long time to finish. You might want to restrict to certain schemas / tables (or even columns) like demonstrated in other answers. Or add notices and a progress indicator, also demonstrated in another answer.

The regclass object identifier type is represented as table name, schema-qualified where necessary to disambiguate according to the current search_path:

if you want to retrieve all tables columns in a PostgreSQL database. You can use the following SQL query:

SELECT table_schema, table_name, column_name, data_type 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = '<table_name>'