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 "information_schema"."columns" WHERE TABLE_NAME = 'some_instances' AND TABLE_SCHEMA = 'public' ORDER BY ORDINAL_POSITION Time to execute this query is 10s+, and it ruins overall performance "information_schema"."columns" contains more than 5m records Postgres version is 9.6 How to speed up such query? UPD. cant paste here execution plan as it's off stackexchange limitation. here is the link: https://github.com/mkdel/code_pile/blob/main/EXPLAIN_ANALYZE_BUFFERS

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 columns has duplicated varchar2 columns after 12c, there are a still a lot of them for example data_default column in ALL_TAB_COLS. select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT, VIRTUAL_COLUMN, IDENTITY_COLUMN from ALL_TAB_COLS C WHERE C.table_name = 'TABLE_NAME' AND C.OWNER = 'SCHEMA_NAME' AND C.COLUMN_NAME = 'COLUMN_NAME'; How to convert these long columns to varchar2?

Is it possible to determine what user created another user's account in Oracle database? I have two types of users in database: - Users having system and object privileges - Users having only system privileges I can find the owner of all users having system and object privileges. select distinct d.username,d.account_status,t.privilege,t.owner from dba_users d,dba_tab_privs t where d.username=t.grantee and d.account_status='OPEN'; But this query doesn't fetch the users having only system Privileges. Please help how to find the owner of all those users having only system privileges?

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 dic3(code integer primary key, description varchar(256));

And we have a table which references these dictionaries

create table TabA (
   id integer primary key,
   d1 integer null references dic1(code) on delete set null on update cascade,
   d2 integer null references dic2(code) on delete set null on update cascade,
   d3 integer null references dic3(code) on delete set null on update cascade
);

Can we create user type so that a field of it will automatically be defined as foreign key? So if we have a second table:

create table TabB(
   id integer primary key,
   d1 ref_dic1,
   d2 ref_dic2,
   d3 ref_dic3,
);

And I want the TabB be the same as TabA. I am not sure this can be done in SQL Server, but most likely we can write such extension for SQLite (maybe someone saw such extension and can share a link?) --- When we select from such table, we can join with a dictionary to see a description rather then a code. But to filter we have to use code (or join with the dictionary).

select id, dic1.description as d1_name
from TabA
join dic1 on TabA.d1=dic1.code
join dic2 on TabA.d2=dic2.code
where dic2.description="Apple"

With UDDT it should be possible to write something like

select id, dictionary(d1) from TabB where dictionary(d2)="Apple"
// or even
select id, d1 from TabB where d2="Apple"
// with autocall of dictionary decoding, since d1 and d2 are UDDT

This can be done in SQL Server with functions even without UDDT. But it would require a bunch of functions like dictionary_dic1(@code), dictionary_dic2(@code), etc. One for each dictionary. Or pass the name of the dictionary table as parameter, and use dynamic sql - performance would suck a lot, but it is possible. With UDDT, I hope to avoid this and have just one dictionary() function which would automatically pick a correct dictionary table. The SQLite extension on the other hand should handle such code with ease. Probably. Again, did anyone saw such extensions? link, please? --- So, what do you think? Is there a value in this or do you see any hurdles which would make such feature impractical? Did anyone saw SQLite extensions (even half baked) in this direction?

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 can I subsequently check the ranges? I've tried browsing the information_schema.tables and information_schema.table_constraints but no luck so far.

I am trying to write a query that will show me the tables that contains all the following columns: *ProductGroup*, *ProductClass* and *ProductID*. But I am not very successful at it. Can somebody help me with that?

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 **305**. Why is there a mismatch?

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 Timetable. From the information building the database file I must create a Data Dictionary but was caught off guard when I had to question myself in explaining the referencing of these two fields that create them as a Composite Key. My "Data Dictionary" is comprised of a Field Name, Data Type with Field Size, Constraints, References and Field Description. I have found it difficult to find any subjects on this matter which is directed by how a Composite Key in a Data Dictionary may be expressed and hope I may get some ideas here.

DBA_SEGMENTS just shows objects that already have some associated extent (and so, it's considered a segment). ### Demo Creating a new tablespace: SQL> create tablespace manu datafile size 20m; Tablespace created. Creating a new table (without any inserts, so no extent will be created). SQL> create table foo.t1 (id int) tablespace manu; Table created. Trying to drop tablespace (without specifying "including contents") SQL> drop tablespace manu; drop tablespace manu * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option How can I map objects and tablespace without using DBA_SEGMENTS? The first idea was to check DBA_OBJECTS , but there is no column about which tablespace the objects are associated with. Is there other view I can use? I am able to drop the tablespace after dropping the table. SQL> drop table foo.t1; Table dropped. SQL> drop tablespace manu; Tablespace dropped. SQL>

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 containing metadata information within data lake. As obvious as the name, it is a catalog of all information within data lake it self. Now, as far as I know, data catalog is database. Whereas data schema is described as containing metadata information within a database. Which means data catalog in data lake is equal to data schema in database. Both seems to have the same purpose such as data discovery. **EXCEPT** the terms are used in different level. One for data lake and the other one is database. My questions are : 1. Isn't the terms used inconsistent and confusing? What is the right term I should be using for each level? 2. If these two are different, how are they different? Please enlighten me..

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: '/tmp/#sql50b_8808_1.ibd' OS error: 71
2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot read first page of './tmp/#sql50b_8808_1.ibd' I/O error
2019-09-30 17:38:19 0 [Note] InnoDB: Cannot read first page in datafile: ./tmp/#sql50b_8808_1.ibd, Space ID:18446744073709551615, Flags: 0
2019-09-30 17:38:19 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-09-30 17:38:19 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-09-30 17:38:19 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-09-30 17:38:19 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tmp.#sql50b_8808_1`. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/  for how to resolve the issue.
2019-09-30 17:38:19 0 [Warning] InnoDB: Ignoring tablespace for tmp.#sql50b_8808_1 because it could not be opened.

I don't have a database named tmp, so the instructions for data dictionary troubleshooting haven't been helpful. I can't find any files that start with #sql anywhere in my file system, but SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; returns 65 rows, including one associated with the file described above. Is there some way to clean this up so MariaDB doesn't try to open these non-existent files?

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 ,t.start_time from v$transaction t inner join v$session s on t.addr = s.taddr; Oracle tells me the objects doesn't exist. It seems like a permission issue. What privileges are needed for me to access dynamic performance views and DBA_... views?

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 | Smith | John into: { "id": "001","last": "Smith", "first": "John" } Yes, bonus points for the quotes and colons! Ha! We do have this very interesting catalog function, which will return all column names into an array. SELECT array_agg(column_name::TEXT) FROM information_schema.columns WHERE table_name = 'people'; How to aggregate (couldn't help myself) that into a regular query?

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 only happens on a small number of our databases and probably has something to do with previous NLS settings. The below execution plan uses 2 FIXED TABLE FULL operations, because the NLSSORT predicate prevents a fixed index from being used: explain plan for select * from gv$active_session_history; select * from table(dbms_xplan.display); Plan hash value: 2432277601 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 136K| 169M| 215 (100)| 00:00:04 | | 1 | VIEW | GV$ACTIVE_SESSION_HISTORY | 136K| 169M| 215 (100)| 00:00:04 | | 2 | NESTED LOOPS | | 136K| 62M| 215 (100)| 00:00:04 | | 3 | FIXED TABLE FULL| X$KEWASH | 136K| 3196K| 72 (100)| 00:00:02 | |* 4 | FIXED TABLE FULL| X$ASH | 1 | 454 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n ls_sort=''BINARY_CI''')) For comparison, here is a good execution plan on 99% of our databases: Plan hash value: 436940376 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 127K| 0 (0)| 00:00:01 | | 1 | VIEW | GV$ACTIVE_SESSION_HISTORY | 100 | 127K| 0 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 100 | 131K| 0 (0)| 00:00:01 | | 3 | FIXED TABLE FULL | X$KEWASH | 100 | 5200 | 0 (0)| 00:00:01 | |* 4 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 1 | 1299 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE") ---------- Here is what I've tried, and workarounds that don't work: 1. **Hints and plan management features.** Fixing queries one-at-a-time isn't good enough. This fixed view is used in too many system queries, I don't want to modify them all. For example, I can fix my example with a hint like this: select /*+ use_hash(@"SEL$3" "A"@"SEL$3") */ * from gv$active_session_history order by sample_time desc;. But I can't change the system queries that use GV$*, and I don't want to have to manage each individual query. 2. **Gathering statistics.** "Rows = 1" implies bad statistics, but I've already tried gathering statistics and it doesn't help. 3. **Faking statistics.** I couldn't get the plan to use a hash join even after setting the table rows ridiculously high and setting the column distinct low. Even when the optimizer thinks the join returns quadrillions of rows it still uses a nested loop with two full table scans. 4. **Changing NLS settings.** At first this *looks* like the typical linguistic-sorting-ignoring-index issue. But nls_sort and nls_comp are both set to BINARY. When I change nls_comp and nls_sort at the session level, the predicate has 2 NLSSORT functions: alter session set nls_comp='LINGUISTIC'; alter session set nls_sort='BINARY_CI'; explain plan for select * from gv$active_session_history where sql_id = '7f7bap53hb12w'; select * from table(dbms_xplan.display); ... Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(NLSSORT("A"."SQL_ID",'nls_sort=''BINARY_CI''')=HEXTORAW('37663762617035336 86231327700') AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n ls_sort=''BINARY_CI''')) When I set them back to BINARY one of the NLSSORT goes away, but one remains, preventing the index: alter session set nls_comp='BINARY'; alter session set nls_sort='BINARY'; explain plan for select * from gv$active_session_history where sql_id = '7f7bap53hb12w'; select * from table(dbms_xplan.display); ... Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("A"."SQL_ID"='7f7bap53hb12w' AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n ls_sort=''BINARY_CI''')) 5. **Find the view source.** I can't find the full view source. It's not in DBA_VIEWS and only the first 4000 characters is in $FIXED_VIEW_DEFINITION. I tried grep -i v.*active_session_history * in $ORACLE_HOME/rdbms/admin/ but didn't see anything. 6. **Recompile.** alter view gv$active_session_history compile; throws an ORA-600. I'm running 11.2.0.4 on Solaris. I created an Oracle support service request but have not received an answer yet.

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) - Its statistics should be updated - It is always cached Can the the size and the complexity of oracle data dictionary affect the database performance? When I add a new object on my database a new (or multiple) entries is/are added on data dictionary. A scenario I can think of has to do with partitioning. So if I have a table which have a partition and subpartitions, I will have an entry for the table, an entry for each partition and an entry of each partition subpartitions in data dictionary. Those entries are spawned among different 'tables'. Imagine know having tables with thousands of partitions and hundreds of subpartition. Again this number of records may not be something that a dbms can handle, but how it will affect its performance as it grows? Note: I have read a lot of discussion on if and how partitioning affects performance in general. This is out of scope for this question. Partitioning is just an example I gave, to show how data dictionary volume can grow.

What oracle data dictionary view can be used to describe types / subtypes defined in a plsql package?

I want to create a new stored procedure but I got this error: 00955. 00000 - "name is already used by an existing object" The problem is I don't remember to have created an object with this name and, now, I would like to remove it (don't worry, it's not on my production database). How can I find the type of this object in order to drop it correctly?

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 to do it?

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 table tq84_t ( col_1 number, col_2 number ); Then I create a view that is dependent on the table. Note the typo col_22 instead of col_2: create or replace force view tq84_v as select col_1, col_22 from tq84_t; Of course, this view will be created in an invalid state: Warning: View created with compilation errors. I create another view which is dependent on tq84_v (which will also be in an invalid state): create force view tq84_v2 as select * from tq84_v; I now query user_dependencies to check whether I have objects that are dependent on tq84_v. I would have expected TQ84_V2 to be reported, but the following query returns no record: select name from user_dependencies where referenced_name = 'TQ84_V'; I re-compile the second view: alter view tq84_v2 compile; and query user_dependencies again: select name from user_dependencies where referenced_name = 'TQ84_V'; Only this time, Oracle will report TQ84_V2 as being dependent on TQ84_V. Is this behavior expected?