Oracle does not come with an INFORMATION_SCHEMA.
Create the INFORMATION_SCHEMA schema
In order to install INFORMATION_SCHEMA, the INFORMATION_SCHEMA schema itself must be created and
granted the necessary privileges:
-- conn sys@&db_name as sysdba
connect / as sysdba
-- Rene Nyffenegger, 2018-02-03: drop … CASCADE.
drop user information_schema cascade;
create user information_schema identified by information_schema;
grant connect to information_schema;
grant create view to information_schema /* Rene Nyffenegger, 2018-02-03 */;
grant resource to information_schema;
grant select_catalog_role to information_schema;
grant create public synonym to information_schema;
grant select on props$ to information_schema with grant option;
-- Rene Nyffenegger, 2018-02-03
grant select on sys.all_procedures to information_schema with grant option;
grant select on sys.all_tables to information_schema with grant option;
grant select on sys.all_types to information_schema with grant option;
grant select on sys.all_users to information_schema with grant option;
grant select on sys.all_views to information_schema with grant option;
grant select on sys.all_triggers to information_schema with grant option;
-- Rene Nyffenegger, 2019-10-22:
grant select on sys.all_objects to information_schema with grant option;
grant select on sys.all_tab_columns to information_schema with grant option;
grant select on sys.all_constraints to information_schema with grant option;
alter user information_schema default role connect, resource, select_catalog_role;
-- connect information_schema@&db_name/information_schema
connect information_schema/information_schema
The views
catalog_name
create or replace view information_schema.catalog_name as
select sys_context('userenv', 'DB_NAME') catalog_name
from dual
;
grant select on catalog_name to public;
create or replace public synonym catalog_name for information_schema.catalog_name;
domains
create or replace view information_schema.domains as
--select * from (
select
sys_context('USERENV', 'DB_NAME') domain_catalog,
owner domain_schema,
type_name domain_name,
to_number(null) character_maximum_length,
to_number(null) character_octet_length,
sys_context('USERENV', 'DB_NAME') character_set_catalog,
'SYS' character_set_schema,
sys_context('USERENV', 'DB_NAME') collation_catalog,
'SYS' collation_schema,
sys_context('USERENV','NLS_SORT') collation_name,
to_number(null) numeric_precision,
to_number(null) numeric_precision_radix,
to_number(null) numeric_scale ,
to_number(null) datetime_precision,
to_char (null) domain_default
from
all_types
--) domains
;
grant select on domains to public;
create or replace public synonym domains for information_schema.domains;
routines
create or replace view information_schema.routines as
select
sys_context('userenv', 'DB_NAME') routine_catalog,
sys_context('userenv', 'DB_NAME') specific_catalog,
ap.owner specific_schema,
ap.owner routine_schema,
decode( ap.procedure_name, null, ap.object_name || ap.procedure_name, ap.procedure_name ) specific_name,
decode( ap.procedure_name, null, ap.object_name || ap.procedure_name, ap.procedure_name ) routine_name,
ao.object_type routine_type,
decode(impltypeowner, null, to_char(null), SYS_CONTEXT('userenv', 'DB_NAME')) type_udt_catalog,
to_clob(get_proc_text(ap.owner, ap.object_name, ao.object_type, 32767)) routine_body,
to_clob(get_proc_text(ap.owner, ap.object_name, ao.object_type, 4000)) routine_definition,
sys_context('userenv', 'DB_NAME') character_set_catalog,
'SYS' character_set_schema,
sys_context('userenv', 'DB_NAME') collation_catalog,
'SYS' collation_schema,
deterministic is_deterministic,
pipelined is_pipelined ,
aggregate is_aggregate,
authid is_definer
from
all_procedures ap,
all_objects ao
where
ap.owner = ao.owner and
ap.object_name = ao.object_name and
ao.object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION')
;
grant select on routines to public;
create or replace public synonym routines for information_schema.routines;
The view routines depends on get_proc_text:
create or replace function information_schema.get_proc_text(
p_owner in varchar2,
p_name in varchar2,
p_type in varchar2,
p_size in pls_integer default 32767 )
return clob
as
v_text CLOB;
v_max_size PLS_INTEGER;
v_size PLS_INTEGER := 0;
v_size_running PLS_INTEGER := 0;
begin
if p_size is null or p_size < 0 /* or p_size > 32767 */ then
v_max_size := 32767;
else
v_max_size := p_size;
end if;
for ci in (
select
to_clob(text) text
from
all_source
where
owner = p_owner and
name = p_name and
type = p_type
)
loop
v_size := length(ci.text);
if v_size + v_size_running > v_max_size then
v_text := v_text || substr(ci.text, 1, v_max_size - v_size_running);
exit;
else
v_text := v_text || ci.text;
end if;
v_size_running := v_size_running + v_size;
end loop;
return v_text;
end;
/
schemata
create or replace view information_schema.schemata as
-- SELECT * FROM (
with char_set as (
select
value$ cs
from
sys.props$
where
name = 'NLS_CHARACTERSET'
)
select
sys_context('userenv', 'DB_NAME') catalog_name,
username schema_name,
char_set.cs default_character_set_name,
sys_context('USERENV','NLS_SORT') default_collation_name,
to_char(null) sql_path
from
char_set,
all_users
-- ) schemata
/
grant select on schemata to public;
create or replace public synonym schemata for information_schema.schemata;
tables
create or replace view information_schema.tables as
-- select * from (
select
sys_context('userenv', 'DB_NAME') table_catalog,
owner table_schema,
table_name table_name,
case
when iot_type = 'Y' then 'IOT'
when temporary = 'Y' then 'TEMP'
else 'BASE TABLE'
end table_type
from
all_tables
union all
select
sys_context('userenv', 'DB_NAME') table_catalog,
owner table_schema,
view_name table_name,
'VIEW' table_type
from
all_views
-- ) tables
;
grant select on tables to public;
create or replace public synonym tables for information_schema.tables;
views
create or replace view views as
select
sys_context('userenv', 'DB_NAME') table_catalog,
owner table_schema,
view_name table_name,
text view_definition,
'VIEW' table_type,
(select max( case when uuc.updatable = 'YES' or
uuc.deletable = 'YES' or
uuc.insertable = 'YES' then 'YES' else 'NO' end
)
from
user_updatable_columns uuc
where
uuc.owner = av.owner and
uuc.table_name = av.view_name
) is_updatable,
decode(
(select 1
from
all_constraints ac
where
ac.owner = av.owner and
ac.table_name = av.view_name and
ac.constraint_type = 'V'), 1, 'CASCADE', 'NONE') check_option
from
all_views av
;
grant select on views to public;
create or replace public synonym views for information_schema.views;