Oracle 获取列数据大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4979515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle get column data size
提问by Marius
I'm new to Oracle db, and i wonder if i can find out the datatype and datasize... I mean : let's say i have the column "location" of type varchar2(20) in table "products". Could i write a query that would return me in the result "varchar2" and "20" ?
我是 Oracle db 的新手，我想知道我是否可以找出数据类型和数据大小...我的意思是：假设我在表“products”中有 varchar2(20) 类型的列“location”。我可以编写一个查询，在结果“varchar2”和“20”中返回我吗？
Thank you!
谢谢！
回答by Ronnis
Check out user_tab_columns.
select data_type, data_length
from user_tab_columns
where table_name = 'PRODUCTS'
and column_name = 'LOCATION';
回答by Gigatron
Do you need to find out the type and size of a column for the purpose of understanding the data, or are you writing a program that needs to know it at runtime?
您是否需要为了理解数据而找出列的类型和大小，或者您是否正在编写需要在运行时知道它的程序？
If you only need to know it for your knowledge, typing desc tablenamein SQLPlus will tell you about the columns in the table.
如果你只是为了你的知识而需要知道它，desc tablename在 SQLPlus 中键入会告诉你表中的列。
If you need to find out the type and size programmatically, what language are you using ... PL/SQL? Java?
如果您需要以编程方式找出类型和大小，您使用的是什么语言……PL/SQL？爪哇？
Note that selecting from USER_TAB_COLUMNS will only list the columns in tables in your own schema. For other tables you'll need to look at ALL_TAB_COLUMNS (or DBA_TAB_COLUMNS, if the DBA has allowed you to access it). Note that your employer or client may disallow stored procedures or packages from being compiled against either.
请注意，从 USER_TAB_COLUMNS 中选择只会列出您自己架构中表中的列。对于其他表，您需要查看 ALL_TAB_COLUMNS（或 DBA_TAB_COLUMNS，如果 DBA 允许您访问它）。请注意，您的雇主或客户可能不允许针对其中任何一个编译存储过程或程序包。
回答by Ketan
SELECT CHAR_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = <TABLENAME> AND COLUMN_NAME = <COLUMNNAME>;
回答by learningloop
The answer to this question was useful to me. But, there is one important difference needs to be considered while taking data_length when the column type is NVARCAHR2. The data_length returned for NVARCHA2column is doubled.
这个问题的答案对我很有用。但是，当列类型为 时，在取 data_length 时需要考虑一个重要的区别NVARCAHR2。为NVARCHA2列返回的 data_length加倍。
This is due to the character encoding being used. Oracle documentationsays:
这是由于使用了字符编码。 Oracle 文档说：
The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.
AL16UTF16 编码的字节数最多为 2 倍，UTF8 编码的字节数最多为 3 倍。
So, ideal query would be the following:
因此，理想的查询如下：
select table_name, column_name,
data_type, NVL(char_col_decl_length, data_length)
from user_tab_columns where table_name='T51_NOCOMP';
回答by CarlosLeo
I do the following from sqlplus for oracle:
我从 sqlplus 为 oracle 执行以下操作：
SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
AND COLUMN_NAME = 'YOUR';`
Hope it works for you too!
希望它也对你有用！
