Questions tagged [data-dictionary]
19 questions
12
votes
4 answers
How to identify the ranges over which a postgres table was partitioned?
If I create a table and partitions like this...
CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100);
CREATE TABLE tab1_p2 PARTITION OF tab1 FOR VALUES FROM (100) TO (200);
how…
ConanTheGerbil
- 1,303
- 5
- 31
- 50
3
votes
2 answers
Roles assigned to each user POSTGRES
i'm trying to make a query that shows the users and all the roles that they have, i already know how to ask about the roles of one particular user:
SELECT oid, rolname FROM pg_roles WHERE
pg_has_role( 'name_of_user', oid, 'member');
Any idea how…
Green_Sam
- 43
- 1
- 4
2
votes
1 answer
information_schema.columns slow performance
we have system that quite a lot and often asks database about:
SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, NUMERIC_SCALE, DATA_TYPE, IS_NULLABLE
FROM…
Mikhail Aksenov
- 430
- 1
- 7
- 19
2
votes
1 answer
what oracle data dictionary view can be used to decribe types defined in a plsql package?
What oracle data dictionary view can be used to describe types / subtypes defined in a plsql package?
johnsod0
- 15
- 1
1
vote
0 answers
User types as foreign keys
Largely theoretical question or idea discussion
Lets say we have several simple dictionaries:
create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer primary key, description varchar(256));
create table…
White Owl
- 1,029
- 3
- 9
1
vote
3 answers
Does increased Data Dictionary size affects performance
As I understand, Data Dictionary
Is a collection of tables, part of SYS schema (if not the whole SYS
schema)
It is stored as 'regular' tables, so whatever is applied to
database tables, also applies to data dictionary (e.g. indexes,
statistics)…
Athafoud
- 186
- 9
1
vote
0 answers
Invalid views not showing in user_dependencies after first compilation
When I create an (invalid) view that selects from another invalid view, the view does not immediately show up in user_dependencies. I am wondering if this is expected.
These are the steps to reproduce the behavior:
First, I create a table
create…
René Nyffenegger
- 3,763
- 7
- 32
- 39
1
vote
1 answer
Recreate fixed views
Is it possible to recreate Oracle fixed views? I have a performance problem with GV$ACTIVE_SESSION_HISTORY that might require rebuilding the view.
A simple select * from gv$active_session_history; runs forever because of a bad execution plan. This…
Jon Heller
- 524
- 2
- 8
- 27
0
votes
1 answer
How to replace LONG columns in Data Dictionary views in Oracle with varchar
This oracle forum posts in 2015 says that Long data type is deprecated for 20 years. Right now, in 2023, it is almost 30 years this data type is deprecated but it is commonly used in data dictionary views.
Even though for some of the long data type…
Atilla Ozgur
- 1,466
- 6
- 24
- 35
0
votes
0 answers
Redshift PG_TABLE_DEF table listing unknown tables
When I count number of tables in my schema manually, it is 220.
When I count number of views in my schema manually, it is 23.
220 + 23 = 243
However, this query:
select count(distinct tablename) from PG_TABLE_DEF where schemaname='myschema'
returns…
Nasrudeen
- 101
- 1
0
votes
1 answer
Expressing A Composite Key in a Data Dictionary
I am looking for an example of expressing a "Composite Keys" in a data dictionary.
I am using an MSO Access application with a Lecturer Table and Subjects Table where I have taken the Primary Keys from their tables to create a table called…
Jeff
- 1
- 1
0
votes
1 answer
Data Lake : Data Catalog and Data Schema definitions
I'm trying to understand the definition of data catalog and data schema. After several articles I read, I'm getting even more confused!
I realized when the article is talking about data lake, the term will be used to describe data catalog as…
cna
- 101
0
votes
1 answer
InnoDB Data Dictionary Cleanup for a Non-Existent Database Named "tmp"?
I have a number of error messages like this in my error.log file referencing different files that can't be opened when I start my mariadb 10.4.8 server:
2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot open datafile for read-only:…
SAH62
- 1
- 1
0
votes
1 answer
What privileges are need for a normal Oracle user to access V$ and DBA_ views?
I'm connecting to Oracle 19c as HR user. I want to execute the following SQL and some dba_... views:
select s.sid
,s.serial#
,s.username
,s.machine
,s.status
,s.lockwait
,t.used_ublk
,t.used_urec
…
Just a learner
- 2,082
- 7
- 36
- 57
0
votes
2 answers
PostgreSQL - how to generate an array containing column name and column data...?
I'd like to create a dictionary - an array - from a simple query, to include column names in output.
IE, I'd like to turn this:
SELECT id, last, first FROM names;
id | last | first
-----+----------+-------------------------
001 |…
DrLou
- 121
- 2
- 4