SELECT d.datname, qs.*
FROM query_store.qs_view qs
join pg_database d on d.oid = qs.db_id
where qs.start_time > '2022-09-21'
order by qs.max_time desc;select datname, state, waiting, count(*)
from pg_stat_activity group by datname, state, waiting
order by datname;SELECT *, now() - pg_stat_activity.query_start AS duration
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds';-- get the ones hangin'
SELECT * FROM pg_stat_activity WHERE state = 'active';
-- attempt cancel
SELECT pg_cancel_backend(<pid>)
--attemt terminate
SELECT pg_terminate_backend(<pid>)
drop owned by the_user;
select pg_size_pretty(pg_relation_size('push_messages'));
select pg_size_pretty(pg_indexes_size('index-navnet'));
-- or in bytes
select pg_relation_size('push_messages');
SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name
Choose the database
[oracle ~]$ . oraenv
ORACLE_SID = [ oracle]? YOURDB
Start the Recovery Manager and then run the various commandoes mention below:
[oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jun 22 09:16:50 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: YOURDB (DBID=1234567890) ----------- Ensure that its the correct database
RMAN > shutdown immediate
RMAN > startup mount
RMAN > run {
RMAN > set until time "to_date('2015 JUN 17 07:00','YYYY MON DD HH24:MI')"; ------ Change the date
RMAN > restore database ;
RMAN > recover database ;
RMAN > }
RMAN > alter database open resetlogs;
RMAN> EXIT
You're now done, and you can use the database as intended.
Database uptime
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;
Show service-name
show parameter service_name
Connect to specific database
sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.1.1.1)(Port=1521))(CONNECT_DATA=(SID=THE_SID)))'
Show all databases
SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
Show all tablespaces
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
Accidentaly deleted all databases?
connect sys/vegard11 as sysdba
startup mount
alter database datafile 'C:\_USR\ORACLE\ODB\ORADATA\ORCL\<db-filename>.DBF' offline drop;
Display whatever version is in use
SELECT * FROM V$VERSION
Show all foreign keys
select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='RECON_EVENTS');
Change user/schema password
ALTER USER TEST_MDS IDENTIFIED BY oracle;
Drop existing user
DROP USER R1_SOAINFRA (CASCADE);
Drop existing tablespace
DROP TABLESPACE R1_SOAINFRA INCLUDING CONTENTS ([AND|KEEP] DATAFILES) CASCADE CONSTRAINTS;
Find all active locks for tables within session
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
Show views in the database
SELECT view_name, owner FROM sys.all_views where VIEW_NAME LIKE 'SCHEMA_%' ORDER BY owner, view_name;
Change passwords
ALTER USER <username> IDENTIFIED BY "<password>";
Alter some configurations
alter system set open_cursors = 1000
alter system set sessions = 300 scope = spfile
alter system set processes = 500 scope = spfile (this might need to be added to the init.ora-configuration file [e.g C:\_usr\oracle\odb\product\11.2.0\dbhome_1\dbs])
Error: ORA-23515
--This provides the entitled error:
drop tablespace DEV_SOAINFRA INCLUDING CONTENTS;
--Fix this by doing this:
select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;' from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name = 'DEV_SOAINFRA');
Rerun the drop :).
Error: ORA-01139
Executing:
alter database open resetlogs;
Might throw the following exception:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/18/2016 09:44:18
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
This is due to the RESTLOGS option is not valid for execution. Run the following command instead:
alter database open
http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle