Not getting list of tables from database
I am trying to get list of tables owned by current Oracle user.I have created user in oracle in which I am having below 2 tables :
Categories
Address
Now when I run below query in oracle then I am getting only above 2 tables which I want :
SELECT table_name FROM user_tables;
Reference of above query : https://www.arungudelli.com/tutorial/oracle/list-all-tables-in-oracle-query/
But problem is when i am trying to get list of tables using GetSchema method then i am not getting anything :
public Datatable GetTables(DbConnection conn)
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
var tables = conn.GetSchema("Tables", new string { "user_tables" });
}
conn.Close();
}
Now when I do like below then I am getting unnecessary tables(> 1000) which are system tables and other tables(dont know from where they are coming though I only have Categories and Address tables) :
var tables = conn.GetSchema("Tables");
So I only want to get list of tables owned by current Oracle user.Below is my connection string :
data source="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyService)))";user id=C##Db1;password=root123;
Can someone please help me with this?
c# oracle ado.net oracle12c
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
add a comment |
I am trying to get list of tables owned by current Oracle user.I have created user in oracle in which I am having below 2 tables :
Categories
Address
Now when I run below query in oracle then I am getting only above 2 tables which I want :
SELECT table_name FROM user_tables;
Reference of above query : https://www.arungudelli.com/tutorial/oracle/list-all-tables-in-oracle-query/
But problem is when i am trying to get list of tables using GetSchema method then i am not getting anything :
public Datatable GetTables(DbConnection conn)
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
var tables = conn.GetSchema("Tables", new string { "user_tables" });
}
conn.Close();
}
Now when I do like below then I am getting unnecessary tables(> 1000) which are system tables and other tables(dont know from where they are coming though I only have Categories and Address tables) :
var tables = conn.GetSchema("Tables");
So I only want to get list of tables owned by current Oracle user.Below is my connection string :
data source="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyService)))";user id=C##Db1;password=root123;
Can someone please help me with this?
c# oracle ado.net oracle12c
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
does your user own the tables?
– Lachlan Lindsay
Nov 22 '18 at 12:20
@LachlanLindsay Yes as I have mentioned in the question that Categories and Adress table are the only tables onwed by user mentioned in the connection string
– ILoveStackoverflow
Nov 22 '18 at 12:22
When you access the database through c# code, who is the "current" user? It seems that the application connects to the DB as user SYSDBA, or some other admin account (which is why you get thousands of tables). How is the Oracle database supposed to know you want the tables for some specific user, if the application connects as SYSDBA?
– mathguy
Nov 22 '18 at 14:22
@mathguy Through connection string?because connection string have the information about the user.isnt it?
– ILoveStackoverflow
Nov 22 '18 at 14:24
add a comment |
I am trying to get list of tables owned by current Oracle user.I have created user in oracle in which I am having below 2 tables :
Categories
Address
Now when I run below query in oracle then I am getting only above 2 tables which I want :
SELECT table_name FROM user_tables;
Reference of above query : https://www.arungudelli.com/tutorial/oracle/list-all-tables-in-oracle-query/
But problem is when i am trying to get list of tables using GetSchema method then i am not getting anything :
public Datatable GetTables(DbConnection conn)
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
var tables = conn.GetSchema("Tables", new string { "user_tables" });
}
conn.Close();
}
Now when I do like below then I am getting unnecessary tables(> 1000) which are system tables and other tables(dont know from where they are coming though I only have Categories and Address tables) :
var tables = conn.GetSchema("Tables");
So I only want to get list of tables owned by current Oracle user.Below is my connection string :
data source="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyService)))";user id=C##Db1;password=root123;
Can someone please help me with this?
c# oracle ado.net oracle12c
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
I am trying to get list of tables owned by current Oracle user.I have created user in oracle in which I am having below 2 tables :
Categories
Address
Now when I run below query in oracle then I am getting only above 2 tables which I want :
SELECT table_name FROM user_tables;
Reference of above query : https://www.arungudelli.com/tutorial/oracle/list-all-tables-in-oracle-query/
But problem is when i am trying to get list of tables using GetSchema method then i am not getting anything :
public Datatable GetTables(DbConnection conn)
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
var tables = conn.GetSchema("Tables", new string { "user_tables" });
}
conn.Close();
}
Now when I do like below then I am getting unnecessary tables(> 1000) which are system tables and other tables(dont know from where they are coming though I only have Categories and Address tables) :
var tables = conn.GetSchema("Tables");
So I only want to get list of tables owned by current Oracle user.Below is my connection string :
data source="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyService)))";user id=C##Db1;password=root123;
Can someone please help me with this?
c# oracle ado.net oracle12c
c# oracle ado.net oracle12c
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
edited Nov 22 '18 at 12:19
ILoveStackoverflow
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
asked Nov 22 '18 at 12:14
ILoveStackoverflowILoveStackoverflow
517419
517419
does your user own the tables?
– Lachlan Lindsay
Nov 22 '18 at 12:20
@LachlanLindsay Yes as I have mentioned in the question that Categories and Adress table are the only tables onwed by user mentioned in the connection string
– ILoveStackoverflow
Nov 22 '18 at 12:22
When you access the database through c# code, who is the "current" user? It seems that the application connects to the DB as user SYSDBA, or some other admin account (which is why you get thousands of tables). How is the Oracle database supposed to know you want the tables for some specific user, if the application connects as SYSDBA?
– mathguy
Nov 22 '18 at 14:22
@mathguy Through connection string?because connection string have the information about the user.isnt it?
– ILoveStackoverflow
Nov 22 '18 at 14:24
add a comment |
does your user own the tables?
– Lachlan Lindsay
Nov 22 '18 at 12:20
@LachlanLindsay Yes as I have mentioned in the question that Categories and Adress table are the only tables onwed by user mentioned in the connection string
– ILoveStackoverflow
Nov 22 '18 at 12:22
When you access the database through c# code, who is the "current" user? It seems that the application connects to the DB as user SYSDBA, or some other admin account (which is why you get thousands of tables). How is the Oracle database supposed to know you want the tables for some specific user, if the application connects as SYSDBA?
– mathguy
Nov 22 '18 at 14:22
@mathguy Through connection string?because connection string have the information about the user.isnt it?
– ILoveStackoverflow
Nov 22 '18 at 14:24
does your user own the tables?
– Lachlan Lindsay
Nov 22 '18 at 12:20
does your user own the tables?
– Lachlan Lindsay
Nov 22 '18 at 12:20
@LachlanLindsay Yes as I have mentioned in the question that Categories and Adress table are the only tables onwed by user mentioned in the connection string
– ILoveStackoverflow
Nov 22 '18 at 12:22
@LachlanLindsay Yes as I have mentioned in the question that Categories and Adress table are the only tables onwed by user mentioned in the connection string
– ILoveStackoverflow
Nov 22 '18 at 12:22
When you access the database through c# code, who is the "current" user? It seems that the application connects to the DB as user SYSDBA, or some other admin account (which is why you get thousands of tables). How is the Oracle database supposed to know you want the tables for some specific user, if the application connects as SYSDBA?
– mathguy
Nov 22 '18 at 14:22
When you access the database through c# code, who is the "current" user? It seems that the application connects to the DB as user SYSDBA, or some other admin account (which is why you get thousands of tables). How is the Oracle database supposed to know you want the tables for some specific user, if the application connects as SYSDBA?
– mathguy
Nov 22 '18 at 14:22
@mathguy Through connection string?because connection string have the information about the user.isnt it?
– ILoveStackoverflow
Nov 22 '18 at 14:24
@mathguy Through connection string?because connection string have the information about the user.isnt it?
– ILoveStackoverflow
Nov 22 '18 at 14:24
add a comment |
1 Answer
1
active
oldest
votes
Taken from Microsoft's documentation regarding GetSchema(String, String)
If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". If a restriction value is not passed in, the default values are used for that restriction. This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. In that case, the empty string ("") is considered to be the value for the specified parameter.
You can read more here
answered Nov 22 '18 at 12:28
mb14mb14
1258
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
1
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430805%2fnot-getting-list-of-tables-from-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Taken from Microsoft's documentation regarding GetSchema(String, String)
If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". If a restriction value is not passed in, the default values are used for that restriction. This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. In that case, the empty string ("") is considered to be the value for the specified parameter.
You can read more here
answered Nov 22 '18 at 12:28
mb14mb14
1258
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
1
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
|
show 2 more comments
Taken from Microsoft's documentation regarding GetSchema(String, String)
If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". If a restriction value is not passed in, the default values are used for that restriction. This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. In that case, the empty string ("") is considered to be the value for the specified parameter.
You can read more here
answered Nov 22 '18 at 12:28
mb14mb14
1258
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
1
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
|
show 2 more comments
Taken from Microsoft's documentation regarding GetSchema(String, String)
If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". If a restriction value is not passed in, the default values are used for that restriction. This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. In that case, the empty string ("") is considered to be the value for the specified parameter.
You can read more here
answered Nov 22 '18 at 12:28
mb14mb14
1258
Taken from Microsoft's documentation regarding GetSchema(String, String)
If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". If a restriction value is not passed in, the default values are used for that restriction. This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. In that case, the empty string ("") is considered to be the value for the specified parameter.
You can read more here
answered Nov 22 '18 at 12:28
mb14mb14
1258
answered Nov 22 '18 at 12:28
mb14mb14
1258
answered Nov 22 '18 at 12:28
mb14mb14
1258
answered Nov 22 '18 at 12:28
mb14mb14
1258
1258
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
1
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
|
show 2 more comments
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
1
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
So it is not possible what I am trying to achieve?
– ILoveStackoverflow
Nov 22 '18 at 12:45
1
1
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
Actually, you can: String tableRestrictions = new String[4]; // For the array, 0-member represents Catalog; 1-member represents Schema; // 2-member represents Table Name; 3-member represents Table Type. And in your case it should be new string { null, "your_youser", "user_tables" }
– mb14
Nov 22 '18 at 12:54
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
What is your_youser is?because I do not have connection string fixed.I am reading connection string from database
– ILoveStackoverflow
Nov 22 '18 at 14:27
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
I am getting error with what you suggested : The Schema 'Tables' does not support more than '2' restrictions
– ILoveStackoverflow
Nov 22 '18 at 14:31
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
Sorry for the typo. The your_user should be the owner of the tables. As for the error you are receiving I would suggest to use only 2 restrictions, new string { "Owner_of_table", "user_tables" } and see where it gets you.
– mb14
Nov 22 '18 at 14:39
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430805%2fnot-getting-list-of-tables-from-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
does your user own the tables?
– Lachlan Lindsay
Nov 22 '18 at 12:20
@LachlanLindsay Yes as I have mentioned in the question that Categories and Adress table are the only tables onwed by user mentioned in the connection string
– ILoveStackoverflow
Nov 22 '18 at 12:22
When you access the database through c# code, who is the "current" user? It seems that the application connects to the DB as user SYSDBA, or some other admin account (which is why you get thousands of tables). How is the Oracle database supposed to know you want the tables for some specific user, if the application connects as SYSDBA?
– mathguy
Nov 22 '18 at 14:22
@mathguy Through connection string?because connection string have the information about the user.isnt it?
– ILoveStackoverflow
Nov 22 '18 at 14:24