Dump the Oracle configuration of a Nuxeo DB, this script will output a /tmp/oraconf.txt file, you can run it using sqlplus using the Nuxeo db account.

dump-nuxeo-oracle-conf.sql

-- ------------------------------------------------------------
-- Dump Nuxeo and Oracle info
--
SET ESCAPE \
SET SQLPROMPT 'SQL> '
SPOOL '/tmp/oraconf.txt';
SET PAGESIZE 50000;
SET LONG 50000;
SET LINESIZE 200;
-- version and db name
SELECT name AS database_name FROM v$database;
SELECT user, sysdate, version FROM v$instance;

SET TIMING ON;
-- ------------------------------------------------------------
-- NUXEO
SELECT COUNT(*) AS documents_count FROM hierarchy WHERE isproperty = 0;
COLUMN "primarytype" format a30;
SELECT primarytype, COUNT(*) AS count FROM hierarchy WHERE isproperty=0 GROUP BY primarytype ORDER BY count DESC;
SELECT COUNT(*) AS hierarchy_count FROM hierarchy;
SELECT COUNT(*) AS proxies_count FROM proxies;
DESC fulltext;
SELECT COUNT(*) AS aces_count FROM acls;
SELECT COUNT(DISTINCT(id)) AS acls_count FROM acls;
SELECT COUNT(*) AS aclr_count FROM aclr;
COLUMN ACL format a100;
SELECT LENGTH(acl) AS aclrmax_size, acl FROM aclr WHERE LENGTH(acl) = (SELECT MAX(LENGTH(acl)) FROM aclr);
SELECT (SELECT COUNT(*) FROM "users") AS "users", (SELECT COUNT(*) FROM "groups") AS "groups" FROM DUAL;
SELECT text FROM all_source WHERE name = 'NX_ANCESTORS';
SELECT text FROM all_source WHERE name = 'NX_GET_READ_ACL';
SELECT COUNT(*) AS ancestors_count FROM ancestors;
SET TIMING OFF;

-- ------------------------------------------------------------
-- ORACLE

-- sga info : shared pool + database buffer + redo + stream
SHOW sga;
-- sga shared pool
SELECT TO_CHAR(ROUND(SUM(decode(pool, 'shared pool', decode(name, 'library cache',0,'dictionary chace',0,'free memory',0,'sql area',0,(bytes)/(1024*1024)),0)),2)) shared_pool_misc_mb from V$SGASTAT;
-- pga info
SELECT TO_CHAR(ROUND(decode(unit,'bytes',(value)/(1024*1024),value),2)) pga_in_use_mb FROM V$PGASTAT WHERE name = 'total PGA inuse';
-- Sizes
SELECT * FROM V$MEMORY_DYNAMIC_COMPONENTS;
SELECT * FROM v$memory_target_advice ORDER BY memory_size;

-- Conf
SHOW PARAMETER target;
SHOW PARAMETER PROCESSES;
SHOW PARAMETER optimizer;
SHOW PARAMETER db_file_multi;
SHOW PARAMETER compat;
-- collation
COLUMN sname format a20
COLUMN pname format a20
COLUMN pval2 format a20
SELECT sname, pname, pval1, pval2 FROM sys.aux_stats$;
COLUMN PARAMETER format a30
COLUMN VALUE format a30
SELECT * FROM NLS_DATABASE_PARAMETERS;

-- DB Size
SELECT allocated.gigabytes AS allocated_gb, used.gigabytes AS used_gb, (used.gigabytes / allocated.gigabytes) * 100 pct_allocated FROM (SELECT SUM(bytes) / 1024 / 1024 / 1024 gigabytes FROM dba_data_files files) allocated, (SELECT SUM(bytes) / 1024 / 1024 / 1024 gigabytes FROM dba_extents) used;
-- Size repartition
COLUMN "SEGMENT_NAME" format a40;
SELECT segment_type, sum(bytes)/1024/1024 AS used_mb FROM dba_extents GROUP BY segment_type ORDER BY 2 DESC;
-- Top biggest segments
SELECT segment_name, segment_type, size_mb  FROM (SELECT /*+ FIRST_ROWS(20) */ a.*, ROWNUM rnum FROM (
  SELECT segment_name, segment_type, sum(bytes)/1024/1024 AS size_mb FROM dba_extents GROUP BY segment_name, segment_type ORDER BY 3 DESC) a WHERE ROWNUM <= 20) WHERE rnum > 0;
-- biggest table last analyzed time
SELECT table_name, num_rows, blocks, last_analyzed FROM (SELECT /*+ FIRST_ROWS(20) */ a.*, ROWNUM rnum FROM (
    SELECT table_name, num_rows, blocks, last_analyzed FROM user_tables ORDER BY 2 DESC NULLS LAST) a
WHERE ROWNUM <= 20) WHERE rnum > 0;
-- Index info
COLUMN table_owner format a15
COLUMN table_name format a40
COLUMN index_name format a40
COLUMN column_name format a30
SELECT di.table_owner, di.table_name, di.index_name, di.column_name FROM dba_ind_columns di RIGHT JOIN user_indexes u ON di.index_name = u.index_name WHERE di.table_owner=user ORDER BY di.table_name, di.column_position;

-- List of connections
SELECT username, COUNT(username) FROM v$session WHERE username IS NOT NULL GROUP BY username;
-- The percentage of blocks and libraries read from the cache
SELECT (1-(pr.value/(dbg.value+cg.value)))*100 cache_hit_ratio FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg WHERE pr.name='physical reads' AND Dbg.name='db block gets' AND cg.name='consistent gets';
-- locks
-- RE: Row Exclusive locks
-- S: Share locks
-- SRX: Share Row Exclusive locks
-- X: Exclusive locks
-- MR: Media Recovery (Share) locks
-- RT: Redo Thread (Exclusive) locks
-- XR: XR locks
-- TS: Temp Segment locks
SELECT type lock_type, count(*) FROM v$lock GROUP BY type;
--
-- Queries that took the most time (N)
SELECT * FROM (SELECT ' ', SQL_ID, SQL_FULLTEXT, EXECUTIONS, 
  ELAPSED_TIME/1000 AS "ELAPSEDms", CPU_TIME/1000 AS "CPU_TIMEms", BUFFER_GETS,
  ROWS_PROCESSED, FETCHES, (ELAPSED_TIME/1000)/EXECUTIONS AS CALLms
FROM V$SQLAREA
WHERE  PARSING_SCHEMA_NAME=(SELECT user FROM v$instance) AND SQL_FULLTEXT NOT LIKE '/* SQL Analyze%'
ORDER BY ELAPSED_TIME DESC) WHERE ROWNUM < 50;
-- Most frequent queries (N)
SELECT * FROM (SELECT ' ', SQL_ID, SQL_FULLTEXT, EXECUTIONS, 
  ELAPSED_TIME/1000 AS "ELAPSEDms", CPU_TIME/1000 AS "CPU_TIMEms", BUFFER_GETS,
  ROWS_PROCESSED, FETCHES, (ELAPSED_TIME/1000)/EXECUTIONS AS CALLms
FROM V$SQLAREA
WHERE  PARSING_SCHEMA_NAME=(SELECT user FROM v$instance) AND SQL_FULLTEXT NOT LIKE '/* SQL Analyze%'
ORDER BY EXECUTIONS DESC) WHERE ROWNUM < 50;
-- Slowest queries (N)
SELECT * FROM (SELECT ' ', SQL_ID, SQL_FULLTEXT, EXECUTIONS, 
  ELAPSED_TIME/1000 AS "ELAPSEDms", CPU_TIME/1000 AS "CPU_TIMEms", BUFFER_GETS,
  ROWS_PROCESSED, FETCHES, (ELAPSED_TIME/1000)/EXECUTIONS AS CALLms
FROM V$SQLAREA
WHERE  PARSING_SCHEMA_NAME=(SELECT user FROM v$instance) AND SQL_FULLTEXT NOT LIKE '/* SQL Analyze%'
ORDER BY ELAPSED_TIME/EXECUTIONS DESC) WHERE ROWNUM < 50;

SPOOL OFF;
SET SQLPROMPT 'SQL> '
quit
/