- Integrated development environment (IDE) for working with SQL/PLSql in Oracle databases.
- An Oracle database client that can run SQL and PL/SQL commands and display their results.
- Is a software product for creating screens that interact with an Oracle database. It has an IDE including an object navigator, property sheet and code editor that uses PL/SQL.
- Oracle Reports is a tool for developing reports against data stored in an Oracle database.
set serveroutput on; set autotrace on statistics; set timing on; declare begin null; end; /
List of users:
select distinct(OWNER) from ALL_TABLES;
List of current user owned tables:
select * from USER_TABLES; select TABLE_NAME from USER_TABLES;
List of tables by owner:
select OWNER || '.' || TABLE_NAME from ALL_TABLES order by OWNER;
List of current user table sizes:
select SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES) from USER_EXTENTS group by SEGMENT_NAME, SEGMENT_TYPE order by sum(BYTES); select sum(BYTES) from USER_EXTENTS;
select * from USER_INDEXES order by TABLE_NAME;
List of index sizes:
select index_name, table_name, sum(user_extents.bytes) as bytes from user_indexes left outer join user_extents on user_extents.segment_name = table_name group by index_name, table_name order by table_name;
List of tables without primary keys:
select OWNER || '.' || TABLE_NAME from ALL_TABLES where TABLE_NAME not in ( select distinct TABLE_NAME from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P' ) and OWNER in ('USER1', 'USER2') order by OWNER, TABLE_NAME;
List of currenct user constraints:
select * from USER_CONSTRAINTS;
List of tablespaces:
select distinct TABLESPACE_NAME from USER_TABLES;
List of current user permissions:
select * from SESSION_PRIVS;
List of user permissions to tables:
select * from ALL_TAB_PRIVS where TABLE_SCHEMA not like '%SYS' and TABLE_SCHEMA not like 'SYS%';
List of user privileges:
select * from USER_SYS_PRIVS select * from USER_TAB_PRIVS select * from USER_ROLE_PRIVS
Timing info about last queries:
select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql order by LAST_LOAD_TIME desc
Improved version of above code:
column LAST_LOAD_TIME format a20; column TIME format a20; column MODULE format a10; column SQL_TEXT format a60; set autotrace off; set timing off; select * from ( select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME, MODULE, SQL_TEXT from SYS."V_\$SQL" where SQL_TEXT like '%BATCH_BRANCHES%' order by LAST_LOAD_TIME desc ) where ROWNUM <= 5;
SET TIMING ON; -- do stuff SET TIMING OFF;
set serveroutput on variable n number exec :n := dbms_utility.get_time; select ...... exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' );
- $SQL lists statistics on shared SQL area without the GROUP BY clause.
select max(scn_to_timestamp(ora_rowscn)) from TBL; select timestamp from all_tab_modifications where table_owner = 'OWNER'; select timestamp from all_tab_modifications where table_name = 'TABLE';
column parameter format a32; column value format a32; select parameter, value from v$nls_parameters; alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS'; alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6'; alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6'; select sysdate from dual;
Show error details:
Dump how exactly field stored:
select dump(date '2009-08-07') from dual;