Examples To Get Definition of DB Objects

Normal DB objects we can get definition from  dbms_metadata.get_ddl():

SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI') FROM dual;

Tablespace
SELECT dbms_metadata.get_ddl('TABLESPACE', 'TEST_DATA') FROM dual;

select  dbms_metadata.get_ddl('INDEX','TEST') from dual;

 select  dbms_metadata.get_ddl('DB_LINK','TEST.COM') from dual;

 select  dbms_metadata.get_ddl('USER','TEST') from dual;

select  dbms_metadata.get_ddl('PROCEDURE','TEST_STRUCTURE') from dual;

select  dbms_metadata.get_ddl('FUNCTION','TEST_FUNC') from dual;

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


For  Oracle TEXT, we need to use specific package:
set long 500000
set pages 999
select ctx_report.create_index_script('FT_TESTTEXT2') from dual;

set long 50000
set pages 9999
select dbms_metadata.get_ddl('TABLE', 'DR$FT_TESTTEXT2MI') from dual;
select dbms_metadata.get_ddl('TABLE', 'DR$FT_TESTTEXT2$I') from dual;

How To Run Oracle Text Query From Sqlplus

Sometimes we need to debug some Text queries, we need to run them on sqlplus
Here are some examples:

set serveroutput on;
 ALTER SESSION SET EVENTS '30579 trace name context forever, level 2';

 declare
 text_result clob;
 begin
dbms_lob.createtemporary(text_result, true, DBMS_LOB.CALL);
 ctx_query.result_set('TEST_IDCTEXT2', 'computer',
 '<ctx_result_set_descriptor><count exact="false"/><group
 sdata="sdDrillDown"><count exact="true"/></group><hitlist start_hit_num="1"
 end_hit_num="2" order="dInDate Desc"><sdata name="dID"/><sdata
 name="sddDocName"/><rowid /></hitlist></ctx_result_set_descriptor>',
 text_result);
 dbms_output.put_line(dbms_lob.substr(text_result,200,1));
 dbms_output.put_line('hello');
 dbms_lob.freetemporary(text_result);
 end;
 /

SELECT DDOCNAME  FROM idctext2 WHERE CONTAINS(otsmeta,  '((((DEFINESCORE((henry), RELEVANCE * .01)) ))) * 10.0 * 10.0')>0;


SELECT DDOCNAME  FROM idctext2 WHERE CONTAINS(otsmeta,  '((((DEFINESCORE((test), RELEVANCE * .1)) )))  and  (((((EMPL%) WITHIN
dDocAccount)) or (((CATEST1%) WITHIN dDocAccount)) or (((GIT/GLOBAL#0023IT#0023ONLY%) WITHIN dDocAccount)) or (((GIT/ALL%) WITHIN dDocAccount)) or (((idcnull) WITHIN dDocAccount)))) ')>0;

Check Flashback Space Usage

A simple sql to show the percentage

SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
  FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED
  FROM V$FLASH_RECOVERY_AREA_USAGE); 

How To Check Cardinality Of A Table

There are 2 db views you can get  cardinality  information
user_tab_col_statistics 
dba_tab_col_statistics 

Check Num_Distinct and Density

The "Density" = "Num_Distinct" / "Total Number of Rows"

Please also pay attention when is the last analyzed time in the view.

Add Hidden Init DB Parameters

You need to add double quote for the parameters. examples:

alter system set "_external_scn_logging_threshold_seconds"=600 scope=spfile sid='*'; 
alter system set "_external_scn_rejection_delta_threshold_minutes"=10080 scope=both sid='*';